Could you please try this ?
Regards,
Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
https://www.opensips-solutions.com
OpenSIPS eBootcamp 2021
https://opensips.org/training/OpenSIPS_eBootcamp_2021/
On 12/21/21 1:52 PM, Bogdan-Andrei Iancu wrote:
Thank you Jonathan,
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
Regards,
Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
https://www.opensips-solutions.com
OpenSIPS eBootcamp 2021
https://opensips.org/training/OpenSIPS_eBootcamp_2021/
On 12/21/21 12:29 PM, Jonathan Hunter wrote:
Hi Bogdan,
Thanks for the reply.
I have modified as below so this works with postgres, essentially
just changed the syntax as below;
~/opensips-3.2/modules/auth_jwt/authorize.c
-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",
+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",
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.
I hope that’s enough detail for you? If you need anything else let me
know!
Thanks
Jon
Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
Windows
*From: *Bogdan-Andrei Iancu <mailto:bog...@opensips.org>
*Sent: *21 December 2021 08:36
*To: *OpenSIPS users mailling list <mailto:users@lists.opensips.org>;
Jonathan Hunter <mailto:hunter...@hotmail.com>
*Subject: *Re: [OpenSIPS-Users] Opensips 3.2 from sources testing
auth_jwt with postgres database backend.
Hi Jonathan,
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 ?
Thanks,
Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
https://www.opensips-solutions.com
<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>
OpenSIPS eBootcamp 2021
https://opensips.org/training/OpenSIPS_eBootcamp_2021/
<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>
On 12/20/21 6:58 PM, Jonathan Hunter wrote:
Hi Guys,
Note this query seems to work with my postgres;
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;
Doe I need to patch for his or should it work with postgres
anyway? Thanks!
Jon
Sent from Mail
<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>
for Windows
*From:*Users <users-boun...@lists.opensips.org>
<mailto:users-boun...@lists.opensips.org> on behalf of Jonathan
Hunter <hunter...@hotmail.com> <mailto:hunter...@hotmail.com>
*Sent:* Monday, December 20, 2021 2:21:08 PM
*To:* OpenSIPS users mailling list <users@lists.opensips.org>
<mailto:users@lists.opensips.org>
*Subject:* [OpenSIPS-Users] Opensips 3.2 from sources testing
auth_jwt with postgres database backend.
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.
See output below, just testing with the example tag;
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
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]
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)
Dec 20 14:08:56 [13688] DBG:db_postgres:db_postgres_submit_query:
0x7fa87dcac018 PQsendQuery failed: ERROR: column "space-monkey"
does not exist
LINE 1: ...ets b on a.tag = b.corresponding_tag where
a.tag="space-mon...
^
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
Dec 20 14:08:56 [13688] DBG:db_postgres:free_query:
PQclear(0x556a28109850) result set
Dec 20 14:08:56 [13688]
ERROR:db_postgres:db_postgres_submit_query: 0x7fa87dcac018
PQsendQuery Error: ERROR: column "space-monkey" does not exist
LINE 1: ...ets b on a.tag = b.corresponding_tag where
a.tag="space-mon...
^
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
Dec 20 14:08:56 [13688] ERROR:core:db_do_raw_query: error while
submitting query
Dec 20 14:08:56 [13688] ERROR:auth_jwt:jwt_authorize: raw_query
failed
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.
Thanks!
Jon
Sent from Mail
<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>
for Windows
_______________________________________________
Users mailing list
Users@lists.opensips.org <mailto:Users@lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
<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>
_______________________________________________
Users mailing list
Users@lists.opensips.org
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
diff --git a/modules/auth_jwt/authorize.c b/modules/auth_jwt/authorize.c
index 0c7e0e79e..856a88969 100644
--- a/modules/auth_jwt/authorize.c
+++ b/modules/auth_jwt/authorize.c
@@ -23,6 +23,7 @@
*/
#include <string.h>
+#include <time.h>
#include "../../ut.h"
#include "../../str.h"
#include "../../db/db.h"
@@ -66,6 +67,7 @@ int jwt_authorize(struct sip_msg* _msg, str* jwt_token,
db_row_t *row;
pv_value_t pv_val;
int_str ivalue;
+ time_t unix_ts;
jwt_token_buf = pkg_malloc(jwt_token->len + 1);
if (!jwt_token_buf) {
@@ -106,16 +108,17 @@ int jwt_authorize(struct sip_msg* _msg, str* jwt_token,
DEC_AND_CHECK_LEN(len,n);
p+=n;
}
-
- 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",
+
+ time( &unix_ts);
+ n = snprintf(p,len," from %.*s a inner join %.*s b on a.%.*s = b.%.*s where a.%.*s='%.*s' and %ld >= b.%.*s and %ld < b.%.*s",
profiles_table.len,profiles_table.s,
- secrets_table.len,secrets_table.s,
+ secrets_table.len,secrets_table.s,
tag_column.len,tag_column.s,
secret_tag_column.len,secret_tag_column.s,
tag_column.len,tag_column.s,
tag.len,tag.s,
- start_ts_column.len,start_ts_column.s,
- end_ts_column.len,end_ts_column.s);
+ unix_ts, start_ts_column.len,start_ts_column.s,
+ unix_ts, end_ts_column.len,end_ts_column.s);
DEC_AND_CHECK_LEN(len,n);
p+=n;
_______________________________________________
Users mailing list
Users@lists.opensips.org
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
