MySQL Encryption and Key management

I am developing a local intranet system in PHP/MySQL to manage our client data. It seems that the best practice would be to encrypt the sensitive data on the MySQL server as it is being entered.

I am not clear, though, on what would be the best way to do this while still having the data readily accessible.

It seems like a tough question to answer: where is the key(s) stored? How to best protect the key? If the key is stored on each users' machine, how to protect it if the machine is exploited? If the key is exploited, how to change the key?

If the key is to be stored in the DB, how to protect it there? How would users access it?

There aren't really any built-in MySQL features for handling sophisticated encrypted key setups. You'll need to implement the bulk of the encryption logic in your own PHP and/or browser-side (javascript?) code.

But your stated concerns are slightly peculiar: It seems like your only real concerns are a SQL injection or brute force (password-guessing, I assume) attack from a remote client desktop/laptop workstation. That makes me suspect that you already have some other, un-mentioned security measures planned, and you've analysed the possible avenues of compromise.

• For one, I'm assuming you have firewall rules protecting the MySQL/PHP host against any kind of access from unapproved remote client IPs. If I'm correct, it makes sense that you're only worried about attacks from compromised users' workstations.

• Also, I'm assuming you understand that if an attacker on the remote client host can escalate to root/Admin privs, or directly compromise the real user's own account, then that client's data has zero protection regardless of encryption or any other safeguards. (The attacker can read the keys from wherever they're saved on disk, or snoop them as the real user enters them at logon, and keys lead to data.)

Starting from those two assumptions, it makes sense for us to conclude that the only two relevant threats are A) brute-force password guessing, and B) SQL injection attempts:

• If the attacker doesn't get ahold of the real user's key, or if he wants access to more than just the real user's data, he can try brute-forcing logon creds for the real user or another account. (In theory you could lock down each account to a specific remote client IP, which would help compartmentalize the risks, too.)
• If the attacker does get a valid key for the real user, he has an avenue past the logon screen (which is presumably simple enough to be secure), to the soft underbelly of the potentially buggy app code. A successful SQL injection from the real user's context could give him access to other clients data, too.

Now, let's talk about how server-side encryption applies to these situations:

• Server-side encryption definitely helps against the SQL injection threat. If the row values are encrypted in the DB tables, the attacker can only see gibberish ciphertext of the data that belongs to other accounts. The threat is contained, compartmentalized.
• Brute forcing password guessing, though, doesn't really get any harder for an attacker facing server-side encryption. Regardless of whether the users' keys are stored on the server or generated on-the-spot from the password, the only thing that matters is whether you have the right password. Either the server decides to let you use the valid stored key because it checks that your password is correct, or it calculates the valid key for you because your password is the correct input to generate that key.

Client side encryption, on the other hand, actually makes brute force password attacks irrelevant. You can't brute-force a properly constructed key. Client-side encryption keeps basically the same level of protection against SQL injection as server-side encryption, too. The client can pass the key to the server at logon, keeping a copy in memory until the session finishes, which puts the crypto CPU burden on the server. Or, the client can handle the encryption/decryption by itself, in the browser. There are ups and downs to both techniques:

• Passing its key to the server is far easier to code and manage, and usually much faster on account of more optimized crypto code (compiled C, probably).
• A pure client-side approach gives extra security, because even if an attacker gets root on the server, he still can't read the encrypted data, and he never will be able to read it. The only possible attack vector is to compromise the remote client workstation.

Finally, I'm going to note that there are some huge operational downsides to encrypting data in the database. Because the encrypted data representations are essentially random patterns, basic database features like indexing, joins, etc. aren't going to work. The client takes on a huge logic burden, and may lose a lot of the benefits that database features normally bring.

You might want to look at ezNcrypt, which uses ecryptfs, access controls, and key management to provide high security and performance for Linux encryption of MySQL databases and other processes. No I don't work for them.

You could use Scytale. It is a NoSQL crypto proxy for modern DBMS and web applications. Supports multi-recipient and group encryption. Loaded with a strong RSA/AES cryptosystem. It is also 100% free and open-source.

https://bitbucket.org/maximelabelle/scytale