Definition of MySQL AES_Encrypt
MySQL AES_Encrypt function is an encryption function that is responsible for encoding a string provided as arguments by implementing AES algorithm. Here, AES is an abbreviated form of Advance Encryption Standard, which when applied with encryption function helps to encrypt the data using a key length of 128 bits and this key length can be prolonged up to 256 bits. The function MySQL AES_Encrypt() performs to encode any particular string and results a binary string. If the provided argument for the function is NULL, then the output will also be NULL.
We a user provides too small a key length then it may be null-padded i.e. due to the memset, but if it’s too big then it may xor the addition al bytes using the first key_size bytes, suppose when the key size will be 4bytes along with the key as 12345678, then it will be xor to 5678 using the outcome as the key with 1234. Therefore, for the best security, we must implement a random key length of the size that is configured AES to be used. Hence, for provided AES-128, we need a 128-bit as a random key or say 32 hex characters.
Syntax of MySQL AES_Encrypt
The syntax for the MySQL AES_ENCRYPT() function is explained as follows:
AES_ENCRYPT (Encryp_String, Key_String);
The arguments mentioned above within the function named as Encryp_String and Key_String are described below:
- Encryp_String: This is the string provided that will be encrypted.
- Key_String: This is the key string to encrypt the first argument String.
These input arguments can be of any length. If any of these argument is provided to be NULL, then the outcome of this function will be NULL. Because AES is said to be a block-level algorithm where padding is implemented to encode irregular strings length so that the resultant length string can be considered using the following formula:
16 * (trunc(str_len/16) + 1); //str_len denotes the string length
AES can be defined as two-way encryption as well as a decryption mechanism that delivers a layer of safety for the sensitive data records while silently permitting the creative data to be repossessed. For this, an encryption key is applied as a seed provided in the AES algorithm. AES implements a compound mathematical algorithm comprising of two concepts such as confusion and the other diffusion. Here, the confusion process helps to hide the relationship of original data and encrypted data result whereas the Diffusion process functions to shift, alter or adjust the data in a composite way.
The function AES_ENCRYPT() in MySQL when executed will return the value which is a binary string after converting the original plaintext. This MySQL AES_ENCRYPT() function is supported for the MySQL versions such as MySQL 5.7, 5.6, 5.5, 5.1, 5.0, 4.1.
How AES_ENCRYPT Function Works in MySQL?
AES_ENCRYPT() function in MySQL applies encryption of data records by using the official AES algorithm, formerly recognized as ‘Rijndael’, where the AES standard allows several key lengths. The default one is 128 bits but as described 196 and 256 bits can also be implemented, where the key length is said to be a trade-off between safety and performance.
Let us view the code example to show the working of AES_Encrypt() function as below:
Here, the above MySQL query statement encodes the string specified like ‘mysqlcoding’ with the key mysqlkeystring. The output for this will be the following after execution:
AES_Encrypt() allows regulator of the block encryption mode and will receive init_vector as an optional initialization vector argument where:
- This system variable block_encryption_mode governs the mode for the server’s block-based encryption algorithms whose value is aes-128-ecb by default, which indicates encryption using a key length having 128 bits and mode ECB.
- The optional argument init_vector delivers an initialization vector for this block encryption modes which needs it.
For the modes, which need the argument init_vector it should be 16 bytes or lengthier where bytes in surplus of 16 are unnoticed. But an error will take place if init_vector is missing. So we can write as follows:
SELECT AES_ENCRYPT (Encryp_String, Key_String, @init_vector);
But for the modes, which do not need the optional argument init_vector, it will be disregarded and an associated warning is produced if it is stated.
Also, for using with the initialization vector, any random string of bytes can be generated by invoking the function RANDOM_BYTES(16). Thus, for the encryption modes which need an initialization vector, the similar vector should be implemented for either the encryption and decryption of any string.
Let us discuss and explain the table list where each is an allowed block encryption mode with the associated initialization vector argument that is needed:
|Block Encryption Mode||Initialization Vector Needed|
It should be noted that statements which implement MySQL AES_Encrypt() or AES_Decrypt() are insecure for the statement-based reproduction.
Examples of MySQL AES_Encrypt
Let us illustrate some examples to show the MySQL AES_Encrypt() function working as follows:
1. Executing AES_Encrypt() function on a string by SELECT statement:
The SELECT statement is used with the MySQL AES_Encrypt() to find the outcome and encrypting the string in MySQL server.
2. Executing AES_Encrypt() function on a bigger string by SELECT statement:
3. Executing AES_Encrypt() function on a NULL string by SELECT statement:
4. Executing AES_Encrypt() function implementing a sample table in database:
INSERT INTO demo VALUE (AES_ENCRYPT('mystring','key'));
If explained the above query the function encrypts the particular string mystring with key and enters the encrypted string as output into the table ‘demo’.
5. Executing AES_ENCRYPT() function with key value:
SELECT AES_ENCRYPT('All is Well','Google');
Here, the key value is Google and the string to be encrypted is All is Well in the arguments of AES_Encrypt() function.
- The MySQL AES_ENCRYPT() function is determined to be insecure as it implements ECB mode by default except configured otherwise.
- Depending on what the server’s block_encryption_mode a user configures, we can use the key length from the list of supported ones such as 128, 256 and 192 where these key lengths are also permitted by the AES standard algorithm.
This is a guide to MySQL AES_Encrypt. Here we also discuss the definition and syntax of MySQL AES_Encrypt along with different examples and its code implementation. You may also have a look at the following articles to learn more –