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.

Why do you need to decrypt a password? It wouldn't trust an application where you can do that at all - allways encryt the user input and compare it to the encrypted value should be all that you need. — Radagast81, Oct 30 '19 at 08:21
i will compare encrypt( user_input)==db_password instead of decrypting password from db — afeef, Oct 30 '19 at 08:30
but everytime during encryption i get new string how can i validate input string — afeef, Oct 30 '19 at 10:13
Don't use a random key for encryption but a fixed one or use a standard encryption method without key `DBMS_OBFUSCATION_TOOLKIT.md5` or `DBMS_CRYPTO.HASH` for example. — Radagast81, Oct 30 '19 at 10:18
i was using key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes); — afeef, Oct 30 '19 at 10:21
And that is not deterministic (you get definerent result every time you call it) and therefore the result of the encryption is also random - more or less. (The name `RANDOM...` indicates this). Once you generate the key, you have to store it for further usage. But as that allows decryption as well it's better to use a hash-algorithm, where the decryption is (most likely) not possible. — Radagast81, Oct 30 '19 at 10:25

peter.hrasko.sk · Answer 1 · 2019-11-05T23:05:45.757

2

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.

edited Nov 05 '19 at 23:05

answered Nov 05 '19 at 22:58

peter.hrasko.sk

4,043
2
19
34

@afeef, I agree with @nop77svk, you should absolutely be hashing and comparing the hashed values of the passwords. Use the MAC operation and have a different 'key' for each one and you'll effectively be *salting* the hash per password, which is standard security practice for cases like this. Following published algorithms, you could build to a standard password hash protocol, but I suspect you'll be satisfied with the `DBMS_CRYPTO.MAC` operation. – Andrew Philips May 08 '20 at 16:48

Cannot Decrypt Varchar2 password using DBMS_CRYPTO.DECRYPT

1 Answers1