Encode JWT token in Oracle PL/SQL

Question

I want to use a JWT to inatialise a meeting room in Jitsi. Our software will create the correct url for the user so it can join the meeting. Only users with the correct token can start a meeting.

The url is created with pl/sql. For the example of a valid token i use the website jwt.io. I wrote the following code to create the token. The first part of the token is correct. Only the signature doesn't match. I think i'm missing a typecast or variable l_content has the wrong type. What am i doing wrong?

                    l_token varchar2(30000);
                    l_header varchar2(1000);
                    l_header_base64  raw (1000);

                    l_payload varchar2(10000);
                    l_payload_base64  raw(10000);

                    l_signature varchar2(30000);
                    l_secretkey string(32767) :='your-256-bit-secret';
                    l_content string(32767);
                                    l_content_raw raw(30000);
                    l_point raw(1);

                    l_charset varchar2(8) := 'AL32UTF8';
                    l_crlf varchar2(2) := chr(13) || chr(10);

                    l_pay_clean varchar2(10000);
                    l_hdr_clean varchar2(10000);



        begin
            sys.dbms_output.put_line('Token from JWT.io: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c');
            l_point := utl_i18n.string_to_raw( '.', l_charset);
            --
                    --
                    -- maak header middels json en base64 encode
                    select json_object ('alg'  value 'HS256'
                                       ,'typ'  value 'JWT')
                    into   l_header
                    from dual;

                    select json_object ('sub'  value '1234567890'
                                        ,'name'  value 'John Doe'
                                        ,'iat'  value 1516239022)
                    into   l_payload
                    from dual;

                     l_header_base64  := utl_encode.base64_encode(utl_raw.cast_to_raw(l_header));
                     l_payload_base64 := utl_encode.base64_encode(utl_raw.cast_to_raw(l_payload));

                    l_hdr_clean := replace(replace(utl_raw.cast_to_varchar2(l_header_base64),l_crlf), '==');
                    l_pay_clean := replace(replace(utl_raw.cast_to_varchar2(l_payload_base64),l_crlf), '==');
                    sys.dbms_output.put_line('l_hdr_clean: ' || l_hdr_clean);
                    sys.dbms_output.put_line('l_pay_clean: ' || l_pay_clean);

                    l_content  := l_hdr_clean || '.' ||l_pay_clean;
                    sys.dbms_output.put_line('l_content: ' || l_content);

                    l_signature  := dbms_crypto.mac(UTL_I18N.string_to_raw(l_content, l_charset)
                                                    ,dbms_crypto.hmac_sh256
                                                    ,utl_i18n.string_to_raw(l_secretkey, l_charset));

                    sys.dbms_output.put_line('l_signature:' || l_signature);

                    return l_hdr_clean||'.'||l_pay_clean||'.'||l_signature;
        end;

Answer 1

Here is the final solution with help from jps:

function cleanup_base64 (p_base in raw) return varchar2
    is
            l_return varchar2(32767);
            l_crlf   varchar2(2) := chr(13) || chr(10);
            l_base   varchar2(32767);
    begin
            l_base   := utl_i18n.raw_to_char (p_base, CONVERT_CHARSET);
            l_base   := replace(replace(replace(replace(l_base,l_crlf), '='),'+', '-'), '/', '_');
            l_return := l_base;

            return l_return;
    end;


 function generate_jitsi_token (p_naam in varchar2) return varchar2
    is

        l_token             varchar2(30000);
        l_header            varchar2(30000);
        l_payload           varchar2(30000);
        l_signature         varchar2(30000);
        l_secretkey         varchar2(10000):="your-secret-key";

        l_content           varchar2(30000);
        l_pay_clean         varchar2(30000);
        l_hdr_clean         varchar2(30000);

        l_header_base64     raw(30000);
        l_payload_base64    raw(30000);
        l_signature_base64  raw(30000);

    begin


        -- maak header middels json en base64 encode
        select json_object ('alg'  value 'HS256'
                           ,'typ'  value 'JWT')
        into   l_header
        from dual;

        -- maak payload middels json en base64 encode
        select json_object ('context' value
                    json_object ('user' value
                         json_object ('name' value p_naam))
                           ,'sub'  value 'text'
                           ,'iss'  value 'text'
                           ,'room' value '*'
                           ,'aud'  value 'jitsi')
        into   l_payload
        from dual;

        --
        l_header_base64  := utl_encode.base64_encode (utl_i18n.string_to_raw (l_header,  CONVERT_CHARSET));
        l_payload_base64 := utl_encode.base64_encode (utl_i18n.string_to_raw (l_payload, CONVERT_CHARSET));

        l_hdr_clean := cleanup_base64 (l_header_base64);
        l_pay_clean := cleanup_base64 (l_payload_base64);

        l_content := l_hdr_clean || '.' ||l_pay_clean;

        l_signature_base64  := utl_encode.base64_encode
                                      (dbms_crypto.mac(utl_i18n.string_to_raw (l_content, CONVERT_CHARSET)
                                                      ,dbms_crypto.hmac_sh256
                                                      ,utl_i18n.string_to_raw(l_secretkey, CONVERT_CHARSET)));

        l_signature :=  cleanup_base64 (l_signature_base64);
        l_token := l_hdr_clean||'.'||l_pay_clean||'.'|| l_signature;


        return l_token;
    end;

