1

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?

2

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.

  • 1
    I suggest that the line with SHA2 adds nothing other than making the key_string harder to type, not harder to hack. – Rick James Apr 17 '18 at 19:24
  • 1
    The AES_* functions date back at least to MySQL 5.0. – Rick James Apr 17 '18 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.