Store sensitive data in MySQL DB

Question

How should sensitive data be stored in a MySQL database?

1) Should I focus more on MySQL database security and store data in plain text?

• I found a step-by-step guide on how to make the MySQL database more secure:
• http://www.symantec.com/connect/articles/securing-mysql-step-step

2) Do I have to encrypt data?

• If so, how to do encryption?
  • Using MySQL aes_encrypt / aes_decrypt?
  • Using PHP AES functions / algorithms to encrypt / decrypt data?
• How to store data in MySQL?
  • Blob
  • Binary
  • VARBINARY

In my case, “sensitive” data is payments made by individuals.

thanks

Answer 1

This is a mixture of both. The two existing answers (at the time of writing this fooobar.com/questions/1413915 / ... and fooobar.com/questions/1413915 / ... ) are valid - you need to look at about 5 possible attack methods that I can think of

• They gain access to your database server; so yes, ensure that the child is just as reasonable (Matt replied).
• Offline data capture (someone gets to your database data in some other way, maybe backup, maybe they guess the password, maybe MITM if you transfer data from one place to another). To do this, you encypt your data. For some reason, you can also do a CSV dump and send someone an email. Oops But this is happening. So encrypt (vlzvt answer)

But three elements are not mentioned:

• They can access your web server (if it is different from your database server). If they have access to the web server, all bets are disabled, since they have their own password, the key is the key to success. Therefore, you need to make it even more secure than the database server. (Matt may have meant higher, but just make it clear)
• As above, but do not forget if anyone is accessing phpMyAdmin or your management consul. Do not use access to open passwords with clear text or config for access.
• Finally, your application in itself (and the most difficult to block). You need to prevent the use of SQL injections that can reveal data. Data encryption would stop minimizing problems if someone got access through an incomplete request - therefore, encryption is the solution for this.

---

For part 2 of your question:

Using the encryption and decryption functions of MySQL will stop someone who has access to raw data, but not MITM or SQL injections or even CSV dumps taken for transportation.

So, IMO (and this is just my opinion and how I did it) is to encrypt using PHP and hammer encrypted data over the wire, as this stops all data capture methods, and CSV dump will be "scrambled".

If you do this, you can also use the varbinary / blob types, as this prevents you from accidentally reading / editing in phpMyAdmin. Plus, potentially saving a few bytes nominally (although it depends on indexes and other things - so one is not a winning argument).

---

And now the downside: search and sort. Everything that you index or search, if encrypted, will only match the entire, case-sensitive string, filled to the desired length (usually the search will be case-insensitive, and you can search for details using LIKE). And if you want ORDER BY, you need the source strings. So you should not keep in mind when designing a structure.

Hope this helps.

Answer 2

What is the worst case scenario if an attacker gains access to text data? Given that you need to decrypt the data in order to make it useful, and therefore you need the encryption key to be somewhere accessible, any attacker who can get into the database will probably be able to get to the key if this is not archiving, but not a live website. I would focus on the security of the database server if you do not load hard drives with full information that may be lost, but in fact it depends on why you need to encrypt it.

Answer 3

if you need to protect data in your hacked database, you can encrypt it with mcrypt

$key = "mykey"; $crypttext = mcrypt_encrypt(MCRYPT_RIJNDAEL_256,$key,$data,MCRYPT_MODE_ECB,$key); 

after that you can choose BLOB, TEXT, MEDIUMTEXT or whatever, based on the expected data size. * For VARBINARY / BINARY, you may need to pack first.

Answer 4

The encryption operation has additional costs.

You need to evaluate whether this additional cost will be a problem in your case, for example, if your data will be significant.

The first line to avoid data leakage is a strong data access policy with access profiles and so on. This has the disadvantage that you will need to manage mysql and configure it.

If you want to take care of managing your profiles, you can encrypt the data, assuming additional CPU overhead and (depending on the encyption algorithm) additional storage space.

System security is equal to the security of weaker components, do not focus only on the encryption task, it only gives you a sense of security if the data can be decrypted, the only thing that takes time and brute force for the intruder to break the encryption.