<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body>
<font face="monospace">Thank you Jonathan,<br>
<br>
I guess the proper fix here will be to push the unixtimestamp from
the code level, rather from DB level - in this way we can get an
SQL compliant query. Let me send you a patch for testing<br>
<br>
Regards,<br>
</font>
<pre class="moz-signature" cols="72">Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
<a class="moz-txt-link-freetext" href="https://www.opensips-solutions.com">https://www.opensips-solutions.com</a>
OpenSIPS eBootcamp 2021
<a class="moz-txt-link-freetext" href="https://opensips.org/training/OpenSIPS_eBootcamp_2021/">https://opensips.org/training/OpenSIPS_eBootcamp_2021/</a></pre>
<div class="moz-cite-prefix">On 12/21/21 12:29 PM, Jonathan Hunter
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:AS8P195MB1287E1A0A229DC419595EAA4BD7C9@AS8P195MB1287.EURP195.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style>@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Courier New";}p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}.MsoChpDefault
{mso-style-type:export-only;}div.WordSection1
{page:WordSection1;}</style>
<div class="WordSection1">
<p class="MsoNormal">Hi Bogdan,</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks for the reply.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have modified as below so this works
with postgres, essentially just changed the syntax as below;</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">~/opensips-3.2/modules/auth_jwt/authorize.c</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">-n = snprintf(p,len," from %.*s a inner
join %.*s b on a.%.*s = b.%.*s where a.%.*s=\"%.*s\" and
UNIX_TIMESTAMP() >= b.%.*s and UNIX_TIMESTAMP() <
b.%.*s",</p>
<p class="MsoNormal">+n = snprintf(p,len," from %.*s a inner
join %.*s b on a.%.*s = b.%.*s where a.%.*s=\'%.*s\' and
extract(epoch from now()) >= b.%.*s and extract(epoch from
now()) < b.%.*s",</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I would assume there needs some further
modification depending on if its MySQL or postgres as
currently I have just changed as above and haven’t tested if
it works for the other backend types.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I hope that’s enough detail for you? If you
need anything else let me know!</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Jon</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Sent from <a
href="https://go.microsoft.com/fwlink/?LinkId=550986"
moz-do-not-send="true">
Mail</a> for Windows</p>
<p class="MsoNormal"><o:p> </o:p></p>
<div
style="mso-element:para-border-div;border:none;border-top:solid
#E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="border:none;padding:0cm"><b>From:
</b><a href="mailto:bogdan@opensips.org"
moz-do-not-send="true">Bogdan-Andrei Iancu</a><br>
<b>Sent: </b>21 December 2021 08:36<br>
<b>To: </b><a href="mailto:users@lists.opensips.org"
moz-do-not-send="true">OpenSIPS users mailling list</a>;
<a href="mailto:hunterj91@hotmail.com"
moz-do-not-send="true">Jonathan Hunter</a><br>
<b>Subject: </b>Re: [OpenSIPS-Users] Opensips 3.2 from
sources testing auth_jwt with postgres database backend.</p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier
New"">Hi Jonathan,<br>
<br>
yeah, that raw query from JWT module is not sql compliant,
but mysql compliant :D. Could you provide the patch for your
fixes, so we can evaluate and push forward ?<br>
<br>
Thanks,<br>
<br>
</span><o:p></o:p></p>
<pre>Bogdan-Andrei Iancu</pre>
<pre><o:p> </o:p></pre>
<pre>OpenSIPS Founder and Developer</pre>
<pre> <a href="https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.opensips-solutions.com%2F&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636784536%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=gBYthg1JyMKs8BcUY%2Ft3OvVvpzxNBWzIKUDnCJBvtAM%3D&reserved=0" moz-do-not-send="true">https://www.opensips-solutions.com</a></pre>
<pre>OpenSIPS eBootcamp 2021 </pre>
<pre> <a href="https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fopensips.org%2Ftraining%2FOpenSIPS_eBootcamp_2021%2F&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636794494%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=gIo3IyIeaQJTe59rxRAQP7HJa5Yzv9DBUn%2B%2BANBGqzk%3D&reserved=0" moz-do-not-send="true">https://opensips.org/training/OpenSIPS_eBootcamp_2021/</a></pre>
<div>
<p class="MsoNormal">On 12/20/21 6:58 PM, Jonathan Hunter
wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal">Hi Guys, <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Note this query seems to work with my
postgres;<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">SELECT a.sip_username,b.secret from
jwt_profiles a inner join jwt_secrets b on a.tag =
b.corresponding_tag where a.tag='space-monkey' and
extract(epoch from now()) >= b.start_ts and extract(epoch
from now()) < b.end_ts;<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Doe I need to patch for his or should it
work with postgres anyway? Thanks!<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Jon<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Sent from <a
href="https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0"
moz-do-not-send="true">
Mail</a> for Windows<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><img
style="width:3.8472in;height:.0208in"
id="Horizontal_x0020_Line_x0020_1"
src="cid:part8.4A4D286A.87718709@opensips.org" class=""
width="369" height="2" border="0"><o:p></o:p></p>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">From:</span></b><span
style="color:black"> Users
<a href="mailto:users-bounces@lists.opensips.org"
moz-do-not-send="true"><users-bounces@lists.opensips.org></a>
on behalf of Jonathan Hunter
<a href="mailto:hunterj91@hotmail.com"
moz-do-not-send="true"><hunterj91@hotmail.com></a><br>
<b>Sent:</b> Monday, December 20, 2021 2:21:08 PM<br>
<b>To:</b> OpenSIPS users mailling list <a
href="mailto:users@lists.opensips.org"
moz-do-not-send="true">
<users@lists.opensips.org></a><br>
<b>Subject:</b> [OpenSIPS-Users] Opensips 3.2 from
sources testing auth_jwt with postgres database backend.</span>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div>
<p class="xmsonormal">Hi guys, I am testing the auth_jwt
module with a postgres backend database and it appears
to be using Mysql syntax, so I am getting error below,
as I dont think UNIX_TIMESTAMP is a postgres function
and its not happy with the quotes.</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">See output below, just testing with
the example tag;</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
--[96gpfj5qgkseqevkhv5a] RT_REGISTER token is
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ0YWciOiJzcGFjZS1tb25rZXkifQ.4JxP0_a-l6uDhjP50JaocheyvgQGhB-0zJsYpFTACkgDec
20 14:08:56 [13688] DBG:auth_jwt:jwt_authorize: Decoded
JWT and found claim tag with value space-monkey</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
DBG:auth_jwt:jwt_authorize: built JWT raw db query
[SELECT a.sip_username,b.secret from jwt_profiles a
inner join jwt_secrets b on a.tag = b.corresponding_tag
where a.tag="space-monkey" and UNIX_TIMESTAMP() >=
b.start_ts and UNIX_TIMESTAMP() < b.end_ts]</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
DBG:db_postgres:db_postgres_submit_query: 0x7fa87dcac018
PQsendQuery(SELECT a.sip_username,b.secret from
jwt_profiles a inner join jwt_secrets b on a.tag =
b.corresponding_tag where a.tag="space-monkey" and
UNIX_TIMESTAMP() >= b.start_ts and UNIX_TIMESTAMP()
< b.end_ts)</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
DBG:db_postgres:db_postgres_submit_query: 0x7fa87dcac018
PQsendQuery failed: ERROR: column "space-monkey" does
not exist</p>
<p class="xmsonormal">LINE 1: ...ets b on a.tag =
b.corresponding_tag where a.tag="space-mon...</p>
<p class="xmsonormal">
^</p>
<p class="xmsonormal">Query: SELECT
a.sip_username,b.secret from jwt_profiles a inner join
jwt_secrets b on a.tag = b.corresponding_tag where
a.tag="space-monkey" and UNIX_TIMESTAMP() >=
b.start_ts and UNIX_TIMESTAMP() < b.end_ts</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
DBG:db_postgres:free_query: PQclear(0x556a28109850)
result set</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
ERROR:db_postgres:db_postgres_submit_query:
0x7fa87dcac018 PQsendQuery Error: ERROR: column
"space-monkey" does not exist</p>
<p class="xmsonormal">LINE 1: ...ets b on a.tag =
b.corresponding_tag where a.tag="space-mon...</p>
<p class="xmsonormal">
^</p>
<p class="xmsonormal">Query: SELECT
a.sip_username,b.secret from jwt_profiles a inner join
jwt_secrets b on a.tag = b.corresponding_tag where
a.tag="space-monkey" and UNIX_TIMESTAMP() >=
b.start_ts and UNIX_TIMESTAMP() < b.end_ts</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
ERROR:core:db_do_raw_query: error while submitting query</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688]
ERROR:auth_jwt:jwt_authorize: raw_query failed</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">I have installed from sources, am I
missing something here or missed a step? Any help would
be great as really want to get this module working.</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">Thanks!</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">Jon</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">Sent from <a
href="https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0"
moz-do-not-send="true">
Mail</a> for Windows</p>
<p class="xmsonormal"> </p>
</div>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________</pre>
<pre>Users mailing list</pre>
<pre><a href="mailto:Users@lists.opensips.org" moz-do-not-send="true">Users@lists.opensips.org</a></pre>
<pre><a href="https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists.opensips.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fusers&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636814408%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5gEZIA9TrXbMZhAXuOz5bn0MR99zHJ%2FN8qXvYl615AA%3D&reserved=0" moz-do-not-send="true">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a></pre>
</blockquote>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</blockquote>
<br>
</body>
</html>