Cannot Decrypt Varchar2 password using DBMS_CRYPTO.DECRYPT

Question

what i need

• i need to decrypt password store in database.

I tried sql

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm#i1001225

sql

                DECLARE
                   input_string       VARCHAR2 (200) :=  'Secret Message';
                  encrypted_answer_raw  RAW(2000);             
                   output_string      VARCHAR2 (200);
                   encrypted_raw      RAW (2000);             -- stores encrypted binary text
                   decrypted_raw      RAW (2000);             -- stores decrypted binary text
                   num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
                   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
                   encryption_type    PLS_INTEGER :=          -- total encryption type
                                            DBMS_CRYPTO.ENCRYPT_AES256
                                          + DBMS_CRYPTO.CHAIN_CBC
                                          + DBMS_CRYPTO.PAD_PKCS5;
                BEGIN
                   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
                   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
                   encrypted_raw := DBMS_CRYPTO.ENCRYPT
                      (
                         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
                         typ => encryption_type,
                         key => key_bytes_raw
                      );
                    -- The encrypted value "encrypted_raw" can be used here
                    debug_msg('encrypted_raw'||encrypted_raw);

Decryption code

Code Works

                  decrypted_raw := DBMS_CRYPTO.DECRYPT
                      (
                         src => encrypted_raw,
                         typ => encryption_type,
                         key => key_bytes_raw
                      );

                   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

Code Doesn't works

                  encrypted_answer_raw:=utl_raw.cast_to_raw('989628CCF16292A73FEB63D4694C8129');
                   decrypted_raw := DBMS_CRYPTO.DECRYPT
                      (
                         src => encrypted_answer_raw,
                         typ => encryption_type,
                         key => key_bytes_raw
                      );

                   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

                        DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
                END;

Error

        Error report -
        ORA-28817: PL/SQL function returned an error.
        ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
        ORA-06512: at "SYS.DBMS_CRYPTO", line 44
        ORA-06512: at line 25
        28817. 00000 -  "PL/SQL function returned an error."
        *Cause:    A PL/SQL function returned an error unexpectedly.
        *Action:   This is an internal error. Enable tracing to find more
                   information. Contact Oracle customer support if needed.
        *Document: NO

989628CCF16292A73FEB63D4694C8129 is database enrypted password stored in db.

• any help is most welcome.

Answer 1

I suspect that instead of doing

encrypted_answer_raw := utl_raw.cast_to_raw('989628CCF16292A73FEB63D4694C8129');

you may want to do

encrypted_answer_raw := hextoraw('989628CCF16292A73FEB63D4694C8129');

I believe the problem is that the password is not really 989628CCF16292A73FEB63D4694C8129, but rather –(Ěńb’§?ëcÔiL) (displayed in win1250 codepage), despite your Oracle client software showing raw columns as hexa strings.

As a sidenote...

All those comments under your question, stating that you should store+compare password hashes instead of encrypting+decrypting them, are valid.