Examples of Using the Data Encryption API

Examples of using the data encryption API include using the DBMS_CRYPTO.SQL procedure, encrypting AES 256-bit data, and encrypting BLOB data.

Example: Data Encryption Procedure
The DBMS_CRYPTO.SQL PL/SQL program can be used to encrypt data.
Example: AES 256-Bit Data Encryption and Decryption Procedures
You can use a PL/SQL block to encrypt and decrypt a predefined variable.
Example: Encryption and Decryption Procedures for BLOB Data
You can encrypt BLOB data.
Example: Encrypting or Decrypting a Number String
You can use the DBMS_CRYPTO PL/SQL package to create functions that will perform the on-demand encryption or decryption of a number string.
Example: Deriving an Encryption Key from a Password by Using PBKDF2
You can use DBMS_CRYPTO to derive cryptographic key material from a password or passphrase by using Password-Based Key Derivation Function 2 (PBKDF2).

Example: Data Encryption Procedure

The DBMS_CRYPTO.SQL PL/SQL program can be used to encrypt data.

This example code performs the following actions:

Encrypts a string (VARCHAR2 type) using DES after first converting it into the RAW data type.

This step is necessary because encrypt and decrypt functions and procedures in DBMS_CRYPTO package work on the RAW data type only.
Shows how to create a 160-bit hash using SHA-1 algorithm.
Demonstrates how MAC, a key-dependent one-way hash, can be computed using the MD5 algorithm. (Starting in Oracle Database release 21c, the MD5 algorithm has been deprecated.)

The DBMS_CRYPTO.SQL procedure follows:

DECLARE
    input_string     VARCHAR2(16) := 'tigertigertigert';
    raw_input        RAW(128) :=
UTL_RAW.CAST_TO_RAW(CONVERT(input_string,'AL32UTF8','US7ASCII'));
    key_string       VARCHAR2(16)  := 'scottscoscottsco';
    raw_key          RAW(128) :=
UTL_RAW.CAST_TO_RAW(CONVERT(key_string,'AL32UTF8','US7ASCII'));
    encrypted_raw    RAW(2048);
    encrypted_string VARCHAR2(2048);
    decrypted_raw    RAW(2048);
    decrypted_string VARCHAR2(2048);
-- Begin testing Encryption:
BEGIN
    dbms_output.put_line('> Input String                     : ' ||
    CONVERT(UTL_RAW.CAST_TO_VARCHAR2(raw_input),'US7ASCII','AL32UTF8'));
    dbms_output.put_line('> ========= BEGIN TEST Encrypt =========');
    encrypted_raw := dbms_crypto.Encrypt(
        src => raw_input,
        typ => DBMS_CRYPTO.AES_CBC_PKCS5,
        key => raw_key);
        dbms_output.put_line('> Encrypted hex value              : ' ||
        rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
decrypted_raw := dbms_crypto.Decrypt(
        src => encrypted_raw,
        typ => DBMS_CRYPTO.AES_CBC_PKCS5,
        key => raw_key);
    decrypted_string :=
    CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');
dbms_output.put_line('> Decrypted string output          : ' ||
        decrypted_string);
if input_string = decrypted_string THEN
    dbms_output.put_line('> String DES Encyption and Decryption successful');
END if;
dbms_output.put_line('');
dbms_output.put_line('> ========= BEGIN TEST Hash =========');
    encrypted_raw := dbms_crypto.Hash(
        src => raw_input,
        typ => DBMS_CRYPTO.HASH_SH1);
dbms_output.put_line('> Hash value of input string       : ' ||
        rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
dbms_output.put_line('> ========= BEGIN TEST Mac =========');
    encrypted_raw := dbms_crypto.Mac(
        src => raw_input,
        typ => DBMS_CRYPTO.HMAC_MD5,
        key => raw_key);
dbms_output.put_line('> Message Authentication Code      : ' ||
        rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)));
dbms_output.put_line('');
dbms_output.put_line('> End of DBMS_CRYPTO tests  ');
END;
/

Example: AES 256-Bit Data Encryption and Decryption Procedures

You can use a PL/SQL block to encrypt and decrypt a predefined variable.

For the following example, the predefined variable is named input_string and it uses the AES 256-bit algorithm with Cipher Block Chaining and PKCS #5 padding:

declare
   input_string       VARCHAR2 (200) := 'Secret Message';
   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 in the encrypted_raw variable can be used here:
   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');
   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
end;

Example: Encryption and Decryption Procedures for BLOB Data

You can encrypt BLOB data.

The following sample PL/SQL program (blob_test.sql) shows how to encrypt and decrypt BLOB data. This example code does the following, and prints out its progress (or problems) at each step:

Creates a table for the BLOB column
Inserts the raw values into that table
Encrypts the raw data
Decrypts the encrypted data

The blob_test.sql procedure follows:

-- 1. Create a table for BLOB column:
create table table_lob (id number, loc blob);

-- 2. Insert 3 empty lobs for src/enc/dec:
insert into table_lob values (1, EMPTY_BLOB());
insert into table_lob values (2, EMPTY_BLOB());
insert into table_lob values (3, EMPTY_BLOB());

