I'm planning on building a web application that will store what I believe to be sensitive data. We're not talking credit cards, or passport numbers but I still believe the user's data to be of great importance so I will make every effort to keep it as secure as possible.
I've had a look at this page to see what MySQL offers:
AES_ENCRYPT/AES_DESCRYPT look like good choices for me, but the question then arises how to go about securely storing the encryption key. If someone breaches my server and I store the encryption key in my application then that's plaintext too so the person can decrypt without a problem.
Any ideas from people that have done this kind of thing before?
Encrypting the data doesn't protect against server breaches - you need other forms of protection to prevent server breaches.
Encrypting data doesn't prevent server breaches, but if the data is really sensitive, it would be nice that it's worthless to the hackers (that is of course if the encryption key cannot be found by the hackers).
I'm well aware of this. I'm asking what can I do to make sure that if my server is breached, they can't do anything with the data. I've spoken to a few people who have suggested completely isolating the database on it's own server that way the encryption key is safe if the data is breached, and the data is safe if the encryption key is breached.
However, what can I do if it's assumed that the encryption key and database always reside on the same server.
What you shouldn't do is keep the encrypted data and the key on the same server, like you said, that's the same as those people putting post-it notes with username/passwords on their monitor.
The only thing I can come up with is just think up a password and let the user(s) type that password when they want to view the data. To prevent the user from having to type this password over and over again you could store it in a session. But make sure the session is cleaned up when the user logs out / is inactive for some time.
This of course opens the possibility of session hijacking, might want to look into that. There are some counter-measures, but I haven't been able to come up with a water-tight solution thusfar.
Note that when you want to use a new password you have to dectypt all the data, and then encrypt it with the new password.
Also note that users providing an incorrect password get garbage instead of the original data. AFAIK there is no way to check if the passord the user entered is correct.
PS. If you this, you should probably also use SSL (https), because otherwise it can still be sniffed (man in the middle attack)
Thanks for the reply Scallio. These are all things I've considered but storing that decryption key per user in a session is probably a bad idea as if a hacker breaches your server he has all the decryption keys in a central place just as if you had a single encryption key. I think having an encryption key per user is probably a good idea though.
Another problem I foresee is that you'll be severely limited on what you can do with the data if it's encrypted. For example if there was a table which stored a users date of birth (a trivial and unrealistic example I know) and you wanted to encrypt the value, it would store the encrypted value in the field. But what if you wanted to pull out all your users with who were between the ages of 21-30. This wouldn't be possible.
Just some further problems I've been thinking about.
Encrypting the data in the database itself just makes it more difficult to use (as you have mentioned) without making it any more secure (unless the data belongs to individual users and you get them to each store their own encryption key so that they are the only one who can access their data).
You will probably find that it is a lot more effective to implement ways to secure who can access the data in the database remotely rather than worrying about encrypting the data in the database itself. By the time someone gets full access to your database so as to be able to see all of the data they have already compromised most of your system.
Beware that if you do that, user A can't see user B's data. If that's your goal than of course that's fine, but if the users have to collaborate on documents, etc, giving each user his/her own key is not workable IMHO.
Stephen, so how do big organisations do it? Presumably paypal don't store my credit card in plaintext anywhere and that wherever it's stored it's probably isolated from the encryption key used (which I would guess wouldn't be my password as in the case of irregularities in a users payments they would need to investigate using the credit card number).
My guess would be that they probably use a lot of layered isolation, maybe even indirection and obfuscation to some extent, and coupled with intrusion detection systems. I don't think this would be nearly as effective if everything was on a single machine.
It is much more effective spending the time on adding security that reduces the chances that your server can get breached.
Encrypting the data in the database effectively means that you can't use the database properly because you have no way of efficiently extracting the desired data from it.
Or not. It's easier to exploit some SQL injection or App vulnerability to get data than to get full access to a server.
It is blocking those things that is what it is worth time doing.
Anyway if you use PDO or mysqli then there is no such thing any more as sql injection because you use prepare statements to totally separate the query from the data it uses and so no one can inject code into the query. Building security into the app to remove vulnerabilities is also worth doing. If you have plugged all of the known types of security hole then for someone to break into your server they need to find some other hole that you haven't blocked because no one knows such holes exist to need blocking.
Without that security anything you do actually on the server is pointless because your site is going to be taken offline by whoever breaks in within a few days anyway and so you will not have time to build up a significant volume of confidential data.
Encrypting data you don't need to use in your queries is cheap and easy to do. If you host multiple sites or got yours on a shared server, if you use third party software for part of your website (phpBB, Joomla etc.) you could miss some attack vectors. And we don't know what attacks will be available in 5 years.
Telling your customer that yeah, you got hacked but the things they had in your database will require some work before it's usefull will help not lose all of them.
If you think hackers only break servers, you are wrong. The good ones do it for money so they want you to have a working server to pillage until you discover you've been hacked. Which can take month. http://www.darkreading.com/database_security/security/attacks/showArticle.jhtml?articleID=222600139
And about the "all known holes" blocked... when I read that some "security consultant" can not grasp the danger of CSRF, I doubt half the websites developpers knows about the OSWAP top 10 holes and even less put the effort needed to fix some.
But if someone breaks into your server they get both the encrypted data and the code that is used to decrypt it so that they can easily convert it back to readable text.
Worst case is that they need to break into two servers if the code and key are on different servers but chances are that whatever they use to break into one of the servers will also get them into the other.
But you don't need to have direct access to the server to get a database datas most of the time. And, I'm sure at least 10% of server hosting apps have know attack vectors which can give you some database dump. And that's not taking into account future attack vectors.
You don't know who will be in charge of the servers you've been hired to setup in the future.