mysql: encrypting and decrypting data

Question

Does mysql provide a mechanism for storing and retrieving encrypted data? I don't mean passwords, I mean real strings.

I'd like to encrypt a string, store in mysql and then retrieve the decrypted string at a later date.

So, I know there is the AES_Encrypt and decrypt functions. But they ask for a key. (which is fine) but I wondering if you call those functions and use your user password as the key. Or something else that is super-simple.

Also, is there a simple wrapper for the AES_Encrypt & decrypt functions in Rails? Or do you need to build the query manually?

Solution 1

If I understand you, then all you need is a method to generate an AES key from your (or other) user password?

Shouldn't you be asking 'Is there an easy method to generate an AES-key from 5-20char string'?

As you point out, the other tools are already in place in mysql: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

Also you might find some ideas in this post here on SO.

Solution 2

You can just concat the encrypt functions:

select aes_encrypt('MyData',Password('MyPassword'))

and back again..

select Aes_decrypt( aes_encrypt('MyData',Password('MyPassword'))
     , Password('MyPassword'))

Comments

[Bimal Poudel]

Be sure to re-encrypt the original data if the password is about to change.

[JJJ]

MD5 is not an encryption algorithm. Never use MD5 to "encrypt" passwords.