In MySQL there are builtin
AES_DECRYPT() functions which take the form of:
What length is required for the
key_str argument? Can it be variable? What is the minimum and maximum string key lengths?
Information Security Stack Exchange is a question and answer site for information security professionals. It only takes a minute to sign up.Sign up to join this community
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.
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.
AES supports three different key sizes. If you specify smaller key then it would be automatically padded by AES.
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.
CREATE TABLE `user` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `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;