Mimic AES_ENCRYPT and AES_DECRYPT functions in Ruby

Question

Hello there!

I need to mimic what MySQL does when encrypting and decrypting strings using built-in functions AES_ENCRYPT() and AES_DECRYPT().

I have read a couple of blog posts and apparently MySQL uses AES 128-bit encryption for those functions. On top of that, since this encryption requires a 16-bit key, MySQL pads the string with x0 chars (\0s) until it's 16-bit in size.

The algorithm in C from MySQL source code is spotted here.

Now I need to replicate what MySQL does in a Rails application, but every single thing I tried, doesn't work.

Here's a way to replicate the behavior I am getting:

1) Create a new Rails app

rails encryption-test
cd encryption-test

2) Create a new scaffolding

script/generate scaffold user name:string password:binary

3) Edit your config/database.yml and add a test MySQL database

development:
 adapter: mysql
 host: localhost
 database: test
 user: <<user>>
 password: <<password>>

4) Run the migration

rake db:migrate

5) Enter console, create an user and update its password from MySQL query

script/console
Loading development environment (Rails 2.2.2)
>> User.create(:name => "John Doe")
>> key = "82pjd12398JKBSDIGUSisahdoahOUASDHsdapdjqwjeASIduAsdh078asdASD087asdADSsdjhA7809asdajhADSs"
>> ActiveRecord::Base.connection.execute("UPDATE users SET password = AES_ENCRYPT('password', '#{key}') WHERE name='John Doe'")

That's where I got stuck. If I attempt to decrypt it, using MySQL it works:

>> loaded_user = User.find_by_sql("SELECT AES_DECRYPT(password, '#{key}') AS password FROM users WHERE id=1").first
>> loaded_user['password']
=> "password"

However if I attempt to use OpenSSL library, there's no way I can make it work:

cipher = OpenSSL::Cipher::Cipher.new("AES-128-ECB") 
cipher.padding = 0
cipher.key = key
cipher.decrypt 

user = User.find(1)
cipher.update(user.password) << cipher.final #=> "########gf####\027\227"

I have tried padding the key:

desired_length = 16 * ((key.length / 16) + 1)
padded_key = key + "\0" * (desired_length - key.length)

cipher = OpenSSL::Cipher::Cipher.new("AES-128-ECB") 
cipher.key = key
cipher.decrypt 

user = User.find(1)
cipher.update(user.password) << cipher.final #=> ""|\e\261\205:\032s\273\242\030\261\272P##"

But it really doesn't work.

Does anyone have a clue on how can I mimic the MySQL AES_ENCRYPT() and AES_DECRYPT() functions behavior in Ruby?

Thanks!

Answer 1

Generally you don't want to pad the key, you pad/unpad the data to be encrypted/decrypted. That could be another source of problems. I suggest using test data of a complete number of blocks to eliminate this possibility.

Also, I suspect the key for the OpenSSL API requires a "literal" key, not an ASCII representation of the key as you have in your code.

Given the paucity of the OpenSSL ruby docs and if you speak a little Java, you may want to prototype in JRuby with the BouncyCastle provider - this is something that I've done to good effect when working with TwoFish (not present in OpenSSL API).

EDIT: I re-read your comment about padding the key. You have some bits/bytes confusion in your question, and I'm not sure how this applies in any case since your posted key is 89 characters (712 bits) in length. Perhaps you should try with a 128 bit key/password to eliminate this padding phenomenon?

Incidentally, MySQL devs should be spanked for weak crypto, there are better ways to stretch passwords than by simply padding with zero bytes :(

Answer 2

For future reference:

According to the blog post I sent before, here's how MySQL works with the key you provide AES_ENCRYPT / DECRYPT:

"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."

I don't know if you can read C, but here's the mentioned snippet:

http://pastie.org/425161

Specially this part:

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 came up with this method (with a help from Rob Biedenharn, from ruby forum):

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

That, given a string returns the key MySQL uses when encrypting and decrypting. So all you need now is:

def aes(m,k,t)
  (aes = OpenSSL::Cipher::AES128.new("ECB").send(m)).key = k
  aes.update(t) << aes.final
end

def encrypt(key, text)
  aes(:encrypt, key, text)
end

def decrypt(key, text)
  aes(:decrypt, key, text)
end

To use openssl lib, built into ruby, and then you can make the two "final" methods:

def mysql_encrypt(s, key)
  encrypt(mysql_key(key), s)
end

def mysql_decrypt(s, key)
  decrypt(mysql_key(key), s)
end

And you're set! Also, complete code can be found in this Gist:

http://gist.github.com/84093

:-)

Answer 3

If you don't mind using an openssl implementation attr_encrypted is a gem that will allow drop-in encryption on most classes, ActiveRecord or not. It unfortunately will not be compatible with MySQL's AES_EN/DECRYPT functions though.