CODEWITHSUNDEEP
mysqldatabasesecurityencryption
Jesse Luke Orange
Jesse Luke Orange

Reputation: 1999

Encryption of database data

Now I know this question has been asked many, many times regarding where to do encryption, what to encrypt and what to encrypt with.

Anyway, when it comes to encryption of data within the database I know I can use AES_ENCRYPT() with the fieldname and a given key, plus I can incorporate things like date and time, or even a key stored in a separate table.

The issue I, as well as probably others face, is that there seems to be a million different ways to do it with everyone having an opinion on each one.

Also when it comes to security I know it is best not to try and create your own security measures.

So, my question is: what is a good standard practise to follow?

I have no issue with using AES but is there an industry standard?

Upvotes: 0

Views: 1169

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

I have done some of this and therefore can describe a tradeoff. There are a few implementation specific questions such as whether there are measures in place to make encryption of short values safe (and I don't know the answer to that on MySQL -- I know on PostgreSQL that issue being handled has significant performance costs).

Assuming a secure encryption implementation in the database (and performance vs security here is actually another big tradeoff), you have some fundamental issues with this in the db.

  1. Once you are storing cyphertext in the db, you give up on your ability to search by that information using an index.

  2. With aes_encrypt you pass your key in, in the query string. This means that if the query is logged, the key is logged to. Ensuring that does not happen can make troubleshooting a bit hard when things go wrong.

  3. On the other hand you can build more advanced key management systems with appropriate subqueries, such that you can gracefully handle key roatation.

Handling key rotation with application-level encryption is harder than it is in the db. But key disclosure in logs is a real issue that requires thinking things through. If I could give advice, it would be to focus on that issue first and then solve whichever one of those problems you are more inclined to solve.

Upvotes: 1

Related Questions