Explain the MySQL AES_ENCRYPT string key length.

The MySQL 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.