redgoose.ca

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;

Comments

Very useful – Thank you

Jim Clark - Wednesday January 18, 2012

Thanks! I only spent 6 hours on this before getting here. I needed to encrypt in oracle and decrypt in perl.

I used all the same cipher settings in oracle and perl as above.

To encrypt for perl I just converted the encrypted raw variable using:
utl_encode.base64_encode(myencrypted_raw);

To decrypt in perl,
where $enc is the string from oracle:
$enc = pack(“H*”,$enc); #convert from hex
$enc = decode_base64($enc); #decode base 64
my $decrypted = $cipher->decrypt($enc);

Pete - Wednesday June 13, 2012

Dear Pete/Tariq,

thanks for your great tips. I am going to encrypt a file in Oracle with (AES256 + CBC + PKCS5) with a password over Initialization Vector and send it to customer to decrypt it on its machine without Oracle. it would be appreciated if you tell me how I can write an EXE file to open the encrypted file on the client machine and decrypt it.
i have hashed the IV with a pass key in oracle by following method:
iv := dbms_crypto.Mac(DBMS_CRYPTO.RANDOMBYTES, 1, UTL_I18N.STRING_TO_RAW (dbms_random.string(‘X’,6), ‘AL32UTF8’));

thanks and regards,
Mojtaba

MJ - Wednesday November 20, 2013

Very helpful!

I am trying to encrypt strings in Oracle but decrypt them in Perl.

Here, ‘decrypt_hex’ looks convenient for Oracle encrypted strings.

###############
use Crypt::CBC;

my $enc = ‘4B4E8B8253F4A9FA8755C617B0B78377576C09052B7C2A59A49E2D61C924E887’;

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 $decrypted = $cipher->decrypt_hex($enc);

print “decrypted result: $decrypted \n”;

####################
C:\Test>perl test-aes256-decrypt.pl
decrypted result: string to encrypt

Cho - Wednesday July 30, 2014

Say something






Textile Help

Hello

My name is Tariq. I am a twenty something website developer based in Toronto, Canada, working at kanetix doing what I love to do. Yeppers, I like turtles and get on (the TTC) daily.

Popular Posts

Subscribe

RSS Feed