EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL AES_Encrypt

MySQL AES_Encrypt

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byRavi Rathore

Updated May 29, 2023

MySQL AES_Encrypt

Definition of MySQL AES_Encrypt

The MySQL AES_Encrypt function implements the AES algorithm to encode a provided string as arguments. The AES algorithm, which stands for Advanced Encryption Standard, encrypts the data using a key length of 128 bits, extendable up to 256 bits. MySQL AES_Encrypt() encodes a specific string and produces a binary series. If the argument provided to the function is NULL, the output will also be NULL.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

We, a user, provide too small a key length. It may be null-padded, i.e., due to the memset, but if it’s too big, then it may xor the additional bytes using the first key_size bytes; suppose when the critical 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 configured AES to be used. Hence, for providing AES-128, we need a 128-bit as a random key or 32 hex characters.

Syntax of MySQL AES_Encrypt

The syntax for the MySQL AES_ENCRYPT() function is as follows:

AES_ENCRYPT (Encryp_String, Key_String);

The function named Encryp_String describes the arguments mentioned above, which are Encryp_String and Key_String.

  • Encryp_String:  This string 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 arguments, such as the key value or the string to be encrypted, are NULL, the function will return NULL. AES is an algorithm that operates at the block level, where padding encodes strings of irregular lengths. The resulting length of the string can be calculated using the following formula:

16 * (trunc(str_len/16) + 1);               //str_len denotes the string length

AES is a two-way encryption and decryption mechanism that provides security for sensitive data records while allowing the original data to be retrieved. The AES algorithm utilizes an encryption key as a seed to achieve this. AES implements a compound mathematical algorithm comprising two concepts: confusion and diffusion. Here, the confusion process helps to hide the relationship between the original data and the encrypted data result. In contrast, the Diffusion process functions to shift, alter or adjust the data compositely.

When executed, the function AES_ENCRYPT() in MySQL will return the value, which is a binary string, after converting the original plaintext. The MySQL AES_ENCRYPT() function supports MySQL versions 5.7, 5.6, 5.5, 5.1, 5.0, and 4.1.

How does AES_ENCRYPT Function Work 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 key length is 128 bits, but 196 and 256 bits can also be implemented as described. The key length in AES is a trade-off between safety and performance.

Let us view the code example to show the working of the AES_Encrypt() function as below:

SELECT AES_Encrypt('mysqlcoding','mysqlkeystring');

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 the 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, indicating encryption using a key length of 128 bits and mode ECB.
  • The optional argument init_vector delivers an initialization vector for this block encryption mode, which needs it.

Modes that require the init_vector argument should have a length of 16 or more bytes, with any bytes beyond 16 being ignored. 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 the modes that do not need the optional argument init_vector will be disregarded, and an associated warning is produced if it is stated.

To use an initialization vector (IV), a random string of bytes can be generated by invoking RANDOM_BYTES(16). When the encryption mode requires an IV, you should consistently use the generated vector for the encryption and decryption of any string.

Certainly! Below is a table list that explains various allowed block encryption modes along with the associated initialization vector (IV) argument required:

Block Encryption Mode  Initialization Vector Needed
ECB No
CBC Yes
CFB1 Yes
CFB8 Yes
CFB128 Yes
OFB Yes

It should be noted that statements which implement MySQL AES_Encrypt() or AES_Decrypt() are insecure for statement-based reproduction.

Examples of MySQL AES_Encrypt

Let us illustrate some examples to show the MySQL AES_Encrypt() function works as follows:

1. Executing AES_Encrypt() function on a string by SELECT statement:

SELECT AES_ENCRYPT('XYZ','key');

The MySQL AES_Encrypt() uses the SELECT statement to find the outcome and encrypt the string in the MySQL server.

Output:

MySQL AES_Encrypt-1.1

2. Executing AES_Encrypt() function on a bigger string by SELECT statement:

SELECT AES_ENCRYPT('MySQLdatabasefunction','key');

Output:

Using SELECT statement:-1.2

3. Executing AES_Encrypt() function on a NULL string by SELECT statement:

SELECT AES_ENCRYPT(NULL,'key');

Output:

using SELECT statement 1.3

4. Executing AES_Encrypt() function implementing a sample table in the database:

INSERT INTO demo VALUE (AES_ENCRYPT('mystring','key'));

If explained in the above query, the function encrypts the particular string mystring with a 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');

Output:

MySQL AES_Encrypt-1.4

The AES_ENCRYPT() function accepts the key value “Google” and the string to be encrypted, “All is Well,” as arguments.

Conclusion

  • The MySQL AES_ENCRYPT() function is considered insecure because it defaults to using ECB mode unless 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 the AES standard algorithm also permits these key lengths.

Recommended Articles

We hope that this EDUCBA information on “MySQL AES_Encrypt” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Function
  2. MySQL encode()
  3. Amazon RDS for MySQL
  4. MySQL Cheat Sheet