Обсуждение: JWT decoder
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'
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.
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 usingselect (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 dualBut there doesn't seem to be a way doing it in postgres.Has anyone got any suggesions?ThanksMasih
-- 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
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.
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'
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
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.
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