Mimicking MySQL's AES_ENCRYPT and AES_DECRYPT in pure Ruby

Posted by admin, Tue Mar 24 11:27:00 UTC 2009

I had a problem where I needed to interact with a legacy database that relies on MySQL’s AES_ENCRYPT and AES_DECRYPT functions for data encryption for a couple of fields.

After searching “the intertnets” for it, no ready-made solution was found. So, I started with MySQL documentation on the functions:

AES_ENCRYPT() and AES_DECRYPT() allow encryption and decryption of data using the official AES (Advanced Encryption Standard) gorithm, previously known as “Rijndael.” Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes.

AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.

Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:

16 × (trunc(string_length / 16) + 1)

If AESDECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AESDECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

However, after doing a lot of experimentation (as pointed out on this two posts), I downloaded MySQL code and, even though C is not my thing, I figured something out (with the help of this other blog post):

“The algorithm just creates a 16 byte buffer set to all zero, then loops through all the characters of the string you provide and does an assignment with bitwise OR between the two values. If we iterate until we hit the end of the 16 byte buffer, we just start over from the beginning doing ^=. For strings shorter than 16 characters, we stop at the end of the string.”

And here’s the relevant snippet of MySQL’s source file my_aes.c:

bzero((char*) rkey,AES_KEY_LENGTH/8);      /* Set initial key  */

for (ptr= rkey, sptr= key; sptr < key_end; ptr++,sptr++)
{
  if (ptr == rkey_end)
    ptr= rkey;  /*  Just loop over tmp_key until we used all key */
  *ptr^= (uint8) *sptr;
}

So, I finally came up with the method below, that replicates the same behavior, in Ruby (kudos to Rob Biedenharn, from Ruby Forum, who helped me to refactor this):

def mysql_key(key)
   final_key = "\0" * 16
   key.length.times do |i|
     final_key[i%16] ^= key[i]
   end
   final_key
end

Once you have the correct key, everything becomes a lot simpler. I have used OpenSSL::Cipher::AES128.new("ECB") from openssl built-in library and things worked just fine.

If you ever need to replicate the behavior of those functions on your own Ruby code, here’s a permanent link for a Gist with complete source code:

http://gist.github.com/84093

Hope it helps ;)

Filed Under: Ruby | Tags: decryption encryption mysql ruby