<html><body><div style="font-family: lucida console,sans-serif; font-size: 12pt; color: #000000"><div>Hello Everyone,</div><div>For dump I though do something like this. No sure if possible optimise it. Also how if possible convert inc_time to human readable ?</div><div><br data-mce-bogus="1"></div><div>xlog("L_INFO", "[$rm], Got Register request for <$tU> dumping OFFLINE SMS\n");<br><br> while(sql_pvquery("cb","SELECT id FROM silo ORDER BY id ASC","$avp(id)")) {<br> 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)");<br> crypto_aes_decrypt("$var(body)", "decryption key", "$avp(decrypted)");<br><br> $uac_req(method)="MESSAGE";<br> $uac_req(furi)=$avp(src);<br> $uac_req(turi)=$avp(dst);<br> $uac_req(body)=<span style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;" data-mce-style="color: #000000; font-family: 'lucida console', sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">$avp(decrypted)</span>;<br> uac_req_send();<br> }<br></div><div><br data-mce-bogus="1"></div><div>Slava.</div><div><br></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>From: </b>"volga629" <volga629@skillsearch.ca><br><b>To: </b>miconda@gmail.com, "sr-users" <sr-users@lists.sip-router.org><br><b>Sent: </b>Thursday, 24 November, 2016 09:34:18<br><b>Subject: </b>Re: [SR-Users] msilo<br></div><br><div data-marker="__QUOTED_TEXT__"><div style="font-family: lucida console,sans-serif; font-size: 12pt; color: #000000" data-mce-style="font-family: lucida console,sans-serif; font-size: 12pt; color: #000000"><div>Hello Daniel,</div><div>That good idea, I will need help with <span style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;" data-mce-style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;">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.</span></div><div><span style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;" data-mce-style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;"><br></span></div><div><span style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;" data-mce-style="color: rgb(0, 0, 0); font-family: "lucida console", sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;"><br></span></div><div>route[OFFLINE_MESSAGE] {<br> if(!is_method("MESSAGE")) {<br> return;<br> }<br><br> if(isflagset(FLAG_FROM_PEER)) { <br> if(!lookup("location")) {<br> xlog("L_INFO", "User $tU domain $td offline. Trying store SMS for later delivery\n");<br> xlog("L_INFO", "SMS received from $fU to $tU domain [$td] --> storing using MSILO\n");<br> # MSILO - storing as offline message<br> if(m_store("$tu")) {<br> xlog("L_INFO", "MSILO: offline message stored\n");<br> # Encrypt stored offline message<br> crypto_aes_encrypt("$rb", "mykey", "$avp(encrypted)"); <br> sql_pvquery("cb", "select * from silo ORDER BY id DESC LIMIT 1","$avp(id)");<br> sql_query_async("cb","UPDATE silo SET body = $avp(encrypted) WHERE id = $avp(id)");<br><br> if(is_request()) {<br> if(!sl_send_reply("202", "Accepted")) {<br> sl_reply_error();<br> }<br> } else {<br> xlog("L_INFO", "MSILO: offline message NOT stored\n");<br> #if(!sl_send_reply("503", "Service Unavailable")) {<br> # sl_reply_error();<br> #}<br> }<br> }<br> t_on_failure("SMS_FAIL_ROUTE");<br> exit;<br> }<br> }<br>}<br><br></div><br><div>Slava.</div><hr id="zwchr"><div><b>From: </b>"Daniel-Constantin Mierla" <miconda@gmail.com><br><b>To: </b>"sr-users" <sr-users@lists.sip-router.org><br><b>Sent: </b>Thursday, 24 November, 2016 06:45:48<br><b>Subject: </b>Re: [SR-Users] msilo<br></div><br><div><p>I expect the encryption/decryption can be done with some triggers
in mysql server.<br>
</p>
In kamailio config you can use crypto module to encrypt a text and
store it in a variable:<br>
<br>
- <a class="moz-txt-link-freetext" href="https://www.kamailio.org/docs/modules/stable/modules/crypto.html" target="_blank">https://www.kamailio.org/docs/modules/stable/modules/crypto.html</a><br>
<br>
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.<br>
<br>
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.<br>
<br>
Cheers,<br>
Daniel<br>
<br>
<div class="moz-cite-prefix">On 24/11/2016 05:28, Slava Bendersky
wrote:<br>
</div>
<blockquote cite="mid:1972023295.67204489.1479961703429.JavaMail.zimbra@skillsearch.ca">
<div style="font-family: lucida console,sans-serif; font-size:
12pt; color: #000000" data-mce-style="font-family: lucida console,sans-serif; font-size:
12pt; color: #000000">
<div>Sent message to mailing list</div>
<div><br>
</div>
<hr id="zwchr">
<div><b>From: </b>"volga629"
<a class="moz-txt-link-rfc2396E" href="mailto:volga629@skillsearch.ca" target="_blank"><volga629@skillsearch.ca></a><br>
<b>To: </b><a class="moz-txt-link-abbreviated" href="mailto:miconda@gmail.com" target="_blank">miconda@gmail.com</a><br>
<b>Sent: </b>Thursday, 24 November, 2016 00:25:30<br>
<b>Subject: </b>Re: [SR-Users] msilo<br>
</div>
<br>
<div>
<div style="font-family: lucida console,sans-serif; font-size:
12pt; color: #000000" data-mce-style="font-family: lucida console,sans-serif; font-size:
12pt; color: #000000">
<div>Hello Everyone,</div>
<div>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.</div>
<br>
<div>Slava. </div>
<br>
</div>
</div>
</div>
</blockquote>
<br>
<pre class="moz-signature">--
Daniel-Constantin Mierla
<a class="moz-txt-link-freetext" href="http://twitter.com/#!/miconda" target="_blank">http://twitter.com/#!/miconda</a> - <a class="moz-txt-link-freetext" href="http://www.linkedin.com/in/miconda" target="_blank">http://www.linkedin.com/in/miconda</a>
Kamailio Advanced Training, Berlin, Nov 28-30, 2016 - <a class="moz-txt-link-freetext" href="http://www.asipto.com" target="_blank">http://www.asipto.com</a></pre>
<br>_______________________________________________<br>SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list<br>sr-users@lists.sip-router.org<br>http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users<br></div></div><br>_______________________________________________<br>SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list<br>sr-users@lists.sip-router.org<br>http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users<br></div></div></body></html>