<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Hello,</p>
<p>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.</p>
<p>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.</p>
<p>Cheers,<br>
Daniel<br>
</p>
<br>
<div class="moz-cite-prefix">On 24/11/2016 17:15, Slava Bendersky
wrote:<br>
</div>
<blockquote
cite="mid:1802916000.71815126.1480004145361.JavaMail.zimbra@skillsearch.ca"
type="cite">
<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"
<a class="moz-txt-link-rfc2396E" href="mailto:volga629@skillsearch.ca"><volga629@skillsearch.ca></a><br>
<b>To: </b><a class="moz-txt-link-abbreviated" href="mailto:miconda@gmail.com">miconda@gmail.com</a>, "sr-users"
<a class="moz-txt-link-rfc2396E" href="mailto:sr-users@lists.sip-router.org"><sr-users@lists.sip-router.org></a><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"
<a class="moz-txt-link-rfc2396E" href="mailto:miconda@gmail.com"><miconda@gmail.com></a><br>
<b>To: </b>"sr-users"
<a class="moz-txt-link-rfc2396E" href="mailto:sr-users@lists.sip-router.org"><sr-users@lists.sip-router.org></a><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 moz-do-not-send="true"
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
moz-do-not-send="true"
class="moz-txt-link-rfc2396E"
href="mailto:volga629@skillsearch.ca"
target="_blank"><volga629@skillsearch.ca></a><br>
<b>To: </b><a moz-do-not-send="true"
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 moz-do-not-send="true" class="moz-txt-link-freetext" href="http://twitter.com/#%21/miconda" target="_blank">http://twitter.com/#!/miconda</a> - <a moz-do-not-send="true" 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 moz-do-not-send="true" 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>
<a class="moz-txt-link-abbreviated" href="mailto:sr-users@lists.sip-router.org">sr-users@lists.sip-router.org</a><br>
<a class="moz-txt-link-freetext" href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</div>
</div>
<br>
_______________________________________________<br>
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users
mailing list<br>
<a class="moz-txt-link-abbreviated" href="mailto:sr-users@lists.sip-router.org">sr-users@lists.sip-router.org</a><br>
<a class="moz-txt-link-freetext" href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</div>
</div>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Daniel-Constantin Mierla
<a class="moz-txt-link-freetext" href="http://twitter.com/#!/miconda">http://twitter.com/#!/miconda</a> - <a class="moz-txt-link-freetext" href="http://www.linkedin.com/in/miconda">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">http://www.asipto.com</a></pre>
</body>
</html>