Data Encryption by Application vs Data Encryption in Database

So which is the best way without compromising too much on performance?

Should your implementation ever be audited, "I'm using the industry standard in database XXX" will probably give you a lot less headache than "I hacked something together myself".

@GuntramBlohm I'm stealing that point for my answer, because that's a very good one. Proving your custom software secure is extremely hard, because writing secure software is hard; if you can point to someone else and say "they wrote it, these are their credentials, these are the people who've reviewed it already and found it secure", that's much simpler to deal with.

If your goal is compliance and certification, encryption at the database level may be the better option. If your goal is true security for your users, however, encryption at the application level may be the better answer, because the keys may never reach the database (where the encrypted data is stored). If the database is on a separate machine, this setup can certainly be more secure. But usually, you want both, compliance and true security.

3 Answers 3

Letting your database handle the encryption/decryption is probably for the best:

1. You don't need to write any encryption/decryption code and risk breaking your own security by accident. This also means, as Guntram Blohmpointed out, you won't have to prove your own security to be secure, if it comes down to it. And proving your custom software secure is as hard as you probably think it is, if not moreso.
2. Using the stuff built-in to your database is typically easier, e.g. Postgres lets you pick which columns and tables to encrypt, and their design only briefly has the keys and decrypted data on the server. Other databases are probably similar, but that's the only one I'm familiar with.
3. If you ever need to, say, generate a report based on the data, it'll be simpler to do that with an existing tool that supports encrypted databases than writing all the custom code yourself on the client-side.
4. If you ever need to do any filtering on the data that isn't on exact, case-sensitive values, the database will handle doing it efficiently for you, e.g. maintaining indices but keeping them encrypted until they're needed, and even then only decrypting the specific values they need at any given time.

There is one point in favor of client-side encryption, which you've already mentioned. It's a big one, but I don't think it outweighs the benefits above:

1. Your security will be slightly increased if you handle all encryption/decryption on the client side, because at no point does the database (and therefore any evil sysadmins/attackers in control of that server) know either the decryption key or the decrypted data. However, depending on the data and its usage, there are some cons to consider:
   • It's entirely possible you'll mess up your client-side encryption code and leak the data/key that way, either directly or through side channels.
   • You need to either trust an outsider or handle key-management yourself, which isn't trivial (and is very not trivial to support in code, if you want to avoid scheduled downtime every so often)
   • If you do mess up your code, you alone (well, your company alone -- you know what I mean) are responsible for fixing it and cleaning up the damage, and any fixes are just as likely to have bugs as your original code. Possibly more, given that you'll be stressed.
   • You'll either have to spend a lot of time performance tuning, or accept that your performance will be bad, or reduce security for easy performance gains (e.g. by short-circuiting a comparison or precalculating some constants or letting your compiler optimize the wrong bits). None of those are good things to do. The database server has already had a lot of people doing a lot of tuning; you'd be redoing all that effort from scratch.
   • For every single new dev, you'll need to either train them in good secure coding practices, or tell them not to touch the security-related bits of your code, neither of which is cost-effective or even generally that reliable.
   • If you need to implement fuzzy filtering client-side, you're basically going to have to do SELECT * FROM table; and manually decrypt and filter every single row, which is just. not going to go well. You'll be keeping the key in memory in a directly-web-accessible server for a long time, as a direct response to a request which can be sent by an attacker.

As Kevin points out, you also have the option of encrypting specific fields. For example, if you're storing a customer's username and credit card number, the latter definitely needs to be encrypted, but the former probably doesn't. You could include e.g. a CHECK constraint to ensure that you don't accidentally add an unencrypted credit card. Credit card numbers are a particularly good example of this, because you never need to do any sort of matching against them -- you only ever retrieve them from or store them to the database, so you can handle them efficiently from the client side.

However, note that good security is not one thing. Good security is doing everything right:

• Analyzing your threat model to ensure you're guarding against the right threats
• Using HTTPS for your whole website with valid, current certificates
• Encrypting your database, one way or another
• Encrypting the communication between your website and your database
• Using SQL correctly to avoid SQL injection
• Hardening your database server to protect it from attacks
• Applying the principle of least privilege
• And so much more.

However you handle your database, it's not the end of the road. Make sure you take care of everything.