Using AES Encryption In MySQL Statements TO Secure Column Content

Share on your favorite platform

What is AES encryption?

Advanced Encryption Standard is a symmetric block cipher (symmetric means the same key is used for encryption and decryption), which in 2001 announced by the U.S Federal Information Processing Standards FIPS and adopted by U.S government. and naturally, we consult our users to do the same AES algorithms has a fixed block size of 128bit and an applicable key sizes of: 128bit, 192bit, and 256bit. Key size is tradeoff between performance and security, bigger key size is better security, but it means more processing overhead and latency. AES widely used to protect data at rest. Applications such as:

  • Database Encryption.
  • Self-encrypting desk drives.
  • Storage Encryption

MySQL AES Encryption/Decryption Function

MySQL database offers a very straight-forward official AES encryption/Decryption methodology. a built in AES_ENCRYPT() and AES_DECRYPT(), these functions support the use of all AES key sizes (128bit, 192bit, 256bit). These functions use key size 128bit by default, and the other key sizes can also be used.

The general form oft he encryption function is : AES_ENCRYPT(srt,key_str[,init_vector])

And the general form of the decryption function is:

The general form of the deccryption function is : AES_DECRYPT(crypt_str,key_str[,init_vector])

crypt_str is the encrypted output of AES_ENCRYPT.

AES_ENCRYPT() encrypts the string str using the key string key_str and returns a binary string containing the encrypted output.

AES_DECRYPT() decrypts the string str using the key string key_str and returns the original string.

A real-world example

Here I’m creating a table containing a company subscribers info, the info is their names, and their phone numbers:

Straight-forward table creation

We want to make the subscriber_phone column private so only database users with proper level of access can view it. Below simple insert statement create a record of new subscriber named [Bryan] and encrypts his phone number on the fly using the password [secret] .

Using AES_ENCRYPT in INSERT statement

If we do select for our record the phone will show in encrypted gibberish

Result of SELECT statement shows encrypted text in ‘sbscriber_phone’ column

Now for the proper Database user who is allowed to view Bryan’s phone, he/she should have the decryption password to use it in the SELECT statement as follows.

AES_DECRYPT function inside SELECT statement to get plaintext in the result

Share on your favorite platform

Leave a Reply

Your email address will not be published. Required fields are marked *