<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>