Should I perform encryption in the front end or within the database?

Question

I want to store an encrypted string (specifically, email addresses) in a database. I'm currently using Python and MySQL. I was initially going to use MySQL's AES_ENCRYPT/DECRYPT to handle it, but then got to wondering if one was was better than another.

It seems that if I tie it to a database function, that could potentially lock me into the database and at this point that selection may change down the road. Otherwise I would look to handling it in the Python code, but part of me thinks that having it all handled on the back end would free you to use whatever front end logic and code you wanted to.

Any thoughts or ideas?

Answer 1

I think it would depend on any future plans you have. Do you see yourself using something other than MySQL? Or perhaps see yourself using some other encrypting algorithm?

If you can answer yes (or maybe) to any of those two, then it would be a good time investment to delegate that task to the "middle ware", which ideally should come between the front end and the DB.

Answer 2

There are a lot of depends on this, but I would tend to put it in the application layer rather than the database.

1. if someone downloads your database, they potentially have the encryption code. That means in this regard your attempt to secure data is useless. Remember you always have to assume a worse case scenario, so if they have the code, then you have to assume they can use it.

2. Encryption isn't what a database is used for. In scaling out systems, you can always easily add another app server. It's difficult to add a database (syncing the two etc.). I try and keep as little business logic code as possible on the database server, because if you start having processes eating up cpu and memory on the database, you have to start migrating off of that server. It's easier to just not have to do this in the first place.

Answer 3

Since AES_ENCRYPT/DECRYPT are just functions that you can apply to your data as part of the query, I don't see how that would cause lock in. If you write your code to use it, then change your mind and decide its better to have it handled by your front-end, you'll just have to:

1. Change your queries/view so they don't use those functions anymore;
2. Change the code calling those queries and have them encrypt/decrypt the data using the same algorithm.

(Those two changes must come into effect simultaneously, of course; the converse operation is also possible)

The main advantage I see in doing it in the front end is that you can choose other values for the key size and padding (MySQL uses 128bits and 16 respectively), if needed. You can also change the algorithm more easily, if eventually you decide AES is not the best choice.

I left aside security implications of your setup, since it's hard to tell from which kinds of risks you're trying to protect your data. However, I agree with @Kevin that you get a little more security (by obscurity) if you don't explicitly show the algorithm in the database, assuming your code is kept secret.

Edit: seems I misunderstood Kevin's argument; while I agree that hiding the algorithm is mostly pointless, hacking your server is not the only way for an attacker to access your database - the processes you use to make regular backups of it, including offline ones, creates more opportunities for data leaks that does not necessarily applies to the code (which might have its own process vulnerabilities as well). But in the end what matters most is how strong and well-protected are your encryption key(s).

Answer 4

If you store plain text, you decouple securty from database and business logic, however if the database is able to be accessed, the email addresses are in plain text. Theres been more than one instance of attackers getting hold of backups and/or old drives. Employee that have access to the DB have access to the plain text data, but not nessicerily encyprted data (Depends if they hace access to the key) ... What are you really after - a claim you are secure, if so from whom, or in depth security where you are protected from as many attackers as possible.

Personally If I wanted security, I would encypt in the database and store the key on a seperate machine (if at all). It's a bit more painful, but a lot more secure. If all I wanted was to claim security, I would store the data in plain text.