set echo on
set serveroutput on

declare
    srcdata    RAW(1000);
    srcblob    BLOB;
    encrypblob BLOB;
    encrypraw  RAW(1000);
    encrawlen  BINARY_INTEGER;
    decrypblob BLOB;
    decrypraw  RAW(1000);
    decrawlen  BINARY_INTEGER;

    leng       INTEGER;

begin

    -- RAW input data 16 bytes
    srcdata := hextoraw('6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D');

    dbms_output.put_line('---');
    dbms_output.put_line('input is ' || srcdata);
    dbms_output.put_line('---');

    -- select empty lob locators for src/enc/dec
    select loc into srcblob from table_lob where id = 1;
    select loc into encrypblob from table_lob where id = 2;
    select loc into decrypblob from table_lob where id = 3;

    dbms_output.put_line('Created Empty LOBS');
    dbms_output.put_line('---');

    leng := DBMS_LOB.GETLENGTH(srcblob);
    IF leng IS NULL THEN
        dbms_output.put_line('Source BLOB Len NULL ');
    ELSE
        dbms_output.put_line('Source BLOB Len ' || leng);
    END IF;

    leng := DBMS_LOB.GETLENGTH(encrypblob);
    IF leng IS NULL THEN
        dbms_output.put_line('Encrypt BLOB Len NULL ');
    ELSE
        dbms_output.put_line('Encrypt BLOB Len ' || leng);
    END IF;

    leng := DBMS_LOB.GETLENGTH(decrypblob);
    IF leng IS NULL THEN
        dbms_output.put_line('Decrypt  BLOB Len NULL ');
    ELSE
        dbms_output.put_line('Decrypt BLOB Len ' || leng);
    END IF;

   -- 3. Write source raw data into blob:
    DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.WRITEAPPEND (srcblob, 16, srcdata);
    DBMS_LOB.CLOSE (srcblob);

    dbms_output.put_line('Source raw data written to source blob');
    dbms_output.put_line('---');

    leng := DBMS_LOB.GETLENGTH(srcblob);
    IF leng IS NULL THEN
        dbms_output.put_line('source BLOB Len NULL ');
    ELSE
        dbms_output.put_line('Source BLOB Len ' || leng);
    END IF;

    /*
    * Procedure Encrypt
    * Arguments: srcblob -> Source BLOB
    * encrypblob -> Output BLOB for encrypted data
    * DBMS_CRYPTO.AES_CBC_PKCS5 -> Algo : AES
    * Chaining : CBC
    * Padding : PKCS5
    * 256 bit key for AES passed as RAW
    * ->
    hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
    * IV (Initialization Vector) for AES algo passed as RAW
    * -> hextoraw('00000000000000000000000000000000')
    */

    DBMS_CRYPTO.Encrypt(encrypblob,
                srcblob,
                DBMS_CRYPTO.AES_CBC_PKCS5,
                hextoraw ('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
                hextoraw('00000000000000000000000000000000'));

    dbms_output.put_line('Encryption Done');
    dbms_output.put_line('---');

    leng := DBMS_LOB.GETLENGTH(encrypblob);
    IF leng IS NULL THEN
        dbms_output.put_line('Encrypt BLOB Len NULL');
    ELSE
        dbms_output.put_line('Encrypt BLOB Len ' || leng);
    END IF;

   -- 4. Read encrypblob to a raw:
    encrawlen := 999;

    DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.READ (encrypblob, encrawlen, 1, encrypraw);
    DBMS_LOB.CLOSE (encrypblob);

    dbms_output.put_line('Read encrypt blob to a raw');
    dbms_output.put_line('---');

    dbms_output.put_line('Encrypted data is (256 bit key) ' || encrypraw);
    dbms_output.put_line('---');

    /*
    * Procedure Decrypt
    * Arguments: encrypblob -> Encrypted BLOB to decrypt
    * decrypblob -> Output BLOB for decrypted data in RAW
    * DBMS_CRYPTO.AES_CBC_PKCS5 -> Algo : AES
    * Chaining : CBC
    * Padding : PKCS5
    * 256 bit key for AES passed as RAW (same as used during Encrypt)
    * ->
    hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
    * IV (Initialization Vector) for AES algo passed as RAW (same as
                 used during Encrypt)
    * -> hextoraw('00000000000000000000000000000000')
    */

    DBMS_CRYPTO.Decrypt(decrypblob,
                encrypblob,
                DBMS_CRYPTO.AES_CBC_PKCS5,
                hextoraw
           ('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
                hextoraw('00000000000000000000000000000000'));

    leng := DBMS_LOB.GETLENGTH(decrypblob);
    IF leng IS NULL THEN
        dbms_output.put_line('Decrypt BLOB Len NULL');
    ELSE
        dbms_output.put_line('Decrypt BLOB Len ' || leng);
    END IF;

    -- Read decrypblob to a raw
    decrawlen := 999;

    DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.READ (decrypblob, decrawlen, 1, decrypraw);
    DBMS_LOB.CLOSE (decrypblob);

    dbms_output.put_line('Decrypted data is (256 bit key) ' || decrypraw);
    dbms_output.put_line('---');

    DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.TRIM (srcblob, 0);
    DBMS_LOB.CLOSE (srcblob);

    DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.TRIM (encrypblob, 0);
    DBMS_LOB.CLOSE (encrypblob);

    DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
    DBMS_LOB.TRIM (decrypblob, 0);
    DBMS_LOB.CLOSE (decrypblob);

end;
/

truncate table table_lob;
drop table table_lob;

Example: Encrypting or Decrypting a Number String

You can use the DBMS_CRYPTO PL/SQL package to create functions that will perform the on-demand encryption or decryption of a number string.

The following procedure provides an example of how you can create and use functions to encrypt and decrypt number strings. It also provides an example of testing how the functions work by inserting a converted number string into a table.

Create a function that will encrypt a number string.

The following example function, f_encrypt_number, uses the input value number_in, the return value as the raw type, and DES_CBC_PKCS5 as the encryption algorithm.

CREATE OR REPLACE FUNCTION f_encrypt_number(number_in IN NUMBER)
 RETURN RAW IS
  number_in_raw RAW(128):=UTL_I18N.STRING_TO_RAW(number_in,'AL32UTF8');
  key_number number(32):=32432432343243279898;
  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  encrypted_raw RAW(128);
BEGIN
  encrypted_raw:=DBMS_CRYPTO.ENCRYPT(src=>number_in_raw,typ=>DBMS_CRYPTO.DES_CBC_PKCS5,key=>key_raw);
  RETURN encrypted_raw;
END;
/

Run the function f_encrypt_number to encrypt the number string 2.

SELECT f_encrypt_number('2') FROM DUAL;

The result in this example is 84A8B8D7D8925582:

F_ENCRYPT_NUMBER('2')

--------------------------------------------------------------------------------
84A8B8D7D8925582

Create a function to decrypt a number string.

The following example function, f_decrypt_number, can decrypt an encrypted raw value encrypted_raw. The input is encrypted_raw. It uses DES_CBC_PKCS5 as the decryption algorithm

CREATE OR REPLACE FUNCTION f_decrypt_number (encrypted_raw IN RAW)
 RETURN NUMBER IS
  decrypted_raw raw(48);
  key_number number(32):=32432432343243279898;
  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
BEGIN
  decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => DBMS_CRYPTO.DES_CBC_PKCS5,
         key => key_raw
      );
RETURN   UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
END;
/

Run the function f_decrypt_number to decrypt 84A8B8D7D8925582.

SELECT f_decrypt_number('84A8B8D7D8925582') FROM DUAL;

The result is 2:

F_DECRYPT_NUMBER('84A8B8D7D8925582')

------------------------------------
                                   2

Test the encrypted number string.

In this test, you run f_encrypt_number to encrypt number 2. (The result should be 84A8B8D7D8925582). Then you insert (f_encrypt_number('2'), username) into table test_dbms_crypto. You will be able to see 84A8B8D7D8925582 username inserted to the table. When you run f_encrypt_number to decrypt the ID 84A8B8D7D8925582, the result is 2.
1. Insert the encrypted number string into the test_dbms_crypto table.
```
INSERT INTO test_dbms_crypto VALUES (f_encrypt_number('2'),'username');
1 row created.
COMMIT;
Commit complete.
```
2. Select from the test_dbms_crypto table.
```
SELECT * FROM test_dbms_crypto;
```
  The following output should appear:
```
ID                             NAME

--------------------------     --------------------
84A8B8D7D8925582               username
```
3. Select from the test_dbms_crypto table.
```
SELECT f_decrypt_number(id), NAME FROM test_dbms_crypto ;
```
  The following output should appear:
```
F_DECRYPT_NUMBER(ID) NAME

-------------------- --------------------
                   2 username
```

Example: Deriving an Encryption Key from a Password by Using PBKDF2

You can use DBMS_CRYPTO to derive cryptographic key material from a password or passphrase by using Password-Based Key Derivation Function 2 (PBKDF2).

The following example shows how to use DBMS_CRYPTO.PBKDF2 to derive cryptographic key material from a password or passphrase. The example also uses DBMS_CRYPTO.RANDOMBYTES to randomly generate a cryptographically secure salt.

DECLARE
  v_pass RAW(2000);
  v_salt RAW(16);
  v_key  RAW(32);
BEGIN
  v_pass := UTL_I18N.STRING_TO_RAW('password_or_passphrase', 'AL32UTF8');
  v_salt := DBMS_CRYPTO.RANDOMBYTES(16);

  v_key := DBMS_CRYPTO.PBKDF2(
             typ    => DBMS_CRYPTO.KDF2_HMAC_SHA2_256,
             pass   => v_pass,
             salt   => v_salt,
             iter   => 10000,
             keylen => 32);

  -- Use v_key with DBMS_CRYPTO.ENCRYPT or other DBMS_CRYPTO APIs.
END;
/