From: Felipe Coury <felipe.coury@...>
Date: 2009-03-24T12:21:57+09:00
Subject: Mimic AES_ENCRYPT and AES_DECRYPT functions in Ruby

Hello there!

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

Even though the application I am writing is a Rails application, I think
this question suited here better because, the encryption will take place
in Ruby and not necessarily depends on Rails.

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:
http://gtowey.blogspot.com/2009/01/mysql-aes-encryption-compatability.html

I have even tried to examine MySQL's C source code, but that didn't help
me much, since I can't really program in C. Maybe someone with a little
more experience can have some insights.

The source code that implements the encryption (rijndaelKeySetupEnc) and
decryption (rijndaelKeySetupDec) functions is here:

http://pastie.org/425070

And the actual AES_ENCRYPT (function my_aes_encrypt) and AES_DECRYPT
(my_aes_decrypt) source code is here:

http://pastie.org/425073

Please note that the necessity of using MySQL's compliancy was not my
call and is not a choice. I need that in order to communicate properly
with a legacy application, and I don't "own" this database. Please take
into consideration that security is definitely not the goal, talking to
that system properly is. The key length was not chosen by me and I know
it's a little peculiar, as you'll see below, on my replication "script".

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 (in this case, using
Rails):

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 properly mimic whatever MySQL is
doing in Ruby?

Thanks a lot for your help.

Cheers,

-- Felipe.
-- 
Posted via http://www.ruby-forum.com/.