Encrypt and decrypt all data in database

Question

I want to be able to encrypt all the data that I save in a MySQL database. I also need to be able to decrypt the data using a private key.

This database can be accessed by more than one person.

I would like to know what PHP functionality I need to use in order to accomplish this.

Thank You

Answer 1

Encrypting your entire database is a lot of trouble unless the database offers "transparent encryption", Oracle databases offer such a thing. A light-weight open source solution using transparent encryption is SQLite. With this feature, the encryption does not apply to the data directly but the storage itself is encrypted. This means that you can work on your database as usual with the added benefit that the physical storage is encrypted. But unfortunately, MySQL does not offer such a thing.

First, why it's not a good idea to encrypt your entire database. Encrypted data looks like random garbage to your database. This implies a lot of negative things such as

• SELECTs no longer work on your data the way they did before, if at all (requires a lot of effort)
• Indexing becomes pretty much useless
• Database logs are rendered useless
• ...

If you encrypt using MySQL's built-in encryption support then this means that the data itself is sent in plain text to the database - unless you use a TLS-secured connection, this means that eavesdroppers won't care about your encryption at all.

So if you need to encrypt data you should keep it at a minimum and only encrypt the truly sensitive parts of your data to still be able to execute efficient queries on it. You should also encrypt the data directly in the application if there is no possibility to have a secure TLS connection between application and database.