Encrypting MySQL's DB

AES Encryption

Sometimes clients want that the information they collected from the user should be encrypted and stored in database. Data encryption and decryption is a common technique for secured data. In this article I’ll show how could you use mysql’s built in function to encrypt and decrypt data.

Suppose you’ve a table where you want to encrypt user’s name & address. So look below the structure of the table. In this table we will store name and address as encrypted.

To encrypt & decrypt mysql data we will use AES_ENCRYPT() and AES_DECRYPT() functions. These functions used the official AES (Advanced Encryption Standard) algorithm & encode data with a 128-bit key length. 128 bits is much faster and secure enough for most purposes.

Why we used VARBINARY data type instead of VARCHAR:

Because AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

AES is a block-level algorithm. So when data encrypted it is padded. So you can calculate the length of the result string using this formula:

1. 16 × (trunc(string_length / 16) + 1)

So if your address field structure is = VARCHAR(100) ; //100 length of varchar Then before your encryption it should be converted = 16 * (trunc(100/ 16) + 1) = 16 * (6.25 + 1) = 16 * 7.25 = 116

So VARCHAR(100) should be converted to VARBINARY(116) minimum. I suggest use little more like VARBINARY(150) in this case.

Because if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. But it is also possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.


1| AES_ENCRYPT(str, key_str);

2| AES_DECRYPT(crypt_str,key_str);

Please remember, the encryption and decryption will occur based on a key. So you’ve to keep that key in a secret place and using variable you could pass the key to mysql to encry

pt and decrypt data.

Now look how I insert data using AES_ENCRYPT, where I used key ‘usa2010’ :

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));

Now look how I decrypt data using AES_DECRYPT:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.

#mysql #database

Recent Posts 
Serach By Tags
No tags yet.