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

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.

Answer 2

There is too litte info for a really useful answer but here are the common cases:

If you store passwords where you should check if the user input is correct, but you dont need the original data itself, you can store a hash (SHA1/SHA2). Lookup all practices for hashing to do this right.

If you need to read back the original data, you can use encryption functions. A good choice for a symmetric encryption is AES. However, the problem here becomes key management. Where do you store your key that is used for encryption/decryption? This is a very common problem and depending on the context tehre are different solutions.

Answer 3

There are some basic ways to do it (unless you provide some more thorough information on exactly what you want to accomplish).

• Disable remote access.
• Monitor the MySQL access log.
• Require the use of strong and secure passwords from client end.
• Sanitize ALL your user input, before storing into database.
• Configure your php for security. (register_globals, safe_mode etc etc)
• Use mysql inbuilt encryption functions, as pointed out. http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

If you are protecting against wanna-be hackers this should be OK. Again I need you to specify in a bit more detail.

You also have to consider the level of security you need against the speed of the algorithm.

Answer 4

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.