Encrypt MySQL data using AES techniques

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.

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

You may be expected the table structure should be:

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR(50) NULL ,
`address` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

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:

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.

Syntax:

AES_ENCRYPT(str, key_str);
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 encrypt 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.

About mahmud ahsan

Founder And Lead Programmer at iThinkdiff.net

, , ,

21 Responses to Encrypt MySQL data using AES techniques

  1. Victor Scott January 12, 2010 at 1:59 am #

    This is very informative. However if you are looking for a way to encrypt your MySQL data that does not require any coding or changes to your application, take a look at what we’re doing at http://www.CritoTECH.com with ezNcrypt for MySQL.

    Cheers!

    Victor

  2. saiful103a February 7, 2010 at 12:42 am #

    this is really awesome…
    since i have done a little project in this arena for my algorithm course.

  3. Nick Hammond June 8, 2010 at 7:29 am #

    Verrrrrrrrrrrrrry Helpful. Hard to find answers to my mysql / php issues. Thanks a bunch.

  4. Darshan Shroff November 26, 2010 at 9:02 am #

    This was pretty helpful and written in a simple and concise manner, giving all the necessary details.

    Thanks,
    Darshan Shroff

  5. nagesh April 12, 2011 at 1:17 pm #

    this is an awesome example for encrypt and decrypt in mysql. keep it up, good work

  6. Mads July 17, 2011 at 1:04 pm #

    Thank your a very easy to understand article. I read your post and used it online within an hour. Much easier to understand and use than all the other php/mysql encryption articles I have tried to understand, including md5 that doesnt work with php here and which is not as good as aes. Once again thank you.

  7. Julia Mengen January 9, 2012 at 5:40 am #

    Hey Mahmud,

    you write:
    > 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:

    >> 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

    Afaik trunc is cutting off all numbers behind the decimal point. You can assume, that you get an encrypted string length
    = 16 * ( trunc( 100/ 16 ) + 1 )
    = 16 * ( trunc( 6.25 ) + 1 )
    = 16 * ( 6 + 1 )
    = 16 * 7
    = 112

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

    If understood the manual correctly, you can trust in the calculated maximum length of 112, no matter what the value of the string value is when you assume that the input string has a maximum length of 100.
    Since the algorithm will always deliver the calculated maximum string length, according to the manual, you don’t need “a little more” ;D

    As a simple example you can try

    > select LENGTH( AES_ENCRYPT(’0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789′,’testxyz’) );

    and

    > select LENGTH( AES_ENCRYPT(‘abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij’,’testabc’) );

    Both input strings have a length of 100 characters.

    Have fun : )

  8. sahshan January 28, 2012 at 2:00 am #

    where should i keep the decryption\encryption key?

    hard coded in the app code?

  9. Sean Delaney February 8, 2012 at 2:52 pm #

    I’m running MySQL Server 5.5.9 but I don’t have the VARBINARY option as a field type?

  10. paya March 6, 2012 at 5:21 pm #

    really awesome….am developing my major project from this as “database encryption”…..

  11. Hrishikesh April 4, 2012 at 6:10 am #

    Your Tutorial is very informative.
    I had a question, I was wondering, Is it possible to develop a master key in MySQL for decryption?

  12. Ausome1 July 11, 2012 at 9:04 am #

    Just a heads up thought: This doesn’t protect against man-in-the-middle attacks. Your applications still send the information in plain-text before it is AES encrypted by the database. You would be better off AES encrypting your data first, in your code, before sending it off to the database.

  13. Ravi August 30, 2012 at 12:36 pm #

    thanks buddy for this informative topic.

  14. Gerry Matte October 28, 2012 at 9:00 pm #

    when logging in to mysql without specifying a default schema:
    INSERT into civichelp.PersonEncrypted (username,pwd) VALUES(“myusername”,AES_ECRYPT(“mypassword”,”Mother00″));
    produces Error Code: 1305. FUNCTION AES_ECRYPT does not exist 0.000 sec

    if I instead login with a default schema set to civichelp:
    07:53:44 INSERT into civichelp.PersonEncrypted (username,pwd) VALUES(“myusername”,AES_ECRYPT(“mypassword”,”Mother00″)) Error Code: 1305. FUNCTION civichelp.AES_ECRYPT does not exist 0.000 sec

    Any idea why the function AES_ENCRYPT is not available on my system ? I’ve verified that I do not have a space character between AES_ECRYPT and (

    I am running mysql 5.1.43-community on a Windows 7 platform and my database is using only InnoDb tables.

    • Gerry Matte October 29, 2012 at 12:05 am #

      Sorry about that …. I spelled the function name incorrectly. When I use AES_ENCRYPT all is good.

  15. Anil May 10, 2013 at 12:54 pm #

    Its really helpful. But how to encrypt the float/integer data type field in Table. i.e.
    Let there is any field in my table
    Salary Float;
    Now how to encrypt this Salary field in the database as AES can be applied to only string?

    • Mark August 15, 2013 at 11:14 pm #

      One way: Convert the decimal to a string in your application and store it as a string and then to a parse to return it to a double after being retrieved.

  16. Carlos Rodriguez September 13, 2013 at 1:18 am #

    THANKSSSS!!! I try insert the AES_ENCRIPT() in a colum varchar, but inser NULL, but this method need VARBINAR….

    Thanks bro…

  17. Robbie Ferguson March 18, 2014 at 9:27 pm #

    Great article, thank you.

    Because of the way trunc() works, I thought I’d put together a simple spreadsheet to help me calculate the size of my VARBINARY() cells.

    Figured I’d share it here in case there are others having trouble with the math of it.

    http://www.baldnerd.com/calculate-the-size-of-a-varbinary-field-required-for-aes_encrypt/

    I also included a link back to your article on that post.

    Thanks for the great, informative post!
    -Robbie

Trackbacks/Pingbacks

  1. Ruby on Rails: Ruby on Rails: What is the best way to store sensitive information in a database. Is encrypting every row overkill? Is there a better way? - Quora - May 16, 2012

    [...] encryption is the utmost importance then yes row per row is needed Here is the link for more info : http://thinkdiff.net/mysql/encry… I hope you found my answer beneficial.Regards,SaraComment Loading… • Post • 6:48am [...]