Php two-way encryption to mysql database

Question

I would like to learn / know how other companies deal with the problem of storing the encryption keys. I want to encrypt the user's email address (and other sensitive information) but (because I want to be able to send the user an email) I need two way encryption to decrypt it.

I did some research but I don't fully understand it yet. that's why I Am for your expertise.

The question, what should you use as the encryption key (like the hashed password) or where/how should you store a generated key.

It would be pointless to store the key in the same database because if it gets compromised the key would also be leaked, and the data would be decryptable. But the data needs to be accessible by the program...

So I was thinking (and please do share me your opinion / expertise) about a sqLite database that links the (bcrypt hashed password + mt_rand 64 bit character string of variable length) encryption key to an user_id. The file will only be accessible from the code (or from the outside if nginx / linux is compromised). And if an user request data that is encrypted, the system would use the user's id to search the encryption key inside of the sqlite database and use that key to decrypt the data from the mysql database.

A better way would be that even I wouldn't be able to get to the data (like hashing) but that the system would be able to decrypt it, but I don't think that's possible.

footnote, altho it would solve this problem by leaving the answer, please do also share resources, it would not only help me by solving my problem. But also in learning to be better at security.

Answer 1

What you do here depends on your threat model and what attack scenario you're trying to defend against. I'm going to assume you're trying to protect against a situation where an attacker can read the database contents (e.g. via a SQL injection vulnerability) but not the contents of the filesystem. I'm also going to assume you care about confidentiality and not integrity or authenticity.

It's worth noting that you will need to lock down your database user's permissions to prevent a SQL injection from gaining filesystem access via commands such as LOAD DATA INFILE, otherwise database access inherently also means filesystem access in most cases.

The general way to secure database contents against such a scenario is to have a symmetric key stored externally to the database, e.g. in a config variable or stored in its own file. Then, for each item of data that you want to encrypt, you generate an IV and encrypt the data with AES in CBC mode, then store the IV and encrypted data as columns in the database. You can then decrypt the value by finding the row you want to decrypt, reading out the IV and encrypted email address, then doing an AES-CBC decrypt using the key you loaded from the key file.

This does, however, mean that you can't search the users table by email address. Scott Arciszewski wrote a great blog post recently about building searchable encrypted databases which may help you if this is a requirement.