4

There are a number of questions that discuss storing sensitive information in MYSQL using encryption. Some make great suggestions about hardening the web server, database server, and web application (Store 'sensitive' data in MySQL DB). However, little has been discussed about the exact implementation of MYSQL's AES_Encrypt and AES_Decrypt functionality other than a couple questions dealing with technical issues around usage (How to use AES_ENCRYPT and AES_DECRYPT in mysql).

I am using the MYSQL prepared statements below to encrypt sensitive information:

$stmt = $db->prepare("INSERT INTO Table_Name (SensitiveData) VALUES (AES_ENCRYPT(?,UNHEX(SHA2('RandomStringofData',512))))");
$stmt->bind_param('s',$SensitiveData);
$stmt->execute();
$stmt->close();

I use the AES_DECRYPT function with the same SHA2 string to decrypt the data when needed. The way I understand this, short of someone accessing the web server itself or figuring out the SHA2 passphrase, they cannot easily access this sensitive data. For example, someone gaining access to the database server will not help them interpret this sensitive data.

I have several questions:

  1. I read about the Init Vector here: http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html. I don't quite understand how this works or whether it is something that should be implemented. What is an Init Vector and can it help improve the security of the encryption method I am using above?
  2. I am uncomfortable having the SHA2 passphrase be in plain text in the file that encrypts and decrypts this data. Is there an alternative way to code this so that it is located in a file off the public directory?
  3. One pitfall of this system is that if someone hijacks the web application, they can still get access to this sensitive data simply by running the script that decrypts the information. Although I have robust access control, if this is also circumvented, I am looking at a situation where data can be compromised. I've implemented the SSL precautions and others recommended in this link: How can I store sensitive data securely in a MySQL database?. I've also reviewed the recommendations in this link: How do you Encrypt and Decrypt a PHP String?. Is there a way to have this data decrypted for other applications to use, or is it far more secure to treat it like a password and just compare the hashed values to make sure they are equal?
  4. My final question has to do with overhead. This is a huge database that measures several gigabytes in size, with millions of rows and hundreds of columns. I am encrypting only a few fields in this database and they are not used as indices. How much of a performance impact am I really seeing with something like this?

Thanks.

Community
  • 1
  • 1

1 Answers1

4

Do not use AES_ENCRYPT() for data security. If your application has sensitive data which must be stored encrypted, you should implement encryption and decryption in the application (i.e, in PHP code).

There are several major issues with AES_ENCRYPT():

  • The encryption key is transmitted to the MySQL server. It will be visible to other clients in SHOW PROCESSLIST while the query is running. Additionally, if the general log or slow query logging are enabled, the key may be written to those logs.

  • If you are not using SSL to connect to MySQL -- which you probably aren't -- both the encryption key and the plaintext of the sensitive data will be transmitted over the network, and may be intercepted there.

  • Many commonly used versions of MySQL (older than 5.7.4) only support ECB mode. ECB is insecure.

When you implement encryption in PHP, do not use mcrypt. It is buggy, unmaintained, and is deprecated in PHP 7.1 (and will probably be removed entirely in later versions). Ideally, you should use a library which encapsulates as much of the encryption as possible (like libsodium); failing that, use the encryption primitives available in the OpenSSL extension.

  • This helps clarify a few things, and also raises a few other questions. By encrypt/decrypt in the application, do you mean run the encryption in the PHP code itself and save the encrypted value to the database? I am not using ECB or mcrypt, but it sounds like libsodium may be a possible solution. So if I am doing the encryption/decryption in the app, do I still need libsodium? –  Dec 19 '16 at 03:19
  • 1) Yes. The database should just handle storing and retrieving data. Encryption should be in PHP code. 2) libsodium is how you should perform encryption/decryption in the app. Avoid using cryptographic primitives directly. –  Dec 19 '16 at 04:11