Oracle PL/SQL for DBAs

Decrypting Data

So far, I have focused on encrypting the data; let’s see how to decrypt it to get back the original value with the DES3DECRYPT function . In the following PL/SQL block, I will create an encrypted value from cleartext and then decrypt it.

    DECLARE
       l_enc_val   VARCHAR2 (2000);
       l_dec_val   VARCHAR2 (2000) := 'Clear Text Data';
       l_key       VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP';
    BEGIN
       l_enc_val := get_enc_val (l_dec_val, l_key, '12345678');
       l_dec_val :=
          DBMS_OBFUSCATION_TOOLKIT.des3decrypt
                  (input_string      => UTL_RAW.cast_to_varchar2
                                                              (HEXTORAW (l_enc_val)
                                                              ),
                   key_string        => l_key
                  );
       DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val);
    END;
    /

The output is:

    Decrypted Value = s}?2+¬xt Data

    PL/SQL procedure successfully completed.

Wait! The decrypted value is different from the input given. What went wrong?

Note the parameters to the DES3DECRYPT function. Have you supplied the IV to it? Because an IV was specified during the encryption process, it must be specified during decryption, as well. Let’s rewrite the block with the IV value of 12345678:

    DECLARE
       l_enc_val   VARCHAR2 (2000);
       l_dec_val   VARCHAR2 (2000) := 'Clear Text Data';
       l_key       VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP';
    BEGIN
       l_enc_val := get_enc_val (l_dec_val, l_key, '12345678');
       l_dec_val :=
          DBMS_OBFUSCATION_TOOLKIT.des3decrypt
                  (input_string      => UTL_RAW.cast_to_varchar2
                                                              (HEXTORAW (l_enc_val)
                                                              ),
                   key_string        => l_key,
                   iv_string         => '12345678'
                  );
       DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val);
    END;
    /

The output is as expected: ...