Mysql – ny approach for encrypting thesql column by key

computed-columnMySQLPHPtransparent-data-encryption

I have a scenario for encrypting user information in mysql db. Is there any approach to encrypting column by key. This key information will be there in application configuration file. This key we won't store any where in databases/triggers.

My approach is while connecting to database from application we'll pass key. With that key we'll get the information. If some user directly comes into db he will see the encrypted data in table columns.

Please suggest how can I proceed?

Best Answer

Have a look at the encryption/decryption functions. For example, using AES (see aes_encrypt):

SET @key_str = SHA2('My secret passphrase',512);
SET @crypt_str = AES_ENCRYPT('text',@key_str);
SELECT AES_DECRYPT(@crypt_str,@key_str);

Which gives, as expected:

+----------------------------------+
| AES_DECRYPT(@crypt_str,@key_str) |
+----------------------------------+
| text                             |
+----------------------------------+
1 row in set (0.00 sec)

So you can store the result from AES_ENCRYPT in the column, and then retrieve it using AES_DECRYPT.

The above code is also compatible with MariaDB (at least 10.2). MySQL additionally supports an init string to the AES_ENCRYPT and AES_DECRYPT functions, see the example at the link to AES_ENCRYPT above.

MySQL also offers InnoDB tablespace encryption, but that will encrypt the whole table, not just individual columns.

Related Question