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

Question

I have a client / server application that runs on the intranet, and I have a requirement to encrypt data in one field of one database table. This field is currently a TEXT data type, which, as I found out, is not suitable for encryption in MySQL. The text stored in the field is similar to an employee performance rating containing explanatory comments from supervisors. Due to restrictions on personal information regarding the network in which the system is running, this data cannot be saved in plain text. Data should also be editable by different users with different "roles" (authentication levels) on the client side of the application.

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

• MySQL docs explains the functions of AES and that I should change the data type of the field, probably a BLOB, to accommodate encrypted information.
• The key must be fixed (i.e. it is not salty with the user's password, etc., as I read on SO), because different users should be able to edit / view comments.
• I think the best option is to save the key and pass it as a variable in php code, similar to what I do with the MySQL login information for the application.

Is using AES functions in this scenario the right way to do this, or are there issues that I don't know about? I wondered about issues (performance?) Due to the potential length of the text. Most of the examples I found relate to encrypting lesser data: for example, name, address, credit card numbers, etc.

Any advice would be greatly appreciated. Thanks in advance!

Answer 1

DB Security

First you need to ask yourself: "Why are you encrypting the data in the database?".
The reason is that the database may fall into the wrong hands.
For this reason, you cannot save the key in the database itself.
You must assume that all the data in the database is known to the attacker.

Therefore, the only answer is the presence of a key outside the database.
I would advise salting the key using the data on the same line as the article so that the attacker could not use the rainbow table for all the articles.

pseudocode for select statement: SELECT AES_DECRYPT(article, CONCAT(salt, '$secret_key')) FROM articles WHERE id = '123' 

PHP Security
Please note that listing AES encryption key in PHP source code will also be an error.
He will have to live only in the memory of the computer, which must also be safe.
The option is to read it from a remote computer (make an encrypted transmission), which is safe (a data center with protection), or have a senior official key when starting the program.

How to avoid MySQL ECB hole
If you need this to be truly secure, you will need to do encryption in php.

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

pseudo code

 <?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)); } 

Answer 2

Using AES in CBC or CTR mode is great. Avoid ECB mode as it is unsafe. Use the PKCS7 add-on.

If you want to save the encrypted file as text, not BLOB, then convert the binary cyphertext to Base64 before saving to the database. Base64 uses only text characters. Obviously, you will need to convert Base64 back to binary before decryption.

Your main problem is key processing, since everyone who needs access to the database will need a key, and you cannot store the key in the database itself. You can accept some expert advice on this, as it is important.