JWT decoder

You could probably use Pl/Python. Python has base64 module and urllib
module which can deal with all URL strings I am aware of. Pl/Perl would
probably do as well.

On 8/8/21 10:16 PM, Masih Tavassoli wrote:

Hi experts,

I am trying to find a way to decode a URL request header and extract
its JSON fields in postgreql .

I can do this in Oracle sql using

select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode(
utl_raw.cast_to_raw (regexp_replace ( (
'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'

), '[[:space:]]', ''))))) from dual

But there doesn't seem to be a way doing it in postgres.

Has anyone got any suggesions?

Thanks
Masih

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hello,

On Mon, 2021-08-09 at 02:16 +0000, Masih Tavassoli wrote:

Hi experts,

I am trying to find a way to decode a URL request header and extract
its JSON fields in postgreql .

I can do this in Oracle sql using 

select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode(
utl_raw.cast_to_raw (regexp_replace ( (
'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1
YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4Y
V9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW
5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6B
gefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNji
cyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkw
PO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxB
sGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ng
zRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'
), '[[:space:]]', ''))))) from dual

But there doesn't seem to be a way doing it in postgres.

Has anyone got any suggesions?

Thanks
Masih

Have a look at the orafce extension on github.
It handles "select from dual", etc.

Cheers,
Rob

There are lots of decoders but I need to do it within postgresql.
On Monday, August 9, 2021, 01:24:33 PM GMT+10, Mladen Gogala <gogala.mladen@gmail.com> wrote:

You could probably use Pl/Python. Python has base64 module and urllib module which can deal with all URL strings I am aware of.  Pl/Perl would probably do as well.

On 8/8/21 10:16 PM, Masih Tavassoli wrote:

Hi experts,
I am trying to find a way to decode a URL request header and extract its JSON fields in postgreql .
I can do this in Oracle sql using 

select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( utl_raw.cast_to_raw (regexp_replace ( ('eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'
), '[[:space:]]', ''))))) from dual

But there doesn't seem to be a way doing it in postgres.
Has anyone got any suggesions?
Thanks Masih
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

That was not the question, the question is how do you decode base64url (not base64) in postgres sql?
On Monday, August 9, 2021, 02:08:44 PM GMT+10, rob stone <floriparob@gmail.com> wrote:

Hello,

On Mon, 2021-08-09 at 02:16 +0000, Masih Tavassoli wrote:

Hi experts,

I am trying to find a way to decode a URL request header and extract
its JSON fields in postgreql .

I can do this in Oracle sql using 

select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode(
utl_raw.cast_to_raw (regexp_replace ( (
'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1
YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4Y
V9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW
5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6B
gefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNji
cyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkw
PO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxB
sGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ng
zRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'
), '[[:space:]]', ''))))) from dual

But there doesn't seem to be a way doing it in postgres.

Has anyone got any suggesions?

Thanks
Masih

Have a look at the orafce extension on github.
It handles "select from dual", etc.

Cheers,
Rob

Hmmm, Pl/Python and Pl/Perl are languages usable from within Postgres.
You can write Python functions in Postgres. I apologize for not making
that clear.

On 8/9/21 1:15 AM, Masih Tavassoli wrote:

There are lots of decoders but I need to do it within postgresql.

On Monday, August 9, 2021, 01:24:33 PM GMT+10, Mladen Gogala
<gogala.mladen@gmail.com> wrote:

You could probably use Pl/Python. Python has base64 module and urllib
module which can deal with all URL strings I am aware of.  Pl/Perl
would probably do as well.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Masih Tavassoli wrote:

But there doesn't seem to be a way doing it in postgres.
Has anyone got any suggesions?

RFC 7519 says:

A JWT is represented as a sequence of URL-safe parts separated by
period ('.') characters. Each part contains a base64url-encoded
value. The number of parts in the JWT is dependent upon the
representation of the resulting JWS using the JWS Compact
Serialization or JWE using the JWE Compact Serialization.

base64url is similar to base64 except that the two characters
+ and / are replaced by - and _

Postgres provides decode(..., 'base64') but it's stricter than the
Oracle version showed in your sample code (which seems to ignore the
dot character that is illegal in base64 whereas Postgres would reject
it).

The JWT may be decoded with built-in Postgres functions by
splitting the dot-separated parts with regexp_split_to_table(),
converting them from base64url into binary, then into UTF-8,
and then the results could be cast into the json type if needed.

So the SQL code could be:

create function decode_base64url(text) returns bytea as $$
select decode(
rpad(translate($1, '-_', '+/') -- pad to the next multiple of 4 bytes
,4*((length($1)+3)/4)
,'=')
,'base64');
$$ language sql strict immutable;

with parts(x,n) as (
select * from regexp_split_to_table('<insert the JWT here>', '\.')
with ordinality
)
select n, convert_from(decode_base64url(x), 'utf-8')
from parts where n in (1,2);

"n" in the query is the part number, 1 for the header, 2 for the
payload, 3 for the signature which is ignored here.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite