<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body>
    <font face="monospace">Could you please try this ?<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 1:52 PM, Bogdan-Andrei
      Iancu wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:99ab2369-3dc1-7fb3-0c57-bdd1c37f6df9@opensips.org">
      <meta http-equiv="Content-Type" content="text/html;
        charset=windows-1252">
      <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" moz-do-not-send="true">https://www.opensips-solutions.com</a>
OpenSIPS eBootcamp 2021 
  <a class="moz-txt-link-freetext" href="https://opensips.org/training/OpenSIPS_eBootcamp_2021/" moz-do-not-send="true">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:part10.A9AFBC63.202A16C5@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>
      <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <pre class="moz-quote-pre" wrap="">_______________________________________________
Users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a>
<a class="moz-txt-link-freetext" href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a>
</pre>
    </blockquote>
    <br>
  </body>
</html>