In MySQL there are builtin AES_ENCRYPT() and AES_DECRYPT() functions which take the form of:

AES_ENCRYPT(str, key_str)

What length is required for the key_str argument? Can it be variable? What is the minimum and maximum string key lengths?


MySQL's AES_ENCRYPT function is insecure by default, as it uses ECB mode unless configured otherwise. The documentation provides an example of how to use CBC mode with a 256 bit key (though their example of a key is terrible):

mysql> SET block_encryption_mode = 'aes-256-cbc';
mysql> SET @key_str = SHA2('My secret passphrase',512);
mysql> SET @init_vector = RANDOM_BYTES(16);
mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);

What key length you want to use depends on what block_encryption_mode you configure. Supported key lengths are 128, 192, and 256 (which also happen to be the only key lengths allowed by AES).

It can be seen here that if the provided key is too small it will be null-padded (due to the memset), and if it is too large it will xor the extra bytes with the first key_size bytes (e.g. if the key size is 4 bytes and the provided key is 12345678, it will xor 5678 with 1234 and use the result as the key). For best security you should use a random key of the size you configure AES to use. For AES-128 you want a 128 bit random key, or 32 hex characters:

SELECT AES_ENCRYPT('text', UNHEX('6133C3D40B2BF9267E85ED0C2FDDC686'), @init_vector);

For AES-256 you want a 256 bit random key, which is 64 hex characters:

SELECT AES_ENCRYPT('text', UNHEX('08672D4D2424CFE10E5221BF2EB8409C57431B30B55D6AE2D167E5F9682EF711'), @init_vector);

The MySQL documentation fails to note that the IV (initialization vector) must not be reused for multiple encryptions, and of course the IV must be stored to allow for decryption.

It should also be noted that none of the encryption modes that MySQL supports provide authenticity, and of the supported modes, ECB is terrible and CBC, CFB, and OFB are all malleable.

| improve this answer | |

Variable Key Length:

Yes, you can use different key length if you are going to use multiple keys. And the key size depends on type of data.

For instance, NSA considers 128 bit keys good enough only for data with SECRET designation. For TOP SECRET though, it requires 256 bit keys. The European Network of Excellence in Cryptology report also recommends 128 bit keys for long term protection, but says 256 bit keys are the only good protection against quantum computers.

Key Sizes:

AES supports three different key sizes. If you specify smaller key then it would be automatically padded by AES.

  1. 128 bits
  2. 192 bits
  3. 256 bits

The standard 128 bit keys are used in AES_Encrypt() and AES_Decrypt(). However you can extend it to 256.

Please make sure you select field type as binary because AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

Sample Code:


CREATE  TABLE `user` (
  `first_name` VARBINARY(100) NULL ,
  `address` VARBINARY(200) NOT NULL ,
  PRIMARY KEY (`id`));

Insert statement with encryption function:

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('John', 'usa2018'), AES_ENCRYPT('Obama', 'usa2019'));

Select statement with decryption function:

SELECT AES_DECRYPT(first_name, 'usa2018'), AES_DECRYPT(address, 'usa2019') from user;
| improve this answer | |
  • So if I use a key_str that is 48 characters does this cause any problems? – Justin Jul 31 '18 at 7:43
  • The keys sizes are more due to regulation than actual security, though 256 bit keys may become useful once quantum computers become large and fast enough. – AndrolGenhald Jul 31 '18 at 15:53
  • You say you can extend the key size to 256 bits, but you don't describe how. If you just specify a longer key it will xor bits 129-256 with bits 1-128 and use the resulting 128 bit key. – AndrolGenhald Jul 31 '18 at 16:48

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.