CODEWITHSUNDEEP
sqlencryptionplsqloracle11g
Ian
Ian

Reputation: 107

Trouble With Encryption in Oracle11g

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;
/

Upvotes: 0

Views: 312

Answers (1)

user272735
user272735

Reputation: 10648

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.

Upvotes: 1

Related Questions