Trouble With Encryption in Oracle11g

Question

I am working on a lab where I am attempting to encrypt a dummy SSN and provide output in SQL Plus that shows the unencrypted SSN, encrypted SSN, and the decrypted SSN.

The value I am encrypting is 555 55 5555, however the decrypted value appears as 33353335333532303335333532303335333533353335. I'm not quite certain where this issue lies, since it appears at least some decryption is occurring. Any suggestions are greatly appreciated. SQL below

SET SERVEROUTPUT ON
DECLARE


 ssn VARCHAR2(200) := '555 55 5555';
 ssn_decrypt VARCHAR(200);
 ssn_raw RAW (200) := UTL_RAW.cast_to_raw(ssn);
 num_key_bytes NUMBER := 256/8;
 key_bytes_raw RAW (32);
 encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
 + DBMS_CRYPTO.CHAIN_CBC
 + DBMS_CRYPTO.PAD_PKCS5;
 encrypted_raw RAW (2000);
 decrypted_raw RAW(2000);


 BEGIN
    DBMS_OUTPUT.put_line('The Unencrypted SSN is: ' || ssn);
    key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
    encrypted_raw := DBMS_CRYPTO.encrypt

        (
            UTL_I18N.STRING_TO_RAW (ssn),
             typ => encryption_type,
            key => key_bytes_raw
        );

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

ssn_decrypt := UTL_I18N.RAW_TO_CHAR (decrypted_raw, '555 55 5555');
DBMS_OUTPUT.put_line('The Encrypted SSN is: ' ||
RAWTOHEX(UTL_RAW.cast_to_raw(encrypted_raw))); 
 DBMS_OUTPUT.put_line('The Decrypted SSN is:' || 
 RAWTOHEX(UTL_RAW.cast_to_raw(decrypted_raw)));
END;
/

user272735 · Accepted Answer · 2016-04-22 03:57:41Z

1

The example found from dbms_crypto documentation works flawlessly. Copy that carefully and you'll be good.

One issue is that you don't define the correct parameters in char vs. raw conversions. They should be:

 UTL_I18N.STRING_TO_RAW(ssn, 'AL32UTF8')
 UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

Note the example assumes your database character set is AL32UTF8. If not then you have to make that conversion too.

answered Apr 22, 2016 at 3:57

user272735

10.6k9 gold badges72 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble With Encryption in Oracle11g

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related