MySQL encryption

Question

I am thinking about encrypting some fields in MySQL database using MySQL's encryption functions. I can't find any good sources on how to properly implement it. Has anyone done this and have a good tutorial on this?

I would like to implement salt along with encryption key. I have found out that AES_ENCRYPT is better off with a fixed length key from mysql AES_ENCRYPT key length post. Has anyone written a MySQL function for key derivation algorithm?

EDIT:
For example, I have found a tutorial - Encrypt MySQL data using AES techniques, but it does not go into enough depth at all.

EDIT 2:
I am trying to query encrypted fields directly from the database. It is a small project, so it is not likely that someone will write an application front end for this.

• Derek Downey: You say "the tutorial doesn't go into enough depth at all." It seems to answer the question 'how to encrypt with mysql functions', so could you elaborate on what question it does not answer for you?
• dabest1: I am looking for a similar tutorial that goes into more depth with security in mind. I would like it to cover implementation of key derivation algorithm and securing the connection from client to server, as otherwise passwords would be sent across the network in the clear.
• Antti Rytsölä: If possible it's better to do the encryption/decryption away from the database. This will save your database CPU and makes it harder to gain unauthorized access to data.
• James Butler: +1 for anttir, unless its an academic exercise, do the crypto in the application layer. This way as a bonus your data stays encrypted for longer. (unless you need to decrypt your fields to run queries on them - odd situation though)
• gbn: Can you describe what the problem you want to solve is please?

Answer 1

Check out this tutorial: http://techpad.co.uk/content.php?sid=82

Also you can listen to OurSQL podcast about Mysql and how to encrypt data.

Finaly you have an one hour video about you want right here.

And, of course you have read the MySQL Reference already right?

Answer 2

I think, its a bad idea to use a mysql function for this.

Im a ruby on rails developer and in our city, we use a gem called Bcrypt.

password_salt = BCrypt::Engine.generate_salt
password_hash = BCrypt::Engine.hash_secret("your_pasword", password_salt)

Now, my login table will have,

login_name | password_salt | password_hash

When you want to authenticate an user,

You will get the user entered password as user_password and SELECT this users password_salt from login table and the call the same function as below,

current_password_hash = BCrypt::Engine.hash_secret(user_password, user.password_salt)

Check, current_password_hash is equal to the password_hash stored for the current user in the login table, If yes go ahead, If no, shout at him.

This way, not encryption, you do not even store your password anywhere in the database.

Thanks.

• dabest1: Thanks. The use case is actually different then a website authentication. We need to be able to encrypt and later decrypt the passwords stored. What would you recommend in that case?
• beck03076: stackoverflow.com/questions/5508439/…

Answer 3

I can't find any good sources on how to properly implement it. Has anyone done this and have a good tutorial on this?

The answer my be coming in a little bit late but it may very well help someone else. I have worked on a number of DB encryption projects as a consultant. Since you describe your project as "small", you can afford to experiment trying out different algorithms and methods. For a production DB, this may not be a good idea since a lot of things may go wrong.

If you're running a big DB system, you will need a robust encryption solution that won't affect the overall performance(a small wrong choice in implementation may prove to be costly). You will need to make sure your keys are well managed, and have a professional way to manage your configuration and audit logs.

There not so many solutions on the market addressing the issue, and maybe apart from solutions like MyDiamo and a few others, people with open source Database Systems have problems when they need a comprehensive DB security solution or when they have to comply for HIPAA or similar regulations.