Encrypt data at rest

Question

I am search for this topic from a while.

And I found some topics talking about encrypt data at level of database (MySQL). But it works only from version 5.7

Now I want to find a solution with example please how can I encrypt data with application.

I am using PHP with MySQL database. And I want to encrypt for example bank account number in my database

I have table called bank with columns id, name, account_number

How can I encrypt the account numbers? And how is it different than normal encryption methods with PHP?

I hope I explained my issue well. Please let me know if ii can provide more details.

Answer 1

Honestly, I'd recommend using encrypted storage at the filesystem or hardware level, and just let MySQL work normally. If you use Amazon, they support encrypted EBS volumes and S3 buckets.

MySQL has encryption functions you can call in SQL expressions. See https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html

For example:

INSERT INTO bank 
SET id = 1234, 
    name = 'ABC Bank', 
    account_number = AES_ENCRYPT('8675309', 'password');

Then you have to decrypt as you fetch:

SELECT name, AES_DECRYPT(account_number, 'password')
FROM bank WHERE id = 1234;

A big problem with this method is that your password is in plain text in your query logs and binary logs! Seems like a deal-breaker to me. That's why Transparent Data Encryption (TDE) would be preferable.

However, I know of no TDE implementation built specifically for MySQL Community or MySQL Enterprise or other variants of MySQL.

MySQL Enterprise claims to have supplementary encryption features (https://www.mysql.com/products/enterprise/encryption.html), but this is really just some extra functions you can call in SQL expressions. That's not transparent, because you have to change your application code to invoke the encryption functions explicitly every time you insert or fetch data.

MariaDB 10.1 claims to support transparent encryption for storage, The documentation is here: https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/ I haven't used it, but their implementation has so many limitations, I don't think it's really a good solution. For example: binary logs, query logs, and error logs cannot be not encrypted, so sensitive data could be leaked. Also, some backup tools won't work on an encrypted database.

Encrypted storage doesn't help encrypt data in flight, i.e. on the network as applications are fetching data from the database server. For data in flight, all MySQL variants support SSL network connections.

You still have to worry about data when it exists in RAM in your application itself, or in various cache layers like memcached or squid or the MySQL query cache.

Data also exists in backups and logs. Have a plan for securing these. How are they stored? Who has access? Do they ever get copied to an insecure environment? Are they disposed of securely?