What is the recommended approach for encrypting/decrypting large text data in MySQL?

Question

I have a client/server application that runs on an intranet, and I have the requirement to encrypt the data in one field of one table of the database. That field is currently TEXT data type, which I have learned is not good for encryption in MySQL. The text stored in the field is similar to an employee performance evaluation containing narrative comments from supervisors. Because of personal information restrictions concerning the network the system runs on, this data cannot be stored in plain text. The data also needs to editable by different users with differ "roles" (authentication levels) via the client side of the application.

So... I have been researching how to encrypt this data, and this is what I understand so far:

• MySQL docs explain the AES functions, and that I should change the field datatype likely to a BLOB to accomodate the encrypted information.
• The key will have to be fixed (i.e. not salted with a user password etc. as I have read on SO), because different users need to be able to edit/review the comments.
• I think the best option is to store the key and pass it as a variable in the php code, similar to what I do with the MySQL login information for the application.

Is using the AES functions in this scenario a suitable way to proceed with this, or are there issues I don't know about? I did wonder about problems (performance?) because of the potential length of the text. Most examples I have found relate to encrypting smaller data: e.g. name, address, credit card numbers, etc.

Any advice would be greatly appreciated. Thanks in advance!

Answer 1

Using AES, in CBC or CTR mode is fine. Avoid ECB mode as it is insecure. Use PKCS7 padding.

If you want to store the encrypted file as text, rather then a BLOB, then convert the binary cyphertext to Base64 before saving to the database. Base64 only uses text characters. You will obviously need to convert the Base64 back to binary before decrypting.

Your major problem is key handling, since everyone with access to the database is going to need the key and you cannot store the key in the database itself. You may want to take some expert advice on this as it is critical.

Answer 2

Security of the DB

First you need to ask yourself: "why are you encrypting the data in the database?".
The reason is that the database might fall into the wrong hands.
For this reason you cannot store the key in the database itself.
You must assume that all data in the DB is known to an attacker.

Therefore the only answer is to have the key outside the DB.
I would advise to salt the key using data in the same row as the article, so that an attacker cannot use a rainbow table against all articles.

pseudocode for select statement:

SELECT AES_DECRYPT(article, CONCAT(salt, '$secret_key')) FROM articles
WHERE id = '123' 

PHP security
Note that listing the AES encryption key in the PHP source code will also be a fail.
It will have to only live in memory on the computer which also needs to be secure.
An option is to read it in from a remote computer (make the transfer encrypted) that is secure (datacenter with guards) or have a senior official key it in upon program startup.

How to avoid the MySQL ECB hole
If you need to have it really secure, you will have to do the encryption in php.

See this article to know why MySQL (which uses ECB mode) has a problem: http://en.wikipedia.org/wiki/Block_cipher_modes_of_operation#Electronic_codebook_.28ECB.29

pseudocode

<?pseudophp
$secretmessage = $_GET['secret_message_from_user'];
$randomprefix = hash('sha512',$timestampinmilliseconds);
$secretmessage = $randomprefix."@@@@".$secretmessage; 
//$password = "really long password entered by a trusted superuser";
$key256 = hash('sha512',$password); //stuff the password into 256 bits.
//You'll have to check that the output is really 256 bits, an tweak it if not.
$iv =  '1234567890123456'; //this is public, because the iv is already in the text.

printf("iv: %s\n",bin2hex($iv));
printf("key256: %s\n",bin2hex($key256)); //debug stuff
printf("message before\n %s\n",$secretmessage);

//We use AES aka RIJNDAEL.
$cipher = mcrypt_module_open(MCRYPT_RIJNDAEL_256, '', MCRYPT_MODE_CBC, '');
if (mcrypt_generic_init($cipher, $key256, $iv) != -1)
{
  // PHP pads with NULL bytes if $cleartext is not a multiple of the block size..
  $cipherText = mcrypt_generic($cipher,$cleartext );
  mcrypt_generic_deinit($cipher);
  // Display the result in hex.
  printf("256-bit encrypted result:\n%s\n\n",bin2hex($cipherText));
}