MySQL Encryption/Storing Sensitive Data,

Mysql encryption / storing sensitive data,

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.

How can I store sensitive data securely in a MySQL database?

This is an overly simplified answer and should be taken with a grain of salt, as most answers about security:

Use SSL everywhere.
Use a secure encryption key

For storage of encrypted data, you could use a BLOB field, and use MySQL's built in encryption functions. Example:

update mytable set myfield = AES_ENCRYPT('some value', SHA2('your secure secret key', 512));

If you prefer to do the encryption/decryption in the application code, take a look at PHP's Mcrypt functions.

Encrypt the user input
Store in the database
Decrypt it after fetching it

This is by no means a complete guide, but it's a start and better than doing nothing.

You may be able to learn more on https://security.stackexchange.com/

What are the best practices for storing encrypted data in a database?

TL;DR: Encrypting columns of databases won't help much.

Best practice: figure out your threat model before you spend time and money securing your system. If you build complex security measures without a clear idea of your threat model, you'll trick yourself into a false sense of safety.

And, encrypting columns in a DBMS is a complex security measure.

What is your threat model? What attacks will you get? By whom? How will attacks damage you?

Your most likely outsider attack comes from cybercriminals breaking into your web servers to steal your users' information to commit identity theft (Equifax), blackmail (Ashley Madison), or espionage (US Government human resources database).

If you encrypt some columns in your DBMS and your web users need to be able to view and update those columns, your web servers will have to know the encryption and decryption keys. If a criminal pwns your web server, he will have your keys. Therefore he will have access to the encrypted columns of your dbms. And, he'll have a big signpost saying LOOK! Here's the secret stuff!

There are plenty of other imaginable outsider attacks, of course. Somebody could break through your firewall and hit your database directly. Somebody could get into a cache and grab cached sensitive data. Somebody could guess your web app's administrator password. Or, steal a bulk upload file.

Your proposed design imagines an insider attack. People who already have DBMS access credentials must be prevented from seeing certain columns in certain tables. What will they do with that information? You didn't say. What's the threat?

Stopping short of encryption, you can do these things to keep your insiders from violating your users' confidentiality.

Get the sensitive data out of your system entirely. For example, if you're handling credit cards, work with stripe.com or braintree.com. They'll hold your secrets for you, and they have excellent cybersecurity teams.
Sort out whether you can trust your insiders. Investigate prospective employees, etc.
Establish clear security policies. For example, "We never look at the credit_card table unless we have a specific need to do so." If you're handling health care data in the US, you already have HIPAA guidelines. Get your insiders to understand and agree to your guidelines.
Sack insiders who violate these policies intentionally.
Build mechanisms to help enforce policies. Issue each insider his or her own username/password pair to access the DBMS. Use selective GRANT operations at the table and column level to allow and disallow viewing of data. For example,
```
 GRANT SELECT (name, address) ON person TO username@'%';
```

lets username see the name and address columns, but not the taxpayer_id column in the person table. Read this. https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-column-privileges

Spend your time and money on good firewalls protecting your DBMS machines. Study up on OWASP and follow those practices. Spend time and money running penetration tests of your web app and fixing the problems. Spend them on vetting and training your insiders. These things slow down attackers more effectively than the imagined magic bullet of encrypted columns.

There's the old joke about the two guys and the bear.

Bear: Roar.

Adam: Uh oh, I don't know if we can run faster than this bear.

Bill: I just have to run faster than you.

That's a good way to handle security for your small web site. Make it hard enough to crack that the bad guys will attack somebody else.

If you're running a big web site with a large number of sensitive records (I'm looking at you, Equifax) this isn't good enough.

Store 'sensitive' data in MySQL DB

It's a mixture of both. Two existing answers (at the time I wrote this https://stackoverflow.com/a/10718397/1015483 and https://stackoverflow.com/a/10718459/1015483) are valid - you need to look at about 5 methods of possible attack that I can think of

They get access to your DB server; so yes, secure that baby as much as is reasonable (Matt's answer)
Stand alone data hijacking (someone gets to your database data somehow else, could be a backup, could be they guess a password, could be MITM if you transfer data from one place to another). For this, you do encypt your data. You also may do a CSV dump for some reason and e-mail to someone. Whoops. But it happens. So encrypt (vlzvt's answer)

But three elements not mentioned:

They could gain access to your web server (if different from your DB server). If they have access to the webserver, all bets are off as they have your password, encyption keys the lot. So you need to make that even more secure than the DB server. (Matt might have meant that above - but just make it clear)
Similar to above, but not to be forgotten, is if someone gets access to phpMyAdmin or your management consule. Don't use plain text auth or config stored passwords for access.
Finally there's your application itself (and the hardest to lock down). You need to prevent against SQL injections that may reveal data. Encrypting the data would stop minimise problems if someone did gain access through an untrapped query - so for this, encryption is the solution.

For part 2 of your question:

Using MySQL encrypt/decrypt functions will stop someone who has access to the raw data, but not MITM or SQL injection or even CSV dumps taken for transport.

So, IMO (and it's only my opinion and the way I've done it) is to encrypt with PHP and sned the encrypted data over the wire, as that stops all methods of trapping the data, and a CSV dump will be "scrambled".

If you do that, you may as well use the varbinary / blob types as it stops you accidentally trying to read/edit in phpMyAdmin. Plus potentially saves a few bytes nominally (although this depends on indexes and other stuff - so that alone is not a winning argument).

And now the down side: searching and sorting. Anything you index or search on, if encrypted, will only match the entire, exact, case sensitive string padded to the correct length (normally a search will be case insensitive, and you can do part searches with LIKE). And if you want to ORDER BY then you need the original strings. So bear than in mind when designing the structure.

Hope that helps.

What's the best way to store sensitive data in MySQL?

Seriously, DON'T USE MySQL's aes_encrypt() It is the the most insecure method of using a block cipher. It is using ECB mode, and I can give a simple example demonstration why this is a serious mistake.

Plain text message:

alt text

The same message encrypted with ECB mode (doesn't matter what cipher you use):
alt text

The EXACT same message using CBC mode (again, it doesn't matter what cipher you use):
alt text

There are even more reasons not to use mysql's aes_encrypt, most notably every single query you send will also have the aes key that you use. If the database is compromised the attacker will enable logging and just get your aes key and decrypt the entire database.

So what should you use? I like this class for the time being. Its using CBC mode with a String2Key function and an IV. You can use the primary key as your IV, each message must have a unique IV. Its okay if the attacker knows the IV, and if they are sequential, so long as the block cipher implementation is secure. Reuse of an IV made WEP much less secure.

Encryption at rest and/or AES_ENCRYPT

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.

MySQL Encryption/Storing Sensitive Data,