PHP MySQL - on the fly encrypt decrypt data without storing the key

I have been through several SO questions regarding this and my approach is a bit different in terms of wanting to encrypt data. Here is what I want to do..

Basically all my customers data is stored in the database and after 3 weeks or 4 weeks, I don't really need their data anymo开发者_Go百科re such as address, city, state, zip, phone, email address, products they ordered etc.

Now this data is stored in raw format in the database (mysql).

What I am thinking is that after certain days, I will encrypt all the data which resides in mysql database and instead of storing the key, I will just enter it manually via input box each time (when wanting to encrypt it or if for some reason decrypt it just to display on the screen).

So basically here is how it would work..

1) Select the customer's record in mysql

2) Get the encryption key via input box

3) Update the mysql record by encrypting the data

So here are my 2 questions...

1) Is the above mentioned a good strategy in the sense that if the database was compromised, the data would be secured. Additionally, if the intruder were to gain access to the code, they wouldn't have access to a key because it would not be stored anywhere in any php files.

2) How should I setup the encryption system? Should I used the mysql's function AES_ENCRYPT (Please keep in mind that the length of the data may vary such as the address, or email address or some other information about the customer)

This scheme sounds like it would work just fine. There's only some details that you need to take care of.

AES_ENCRYPT (which is fine -- this also holds with any other encryption method you might use) returns a binary string. You need to store that inside a column that can hold binary strings (BINARY, VARBINARY, BLOB variants), whereas you probably store the pre-encryption information in nonbinary string columns. So you would either need to have another set of columns for the encrypted info, or use some text-based encoding to be able to put the encrypted data in the same columns you use for the unencrypted form.

Also, you should be very careful in your application logic so as not to accidentally repopulate an encrypted row with unencrypted data (think scenarios such as view record -> decrypt -> save changes).

Finally, if the bad guys have write access to your server/application then they might very well intercept the encryption key and save it. Since the key would likely be one and the same for all records (due to key management considerations) this would be enough for them to get all your data. But if they only manage read access, you are good.

Public-key cryptography might be a better choice for you than AES. It uses two keys instead of one. Data encrypted using the public key can only be decrypted by the private key. This means that you don't need to worry too much about the public key falling into the wrong hands, as no data can actually be decrypted using it.

PHP's best option for public-key cryptography is the standard OpenSSL extension, which uses the industry-standard RSA system.

Keys can be stored in a PEM-encoded (fancy base64) file that requires a passphrase to unlock. It's this passphrase that you would want to prompt users for, not the actual key. That's fortunate, as secure RSA keys are rather large and can't exactly be memorized. This also lets you securely store the private key on the server.

Also, it should be obvious, but you probably want to ensure that you serve your application over SSL, even if this is a completely internal app. Securing the data without securing the transport of the data is silly.

You should also think about how to change passwords in the event of an attack. @Charles idea lends itself to this, but using RSA for encrypting the actual data is likely to be slow (public key algorithms are much slower than symmetric ciphers).

I'd recommend that you do something in between what Charles suggested and your original idea. Store a single encryption key, but make sure that key is encrypted with a passphrase. Encrypt your data with the single key. When a user wants to decrypt data they can enter the passphrase which gives them access to the key.

You can now change the password whenever you want without needing to change the encryption key and without re-encrypting all the data.

AES is a good choice for the actual encryption work. It's fast and has been well-studied.

I'm not sure if this would work for you, but at RSA last month there was a company there with Transparent Data Encryption for mySQL on Linux. They also have a way to manage and store the keys so that they are apart from the data. Good for PCI, HIPAA-HITECH, etc...

They encrypt to disc as mySQL is writing and decrypt as the data is requested. No changes to the app or data structure at all. Company is Gazzang. Just search for them, or check out RSA Innovation Sandbox 2011.

Hope this helps