Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

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

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.

share|improve this question
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? – Derek Downey Nov 9 '11 at 18:58
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. – dabest1 Nov 9 '11 at 19:16
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. – anttir Nov 11 '11 at 16:31
+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) – James Butler Nov 20 '11 at 10:40
Can you describe what the problem you want to solve is please? – gbn Dec 27 '11 at 12:51

Check out this tutorial:

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?

share|improve this answer

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.


share|improve this answer
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? – dabest1 Apr 23 '12 at 21:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.