AES256 encryption with Perl and Oracle

Recently I had to implement some AES256 encryption for a project I was working on and needed to do it with Perl, but also have it play nice with Oracle just in case the encryption/decryption needed to be done on the database side with SQL.

Sounds simple, but I struggled for a day or two because I wrongly chose Crypt::OpenSSL::AES instead of Crypt::Rijndael. Google seemed to fail me on that one as searching for "aes encryption perl" brings up the former package. I also tried #perl on irc.freenode.net, but that was a big fail as well. Seems you can't expect much help from the monks unless you need simple regex help.

Anywho, here's how you can accomplish this using Perl and Oracle SQL. First we'll use Perl to encrypt a string and then Base64 encode it so it's easy to manipulate and store (otherwise you'll have to deal with binary data).

Perl code

use Crypt::CBC;
use MIME::Base64;

my $cipher = Crypt::CBC->new({
	key			=> "NSTl4XsUKcOWzHVgci9vPF5jZ8Szscz7", # 256 bits
	cipher		=> "Crypt::Rijndael",
	iv			=> "a41ag2wqDSQjzexC", # 128 bits
	literal_key	=> 1,
	header		=> "none",
	keysize		=> 32 # 256/8
});

my $encrypted = $cipher->encrypt("string to encrypt");

# base64 encode so we can store in db
$encrypted = encode_base64($encrypted);

# remove trailing newline inserted by encode_base64
chomp($encrypted);

print "encrypted result: $encrypted \n";

Note that I used a static initialization vector (IV) in my example. Depending on your implementation you may want to let Crypt::Rijndael generate an IV for you. Just remove the iv and header parameters in that case.

Now you can use Oracle SQL to do the decryption. Just replace S06LglP0qfqHVcYXsLeDd1dsCQUrfCpZpJ4tYckk6Ic= with the output of the Perl encrypted string and you should be good to go.

Oracle SQL code

SET serveroutput on;

DECLARE
    encryption_type PLS_INTEGER :=
        DBMS_CRYPTO.ENCRYPT_AES256
        + DBMS_CRYPTO.CHAIN_CBC
        + DBMS_CRYPTO.PAD_PKCS5;
    key_bytes_raw       RAW (32);           -- 256 bit encryption key
    iv                  RAW(32);            -- 128 bit initialization vector
    encrypted_raw       RAW (2000);         -- stores encrypted binary text
    decrypted_raw       RAW (2000);         -- stores decrypted binary text
    decrypted_string    VARCHAR2 (200);

BEGIN
    key_bytes_raw := UTL_I18N.STRING_TO_RAW('NSTl4XsUKcOWzHVgci9vPF5jZ8Szscz7', 'AL32UTF8');
    iv := UTL_I18N.STRING_TO_RAW('a41ag2wqDSQjzexC','AL32UTF8');

    encrypted_raw := UTL_I18N.STRING_TO_RAW('S06LglP0qfqHVcYXsLeDd1dsCQUrfCpZpJ4tYckk6Ic=','AL32UTF8'); -- base64 encoded and encrypted output from perl code above
    encrypted_raw := utl_encode.base64_decode(encrypted_raw);

    DBMS_OUTPUT.PUT_LINE ('Encrypted binary string: ' || encrypted_raw);

    decrypted_raw := DBMS_CRYPTO.DECRYPT
    (
        src => encrypted_raw,
        typ => encryption_type,
        key => key_bytes_raw,
        iv  => iv
    );

    decrypted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

    DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || decrypted_string);
END;

So far so good, we can decrypt a value, but really it's not that useful if we wanted to decrypt a whole bunch of rows in a table. We can take care of that by creating a Oracle function that takes an encrypted string and returns the decrypted value we need.

CREATE OR REPLACE
  FUNCTION aes256_decrypt(
      str    IN VARCHAR2,
      key IN VARCHAR2,
      iv  IN VARCHAR2)
    RETURN VARCHAR2
  IS
  BEGIN
    DECLARE
      encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
      key_bytes_raw RAW (32);
      iv_raw RAW(32);
      encrypted_raw RAW (2000);
      decrypted_raw RAW (2000);
      decrypted_string VARCHAR2 (200);
    BEGIN
      key_bytes_raw := UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8');
      iv_raw            := UTL_I18N.STRING_TO_RAW(iv,'AL32UTF8');
      encrypted_raw := UTL_I18N.STRING_TO_RAW(str,'AL32UTF8');
      encrypted_raw := utl_encode.base64_decode(encrypted_raw);

      decrypted_raw    := DBMS_CRYPTO.DECRYPT ( src => encrypted_raw, typ => encryption_type, KEY => key_bytes_raw, iv => iv_raw );
      decrypted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
      RETURN decrypted_string;
    EXCEPTION
    WHEN OTHERS THEN
      RETURN str;
    END;
  END aes256_decrypt;

You'll notice I also included 2 additional parameters, key and iv since you might not want to leave sensitive information like that in your database. Now we can do fun stuff like decrypt an entire column in a table.

-- select all rows from users table and decrypt the email column
select aes256_decrypt(email,'NSTl4XsUKcOWzHVgci9vPF5jZ8Szscz7','a41ag2wqDSQjzexC') from users;