Encryption at rest and/or AES_ENCRYPT

by

Encryption at rest

Encryption at rest is the data in the database when it is not being used/accessed or updated. Encryption on the move is things like TLS where the data (from the database) is transported from server to server to browser, to server, to browser, etc. TLS is perfectly good in most situations if it’s handled carefully and approached with an attitude that you need to do more than the bare minimum to actually make it realisitically secure.

A typical example is people put on a TLS certificate from LetsEncrypt on their domain and think that suddenly all their stuff is safe; but they don’t encrypt their sessions or their cookies so leaving a massive potential hole in their defences.

Do not use MySQL’s built in encryption system.

I can not stress this enough; the built in encryption system in MySQL is not suitable for actual secure data protection.

Please read my answer to a very similar question here as to the details (I don’t want to simply copy/paste).

Ok, then, because you insist…. here:

I have always understood NOT TO USE MySQL’s built in encryption fuctionality because the point of encryption of data at rest (in the SQL) is that if the server is compromised, the data is not at [as much] risk.

The problem with the MySQL built in functionality is that it doesn’t apply to when the data is passed to and from the “at rest” state, so the plaintext of any data can be recorded in MySQL logs (and elsewhere on the storage system, such as query lookups are not encrypted so you can from numerous lookups and their count results deduce column values) before/as it is encrypted. You can read more about this here.

Regarding encryption, you should use some tried and tested library like defuse/php-encryption.

From what I’ve read in my own research on this topic, the link provided by Magnus to defuse/php-encryption is one of the best ways of preventing MySQL ever causing you to compromise your data, by never letting the MySQL program/server ever see the plaintext value of your data.

— Answer as posted May 7th 2017.

Also Bill Karwin’s answer to the same question gives some valuable additional insights:

+1 to Martin’s answer, but I’ll add some info for what it’s worth.

MySQL 5.7 has implemented encryption at rest for InnoDB tablespaces (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html).

MySQL 8.0 will reportedly also implement encryption at rest for InnoDB redo log and undo log files (https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html).

This still leaves unencrypted the query logs and the binary log. We’ll have to wait for some future version of MySQL for that.

Why does it take so long? The head of the security engineering for MySQL said at a bird-of-feather session at the Percona Live conference last month [April 2017] that they are being very careful to implement encryption right. This means implementing features for encryption, but also key security and key rotation, and other usage. It’s very complex to get this right, and they don’t want to implement something that will become deprecated and make everyone’s encrypted databases invalid.

— Answer as posted May 7th 2017.

Closing Point:

Security is complex. If you want to do it properly and have a confidence in your protective onion skins then you need to do a lot of things (see bullets below); but the first thing you need to do is:

  • Define Who you are protecting against

Seriously. You need different strategies against someone who wants to steal your plaintext names and addresses versus someone who wants to take over your server versus someone who simply wants to trash the data just because. It is a myth that you can protect against everyone all of the time, by concept this is impossible*; so you need to define the most likely agressors and then work out how best to mitigate their advances.

Sepcifically to MySQL, some clear recommendations:

  • Keep the SQL and the PHP on the same server. Do not remote access to the MySQL data.

  • Exclude external access to the SQL (so it’s localhost only)

  • Obfuscate your table names and column names; if someone break into your data and you have HDTBJ^BTUETHNUYT under the column username then they know that this garble is probably a username so they have a very good start in trying to break your encryption.

  • IMPORTANT: Really lock down your table access; set up lots of MySQL users, each with only the bare minimum privilieges to do what they need; you want a user to read the table (only) and only read certain tables; users to write to certain tables but have no access to other tables. It’s seperation of concern so that if any one user on the MySQL is compromised; you’ve not automatically lost every piece of data in there.

  • Use PHP encrpytion services . Store Encryption keys in a completely separate place; for example have another server you use solely for backup that you can access solely for reaching out to grab the encryption keys, therefore if your PHP/MySQL server is compromised you have some room to cut off and lock down the Key server so thay you can limit the damage. If the key server also has backups then really you’re not too badly compromised (situation dependant).

  • Set up lots of watchers and email informers to tell you exactly when certain processes are running and which server users (not people but programs) are doing what. So you can see why an unexpected process starts to run at 5am to try and measure the size of the MySQL tables. WTF?

  • There is a lot of potential to have your MySQL AES_ENCRYPT’ed data “sniffed” even if it is not at rest in the DB, but if the website gets compromised (or worse, the PHP code is insecure) then timing attacks can work out data contents by timing query lookups and data packet returns.

  • Security is a black hole; at some point or another you’re going to think “Sod this, I’ve done enough”. No one ever has total security, some very dedicated organisations have enough security. You need to work out how far you’re willing to walk before you’ve gone the distance.

* Why impossible? Because to protect your data from all threats, all of the time, it would need to be unreadable, unusable, like a hash. A hash is protected from everyone, all of the time. But a hash can never be un-hashed.

More Related Contents:

  1. Unable to AES_DECRYPT after AES_ENCRYPT in mysql
  2. How to use `bcrypt` algorithm within `encrypt` function in MySQL for verifying password?
  3. How to use IN & NOT IN same query [closed]
  4. MySQL: Large VARCHAR vs. TEXT?
  5. MySQL: is a SELECT statement case sensitive?
  6. Check overlap of date ranges in MySQL
  7. 1114 (HY000): The table is full
  8. UPDATE multiple tables in MySQL using LEFT JOIN
  9. Multiple-column foreign key in MySQL?
  10. Finding all parents in mysql table with single query (Recursive Query)
  11. Get the latest date from grouped MySQL data
  12. How to execute a MySQL command from a shell script?
  13. Select most common value from a field in MySQL
  14. MySQL update a joined table
  15. Returning the ‘last’ row of each ‘group by’ in MySQL
  16. Optimal MySQL settings for queries that deliver large amounts of data?
  17. mysql configuration stops at “starting server”
  18. Is it possible to reference one column as multiple foreign keys?
  19. No Entity Framework provider found for ‘MySql.Data.MySqlClient’ ADO.NET provider
  20. How to Import Multiple csv files into a MySQL Database
  21. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  22. Row to column transformation in MySQL
  23. Data truncated for column?
  24. Using MySQL triggers to log all table changes to a secondary table
  25. mysql select id and name from other table and join query
  26. WooCommerce: Finding the products in database
  27. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  28. Mysql performance on 6 million row table
  29. MySQL Select Multiple VALUES
  30. Convert SQL Server query to MySQL [duplicate]

Leave a Comment