24 Nov
2016
24 Nov
'16
11:15 a.m.
Hello Everyone,
For dump I though do something like this. No sure if possible optimise it. Also how if
possible convert inc_time to human readable ?
xlog("L_INFO", "[$rm], Got Register request for <$tU> dumping OFFLINE
SMS\n");
while(sql_pvquery("cb","SELECT id FROM silo ORDER BY id
ASC","$avp(id)")) {
sql_pvquery("cb", "SELECT dst_addr, src_addr, body, inc_time FROM silo
WHERE id = '$avp(id)'","$avp(dst), $avp(src), $avp(body),
$avp(time)");
crypto_aes_decrypt("$var(body)", "decryption key",
"$avp(decrypted)");
$uac_req(method)="MESSAGE";
$uac_req(furi)=$avp(src);
$uac_req(turi)=$avp(dst);
$uac_req(body)= $avp(decrypted) ;
uac_req_send();
}
Slava.
From: "volga629" <volga629(a)skillsearch.ca>
To: miconda(a)gmail.com, "sr-users" <sr-users(a)lists.sip-router.org>
Sent: Thursday, 24 November, 2016 09:34:18
Subject: Re: [SR-Users] msilo
Hello Daniel,
That good idea, I will need help with sqlops to update information properly. Make sure the
query is optimal. I will still can use if(m_store("$tu")), because it maintain
offline notification, but I can use something like this. For dump I will put uac.
route[OFFLINE_MESSAGE] {
if(!is_method("MESSAGE")) {
return;
}
if(isflagset(FLAG_FROM_PEER)) {
if(!lookup("location")) {
xlog("L_INFO", "User $tU domain $td offline. Trying store SMS for later
delivery\n");
xlog("L_INFO", "SMS received from $fU to $tU domain [$td] --> storing
using MSILO\n");
# MSILO - storing as offline message
if(m_store("$tu")) {
xlog("L_INFO", "MSILO: offline message stored\n");
# Encrypt stored offline message
crypto_aes_encrypt("$rb", "mykey", "$avp(encrypted)");
sql_pvquery("cb", "select * from silo ORDER BY id DESC LIMIT
1","$avp(id)");
sql_query_async("cb","UPDATE silo SET body = $avp(encrypted) WHERE id =
$avp(id)");
if(is_request()) {
if(!sl_send_reply("202", "Accepted")) {
sl_reply_error();
}
} else {
xlog("L_INFO", "MSILO: offline message NOT stored\n");
#if(!sl_send_reply("503", "Service Unavailable")) {
# sl_reply_error();
#}
}
}
t_on_failure("SMS_FAIL_ROUTE");
exit;
}
}
}
Slava.
From: "Daniel-Constantin Mierla" <miconda(a)gmail.com>
To: "sr-users" <sr-users(a)lists.sip-router.org>
Sent: Thursday, 24 November, 2016 06:45:48
Subject: Re: [SR-Users] msilo
I expect the encryption/decryption can be done with some triggers in mysql server.
In kamailio config you can use crypto module to encrypt a text and store it in a variable:
- https://www.kamailio.org/docs/modules/stable/modules/crypto.html
Then you can use sqlops to insert into the database. The issue comes when dumping stored
messages... probably you can just replace msilo with sqlops+uac at the expense of a more
complex configuration file.
On the other hand, probably adds some privacy to the local platform operators, which have
access only to the mysql, because the key will be in kamailio.cfg. For full privacy, the
endpoints should do the encryption/decryption with a key they agreed before, without being
known by the server.
Cheers,
Daniel
On 24/11/2016 05:28, Slava Bendersky wrote:
Sent message to mailing list
From: "volga629" <volga629(a)skillsearch.ca>
To: miconda(a)gmail.com
Sent: Thursday, 24 November, 2016 00:25:30
Subject: Re: [SR-Users] msilo
Hello Everyone,
I want to ask about another improvement for MSILO module. If possible encrypt BODY column
in database. That will improve some privacy concerns for storing body in plain text.
Slava.
--
Daniel-Constantin Mierla http://twitter.com/#!/miconda -
http://www.linkedin.com/in/miconda Kamailio Advanced Training, Berlin, Nov 28-30, 2016 -
http://www.asipto.com
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
25 Nov
25 Nov
10:03 a.m.
New subject: msilo
Hello,
you can select all the record in the database for a user with
sql_query() and do a while on $dbr(...) container -- se the example in
the readme of the sqlops module. Also, you must select only the records
for the user that registers at that moment, otherwise you sent the
records for other users -- so you must have some WHERE in the SELECT
statement.
Also, when delivered successfully, you should delete the record from
database -- an event_route can be executed by uac module in such case
and can be used to implement the deletion.
Cheers,
Daniel
On 24/11/2016 17:15, Slava Bendersky wrote:
Hello Everyone,
For dump I though do something like this. No sure if possible
optimise it. Also how if possible convert inc_time to human readable ?
xlog("L_INFO", "[$rm], Got Register request for <$tU> dumping
OFFLINE
SMS\n");
while(sql_pvquery("cb","SELECT id FROM silo ORDER BY id
ASC","$avp(id)")) {
sql_pvquery("cb", "SELECT dst_addr, src_addr, body, inc_time FROM silo
WHERE id = '$avp(id)'","$avp(dst), $avp(src), $avp(body),
$avp(time)");
crypto_aes_decrypt("$var(body)", "decryption key",
"$avp(decrypted)");
$uac_req(method)="MESSAGE";
$uac_req(furi)=$avp(src);
$uac_req(turi)=$avp(dst);
$uac_req(body)=$avp(decrypted);
uac_req_send();
}
Slava.
------------------------------------------------------------------------
*From: *"volga629" <volga629(a)skillsearch.ca>
*To: *miconda(a)gmail.com, "sr-users" <sr-users(a)lists.sip-router.org>
*Sent: *Thursday, 24 November, 2016 09:34:18
*Subject: *Re: [SR-Users] msilo
Hello Daniel,
That good idea, I will need help with sqlops to update information
properly. Make sure the query is optimal. I will still can
use if(m_store("$tu")), because it maintain offline notification, but
I can use something like this. For dump I will put uac.
route[OFFLINE_MESSAGE] {
if(!is_method("MESSAGE")) {
return;
}
if(isflagset(FLAG_FROM_PEER)) {
if(!lookup("location")) {
xlog("L_INFO", "User $tU domain $td offline. Trying store SMS for
later delivery\n");
xlog("L_INFO", "SMS received from $fU to $tU domain [$td] --> storing
using MSILO\n");
# MSILO - storing as offline message
if(m_store("$tu")) {
xlog("L_INFO", "MSILO: offline message stored\n");
# Encrypt stored offline message
crypto_aes_encrypt("$rb", "mykey", "$avp(encrypted)");
sql_pvquery("cb", "select * from silo ORDER BY id DESC LIMIT
1","$avp(id)");
sql_query_async("cb","UPDATE silo SET body = $avp(encrypted) WHERE id
= $avp(id)");
if(is_request()) {
if(!sl_send_reply("202", "Accepted")) {
sl_reply_error();
}
} else {
xlog("L_INFO", "MSILO: offline message NOT stored\n");
#if(!sl_send_reply("503", "Service Unavailable")) {
# sl_reply_error();
#}
}
}
t_on_failure("SMS_FAIL_ROUTE");
exit;
}
}
}
Slava.
------------------------------------------------------------------------
*From: *"Daniel-Constantin Mierla" <miconda(a)gmail.com>
*To: *"sr-users" <sr-users(a)lists.sip-router.org>
*Sent: *Thursday, 24 November, 2016 06:45:48
*Subject: *Re: [SR-Users] msilo
I expect the encryption/decryption can be done with some triggers in
mysql server.
In kamailio config you can use crypto module to encrypt a text and
store it in a variable:
- https://www.kamailio.org/docs/modules/stable/modules/crypto.html
Then you can use sqlops to insert into the database. The issue comes
when dumping stored messages... probably you can just replace msilo
with sqlops+uac at the expense of a more complex configuration file.
On the other hand, probably adds some privacy to the local platform
operators, which have access only to the mysql, because the key will
be in kamailio.cfg. For full privacy, the endpoints should do the
encryption/decryption with a key they agreed before, without being
known by the server.
Cheers,
Daniel
On 24/11/2016 05:28, Slava Bendersky wrote:
Sent message to mailing list
------------------------------------------------------------------------
*From: *"volga629" <volga629(a)skillsearch.ca>
*To: *miconda(a)gmail.com
*Sent: *Thursday, 24 November, 2016 00:25:30
*Subject: *Re: [SR-Users] msilo
Hello Everyone,
I want to ask about another improvement for MSILO module. If
possible encrypt BODY column in database. That will improve some
privacy concerns for storing body in plain text.
Slava.
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com
3007
days inactive
3008
days old
1 comments
2 participants
participants (2)
-
Daniel-Constantin Mierla -
Slava Bendersky