DBMS_OBFUSCATION_TOOLKIT | PLSQL Script To ENCRYPT/DECRYPT A String

Oracle has provided a very useful utility called DBMS_OBFUSCATION_TOOLKIT to encrypt or decrypt a character string.

Following points should be noted while running the provided script:

If the input data or key given to the PL/SQL DESDECRYPT function is empty, then Oracle raises ORA error 28231 "Invalid input to Obfuscation toolkit."

If the input data given to the DESDECRYPT function is not a multiple of 8 bytes, Oracle raises ORA error 28232 "Invalid input size for Obfuscation toolkit."

If the key length is missing or is less than 8 bytes, then the procedure raises the error ORA-28234 "Key length too short." Note that if larger keys are used, extra bytes are ignored. So a 9-byte key will not generate an exception.

SCRIPT:

SET serveroutput ON;

DECLARE

   lv_encrypted_data              VARCHAR2 (2000);

   lv_decrypted_data              VARCHAR2 (2000);

   piv_str                        VARCHAR2 (2000) :='www.sqlplsqloracleappstutorials.com';

   piv_pass_key                   VARCHAR2 (2000) := 'nisheethr';

   a_var                          VARCHAR2 (100);

   error_in_input_buffer_length   EXCEPTION;

   PRAGMA EXCEPTION_INIT (error_in_input_buffer_length, -28232);

   input_buffer_length_err_msg    VARCHAR2 (100)

                      := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';

BEGIN

   DBMS_OUTPUT.put_line (   'Input_string->:'

                         || piv_str

                         || CHR (10)

                         || 'LENGTH OF STRING=>'

                         || LENGTH (piv_str)

                        );

--  Since DES needs data to be in multples of 8 bytes we had padded the data, if the

--  data did not meet the 8 bytes boundry requirment. So now after decrypting we

--  would need to remove the padding if it exists

   IF MOD (LENGTH (piv_str), 8) != 0

   THEN

      piv_str :=

         RPAD (piv_str,

               LENGTH (piv_str) + 8 - MOD (LENGTH (piv_str), 8),

               CHR (0)

              );

   END IF;

   lv_encrypted_data :=

      DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string      => piv_str,

                                           key_string        => piv_pass_key

                                          );

   DBMS_OUTPUT.put_line ('Encrypted Data: ' || lv_encrypted_data);

   lv_decrypted_data :=

      DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string      => lv_encrypted_data,

                                           key_string        => piv_pass_key

                                          );

   -- This is to remove the padded chracters

   lv_decrypted_data :=

       SUBSTR (lv_decrypted_data, 1, INSTR (lv_decrypted_data, CHR (0), 1) - 1);

   DBMS_OUTPUT.put_line (   'decrypt_string->:'

                         || lv_decrypted_data

                         || CHR (10)

                         || 'LENGTH OF STRING=>'

                         || LENGTH (lv_decrypted_data)

                        );

EXCEPTION

   WHEN error_in_input_buffer_length

   THEN

      DBMS_OUTPUT.put_line ('> ' || input_buffer_length_err_msg);

END;