What's the best way to store sensitive data in MySQL?

Question

I'm managing the MySQL database from PHP scripts. the communication between server and client is secured via SSL. I store user account data which is sensitive.

Is there a way to encrypt this data when entered into the DB? What is the best way to protect this sensitive data?

EDIT: I’m using a CRON job for updating data which relies on this password to login the user account. So I need a way to hash this password and to be able to get the original password for my CRON job tasks.

What’s the best way to achieve it?

Thanks

Answer 1

Seriously, DON'T USE MySQL's aes_encrypt() It is the the most insecure method of using a block cipher. It is using ECB mode, and I can give a simple example demonstration why this is a serious mistake.

Plain text message:

The same message encrypted with ECB mode (doesn't matter what cipher you use):

The EXACT same message using CBC mode (again, it doesn't matter what cipher you use):

There are even more reasons not to use mysql's aes_encrypt, most notably every single query you send will also have the aes key that you use. If the database is compromised the attacker will enable logging and just get your aes key and decrypt the entire database.

So what should you use? I like this class for the time being. Its using CBC mode with a String2Key function and an IV. You can use the primary key as your IV, each message must have a unique IV. Its okay if the attacker knows the IV, and if they are sequential, so long as the block cipher implementation is secure. Reuse of an IV made WEP much less secure.

Answer 2

There are encryption functions in MySQL,

http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

For example, you can use aes_encrypt() to store data in encrypted form.

However, there are some weakness in these functions,

1. It doesn't support IV or block-chaining. The same text always encrypts into the same ciphertext so it's not suitable for sensitive data like passwords.

2. There is no key information so it's very hard to rotate keys.