Answer 2

May be something like that

Oracle PL/SQL Server Side Implementation Example for User Authentication via JWT:

1. Create a table to store JWT tokens:
    
     CREATE TABLE jwt_tokens (
       token VARCHAR2(4000) PRIMARY KEY,
       user_id NUMBER,
       expiration_date DATE
     );
    
     2. Creating a procedure for generating a JWT token:
    
     CREATE OR REPLACE PROCEDURE generate_jwt_token(
       p_user_id IN NUMBER,
       p_expiration_date IN DATE,
       p_secret_key IN VARCHAR2,
       p_token OUT VARCHAR2
     ) AS
       l_header VARCHAR2(4000);
       l_payload VARCHAR2(4000);
       l_signature VARCHAR2(4000);
     BEGIN
       -- Create a JWT header
       l_header := '{"alg":"HS256","type":"JWT"}';
    
       -- Create JWT body
       l_payload := '{"user_id":' || p_user_id || ',"exp":' || p_expiration_date || '}';
    
       -- Create a JWT signature
       l_signature := dbms_crypto.mac(
         utl_raw.cast_to_raw(l_header || '.' || l_payload),
         dbms_crypto.hmac_sh256,
         utl_raw.cast_to_raw(p_secret_key)
       );
    
       -- JWT encoding
       p_token := utl_raw.cast_to_varchar2(
         utl_encode.base64_encode(
           utl_raw.cast_to_raw(l_header) || '.' ||
           utl_raw.cast_to_raw(l_payload) || '.' ||
           utl_raw.cast_to_raw(l_signature)
         )
       );
     END;
    
     3. Create a function to validate the JWT token:
    
     CREATE OR REPLACE FUNCTION verify_jwt_token(
       p_token IN VARCHAR2,
       p_secret_key IN VARCHAR2
     ) RETURN NUMBER AS
       l_header VARCHAR2(4000);
       l_payload VARCHAR2(4000);
       l_signature VARCHAR2(4000);
       l_user_id NUMBER;
       l_expiration_date DATE;
     BEGIN
       -- JWT decoding
       l_header := utl_raw.cast_to_varchar2(
         utl_encode.base64_decode(
           utl_raw.cast_to_raw(regexp_substr(p_token, '^[^\.]+'))
         )
       );
       l_payload := utl_raw.cast_to_varchar2(
         utl_encode.base64_decode(
           utl_raw.cast_to_raw(regexp_substr(p_token, '\.[^\.]+\.')))
       );
       l_signature := utl_raw.cast_to_varchar2(
         utl_encode.base64_decode(
           utl_raw.cast_to_raw(regexp_substr(p_token, '\.[^\.]+$'))
         )
       );
    
       -- JWT signature verification
       IF dbms_crypto.mac(
         utl_raw.cast_to_raw(l_header || '.' || l_payload),
         dbms_crypto.hmac_sh256,
         utl_raw.cast_to_raw(p_secret_key)
       ) <> utl_raw.cast_to_raw(l_signature) THEN
         RETURN NULL;
       END IF;
    
       -- JWT expiration check
       SELECT user_id, expiration_date INTO l_user_id, l_expiration_date
       FROM jwt_tokens
       WHERE token = p_token;
    
       IF l_expiration_date < SYSDATE THEN
         RETURN NULL;
       END IF;
    
       RETURN l_user_id;
     END;
    
     4. Create a procedure to save the JWT token in the database:
    
     CREATE OR REPLACE PROCEDURE save_jwt_token(
       p_token IN VARCHAR2,
       p_user_id IN NUMBER,
       p_expiration_date IN DATE
     ) AS
     BEGIN
       INSERT INTO jwt_tokens(token, user_id, expiration_date)
       VALUES(p_token, p_user_id, p_expiration_date);
     END;
    
     5. An example of using procedures and functions for user authentication through JWT:
    
     DECLARE
       l_token VARCHAR2(4000);
       l_user_id NUMBER;
     BEGIN
       -- JWT token generation
       generate_jwt_token(123, SYSDATE + 1, 'secret_key', l_token);
    
       -- Saving the JWT token in the database
       save_jwt_token(l_token, 123, SYSDATE + 1);
    
       -- JWT token validation
       l_user_id := verify_jwt_token(l_token, 'secret_key');
    
       IF l_user_id IS NULL THEN
         dbms_output. put_line('Invalid token');
       ELSE
         dbms_output.put_line('User ID: ' || l_user_id);
       END IF;
     END;