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
TheDBMS_CRYPTO.SQLPL/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: 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 (
VARCHAR2type) using DES after first converting it into theRAWdata type.This step is necessary because encrypt and decrypt functions and procedures in
DBMS_CRYPTOpackage work on theRAWdata 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.
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(8) := 'scottsco';
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.DES_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.DES_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;