MySQL implements AES encryption/decryption functions AES_ENCRYPT/AES_DECRYPT. These are using a relatively insecure key size (128 bits, corresponding to "SECRET" according to NSA) and block mode (ECB, encrypting equal blocks with equal code blocks) to calculate the cipher. This work aims to enhance the security strength of these functions by supporting for larger key sizes and different block modes. User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html http://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes- encrypt http://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes- decrypt http://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_random- bytes http://dev.mysql.com/doc/refman/5.6/en/server-system- variables.html#sysvar_block_encryption_mode
Requirements for @@block_encryption_mode: 1) The variable @@block_encryption_mode must be settable for every session. The ENCRYPT and DECRYPT functions must use the value set in @@session.block_encryption_mode. 2) If @@session.block_encryption_mode is not explicitly set to anything, it must hold the value of @@global.block_encryption_mode. 3) Setting @@global.block_encryption_mode needs SUPER privileges and if anyone else tries to modify it, an error must be thrown. 4) An error must be thrown if we try to set the variable with an invalid string. Requirements for AES_ENCRYPT/DECRYPT: 5) The cipher generated/decrypted must be according to the string set for @@block_encryption_mode. Along with an mtr test, some manual testing can be done using few online tools. 6) If an initialization vector (IV) is required for a particular mode, an error must be thrown if it is not provided as an argument in AES_ENCRYPT/DECRYPT function. 7) if IV is supplied but not required by the block mode an warning will be generated 8) the IV, when supplied must be 16 bytes or longer. If a string longer than 16 bytes is supplied only the first 16 bytes will be used. if the string is shorter or null an error will be thrown. If non-string arguments are supplied they'll be implicitly converted to strings. Requirements for RANDOM_BYTES(): 9) RANDOM_BYTES will reject all sizes smaller than 1 and larger than 1024.
AES encryption/decryption SQL functions ======================================= MySQL currently provides AES_ENCRYPT() and AES_DECRYPT() functions to perform AES encryption/decryption of the given plain/cipher text using the specified key. The mode of operation used is ECB. With this worklog, the AES_ENCRYPT function fetches the encryption mode to follow, from a server variable @@block_encryption_mode. The same variable is also used by AES_DECRYPT function for decrypting the cipher. This variable has read/write in both global and session contexts and is settable from my.cnf and the command line. Setting the global context of this variable needs SUPER privileges. This variable needs to be set with a value of the format: aes-<keylength>-<mode>. For example, to encrypt data with CBC mode with key length 256, the variable needs to be set as aes-256-cbc. The default value for this variable is aes-128-ecb. Supported AES Mode of Operations ================================ The following modes of operations are supported by OpenSSL and YaSSL libraries respectively : OpenSSL: (1) ECB (2) CBC (3) CFB1 (4) CFB8 (5) CFB128 (6) OFB YaSSL: (1) ECB (2) CBC ECB mode does not require an initialization vector, where as other modes need it. Hence the AES_ENCRYPT() and AES_DECRYPT() functions will now on accept an optional argument for this purpose. AES_ENCRYPT(str, key_str [, IV]) AES_DECRYPT(crypt str, key_str [, IV]) If IV is not provided for the modes which need it, an error will be thrown. If IV is provided for ECB mode which does not need it, a warning will be thrown saying that IV will be ignored. Generation of Initialization Vector (IV) ======================================= Initialization vector would be generated using SSL libraries'RNG. YaSSL : yaSSL::RAND_bytes(buf, buf_size); OpenSSL : RAND_bytes(buf, buf_size); A new function RANDO_MBYTES can be used to generate binary random bytes string for this purpose. It does take one argument : the length of the binary string to be generated. The maximum length of the random bytes generated by that function will artificially be limited to 1023 bytes to avoid memory problems. eg: select RANDOM_BYTES(16) New Server Stat-up option ========================= --block-encryption-mode=mode_string New Server Error (ER_AES_INVALID_IV) ======================================== This new error would be raised when the initialization vector supplied to aes_encrypt()/aes_decrypt() is too short