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:
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:
And the actual AES_ENCRYPT (function my_aes_encrypt) and AES_DECRYPT
(my_aes_decrypt) source code is here:
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):
- Create a new Rails app
rails encryption-test
cd encryption-test
- Create a new scaffolding
script/generate scaffold user name:string password:binary
- Edit your config/database.yml and add a test MySQL database
development:
adapter: mysql
host: localhost
database: test
user: <>
password: <>
- Run the migration
rake db:migrate
- 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 D.”)
key = “82pjd12398JKBSDIGUSisahdoahOUASDHsdapdjqwjeASIduAsdh078asdASD087asdADSsdjhA7809asdajhADSs”
ActiveRecord::Base.connection.execute(“UPDATE users SET password = AES_ENCRYPT(‘password’, ‘#{key}’) WHERE name=‘John D.’”)
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.