14

I am trying to improve the security a a MySQL database that will contain sensitive data. I am struggling to get to grips with some terminology. Can somebody let me know if I have understood the situation correctly:

Encryption at rest - it seems like I can enable this on a table level. All data in the table in encrypted using a key. If somebody got hold of a backup file or gained physical access to the server then the data would be protected. This assumes, of course, that the key is stored elsewhere.

AES_ENCRYPT - when inserting/updating data into my table I can use AES_ENCRYPT('data', 'password'). When querying the data via a SELECT I use AES_DECRYPT

  1. Assuming I was just using encryption at rest then do I need to do anything different in my PHP code to query the data? Does my PHP code need to send the key to the database via my PDO request? Or can I use my normal code for querying the database and the decryption is handled automatically?

  2. Or have I misunderstood what encryption at rest does and I need to use AES_ENCRYPT instead/as well

Chris
  • 4,672
  • 13
  • 52
  • 93
  • Encryption at rest just means when the data is being stored somewhere not being used. So, whether you encrypt the entire table or use AES_ENCRYPT to encrypt only certain data that you will store in the database, that is all encryption at rest. I am not an encryption expert, but you can do the encryption using the PHP or using MySQL. Depending where you did the encryption would dictate how you would need to decrypt. – JadedCore Sep 01 '17 at 13:45
  • 1
    Well using AES_ENCRYPT in a MySQL query limits you in usable data types you can only use VARBINARY, TINYBLOB, MEDIUMBLOB BLOB and LONGBLOB...you can't design good tables and indexes this way... – Raymond Nijland Sep 01 '17 at 13:48
  • @RaymondNijland yes, I had considered this. Would it be normal to use "normal" tables but only apply AES_ENCRYPT to specific columns that wouldn't normally be indexed or used in joins? For example, a users table would have an id, date_created, date_modified, address, email, telephone and then only address, email and telephone have AES_ENCRYPT applied. The other columns are not encrypted? – Chris Sep 01 '17 at 14:27
  • 1
    You can encrypt the data in one field and have a hash value of the same data in another field. You can use the hash field to do any indexing or searching if you need to. Example: $phone = '555.555.5555'; Then $hashPhone = youHashingAlg($phone); Then store $hashPhone in database in another field. Then when you search for 555.555.5555 hash it with the same alg. and search for the hash value and return the entire record with the data decrypted as necessary. – JadedCore Sep 01 '17 at 17:19
  • @JadedCore this is entirely unsafe. Making your own hashing algorithm will simply not be secure. It will be easily compromised and in doing so this will then compromise all the data security. Don't try and hash the data; *encrypt* the data. "Searching" encrypted data is a complex minefield in itself and needs to be treated extremely carefully. – Martin Sep 01 '17 at 20:16
  • @RaymondNijland you can design good tables but yes, indexes will be an issue. indexing encrypted data is generally a no-no; there's simply no point. index associated numerical fields instead. – Martin Sep 01 '17 at 20:17
  • @Martin I didn't say invent my own hashing algorithm. I said use a hashing algorithm to hash the data. – JadedCore Sep 02 '17 at 22:34

1 Answers1

25

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.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
  • 1
    This is great and very helpful. Thank you. I'll re-read and digest everything but is this summary of your argument fair? Set up a regular MySQL database, use defuse/php-encryption to encrypt/decrypt specific just the columns that contain sensitive data, follow the other points from the end of your post and then pay particular attention to creating multiple MySQL users with very specific permissions. Stay away from any encryption offered my MySQL and don't use AES_ENCRYPT. Thank you again – Chris Sep 01 '17 at 21:13
  • @Chris yes, but to be honest the most important thing is that security can go on and on, it's a neverending story -- you need to identify what sort of compromse you're working against and then customise your security efforts against that type of attack. – Martin Sep 01 '17 at 21:21
  • Yep, for sure. I am just keen to get a good base and then build on that. – Chris Sep 01 '17 at 21:21
  • As much as possible you want a separation of concern -- no single user/unit/codebase has any more access or control than it absolutely needs. Always give only minimum access to everything. – Martin Sep 01 '17 at 21:22
  • Got it. And this is achieved by data being encrypted in the database, the ability to decrypt it is limited by who has (needs) access to the appropriate key. In addition the database permissions are locked down to the bare minimum for each user – Chris Sep 01 '17 at 21:24
  • 1
    @Chris the data should never be decrypted in the database the database should only ever take in encrypted data, and only ever output encrypted data (and numbers if we're being exact). The interface system (PHP) should do the encryption and decryption in a secure fashion and with keys that are stored outside the "box",as in on another server or another safe place, so that if one location is compromised, the data on that location is not compromised. Good luck. – Martin Sep 01 '17 at 21:36
  • Yep, got it. Thank you. – Chris Sep 01 '17 at 21:40
  • This is one of the best answers I've found on the topic, thank you :) – vrwd Feb 12 '20 at 15:06