Letting your database handle the encryption/decryption is probably for the best:
- You don't need to write any encryption/decryption code and risk breaking your own security by accident. This also means, as Guntram Blohm pointed 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.
- 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.
- 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.
- 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:
- 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.
