Examples of the correct way to use AES_ENCRYPT() and AES_DECRYPT() to encrypt and decrypt MySQL

Preface

Recently, I encountered a requirement at work: I needed to use AES_ENCRYPT() function to encrypt plain text and store it in MySQL, but I encountered some problems... Let me introduce it in detail below.

It is said that the encrypted ciphertext is decrypted and the result is NULL.

I took a look at the table structure she sent:

Then I saw that she encrypted a string using the AES_DECRYPT() function and then inserted it. After the execution was successful, a warning：
Query OK, 1 row affected, 1 warning (0.00 sec)

(No error but warning, probably because of sql_mode)

At this time, she ignored the warning, and after decrypting it through AES_DECRYPT() , she found that the plaintext obtained was NULL.

Looking back at the table structure, we find that its field attribute is "varchar" && the character set is ut8, and the warning is as follows:

mysql> show warnings;
+---------+------+------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE3f767\x12...' for column 'passwd' at row 1 |
+---------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

Check the documentation and see how these two functions are used:

-- Encrypt 'hello world' with the key 'key' and store the encrypted string in @passmysql> SET @pass=AES_ENCRYPT('hello world', 'key'); 
Query OK, 0 rows affected (0.00 sec)

-- Check the length of the encrypted string (all are powers of 2)
mysql> SELECT CHAR_LENGTH(@pass);
+--------------------+
| CHAR_LENGTH(@pass) |
+--------------------+
| 16 |
+--------------------+
1 row in set (0.00 sec)

-- Decrypt using AES_DECRYPT()mysql> SELECT AES_DECRYPT(@pass, 'key');
+---------------------------+
| AES_DECRYPT(@pass, 'key') |
+---------------------------+
| hello world |
+---------------------------+
1 row in set (0.00 sec)

So how do we save it?

Method 1:

Set the field properties to varbinary/binary/four blob types, and other binary field properties.

Create three fields with attributes of varbinary, binary, and blob.

And encrypt 'plaintext1', 'text2', 'plaintext_text3' with the key key and store them in the table.

Finally take it out.

mysql> CREATE TABLE t_passwd (pass1 varbinary(16), pass2 binary(16), pass3 blob);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_passwd VALUES (AES_ENCRYPT('plaintext1', 'key'), AES_ENCRYPT('text2', 'key'), AES_ENCRYPT('plaintext_text3', 'key')); 
Query OK, 1 row affected (0.01 sec)

mysql> SELECT AES_DECRYPT(pass1, 'key'), AES_DECRYPT(pass2, 'key'), AES_DECRYPT(pass3, 'key') FROM t_passwd;
+---------------------------+---------------------------+---------------------------+
| AES_DECRYPT(pass1, 'key') | AES_DECRYPT(pass2, 'key') | AES_DECRYPT(pass3, 'key') |
+---------------------------+---------------------------+---------------------------+
| Plaintext1 | text2 | Plaintext_text3 |
+---------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

Of course, the length of the attribute brackets depends on the length of the plaintext. Here, the plaintext is shorter, so only 16 is given.

Method 2:

Convert the ciphertext to hexadecimal and store it in a varchar/char column.

Here you need to use HEX() to deposit and UNHEX() to withdraw.

Create a field with a string attribute.

First encrypt 'hello world' with the key 'key2' using AES, and then convert the encrypted string into hexadecimal using the HEX function.

Finally, the encrypted string is taken out through UNHEX, and then decrypted through AES according to the key 'key2':

mysql> CREATE TABLE t_passwd_2(pass1 char(32));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_passwd_2 VALUES (HEX(AES_ENCRYPT('hello world', 'key2')));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX(pass1), 'key2') FROM t_passwd_2; 
+-----------------------------------+
| AES_DECRYPT(UNHEX(pass1), 'key2') |
+-----------------------------------+
| hello world |
+-----------------------------------+
1 row in set (0.00 sec)

Similarly, depending on the length of the plaintext, the length of the string encrypted by AES_ENCRYPT will also change, so the length of the string after HEX will also change.
In actual use, you only need to evaluate a reasonable value based on the business.

Method 3:

Store directly in varchar without hexadecimalization.

Going back to the beginning of the problem, it is not possible to store the encrypted string in the utf8 character set and the attribute is varchar.

Actually, just change the character set to latin1:

No warning will be reported during insert.

mysql> CREATE TABLE t_passwd_3(pass varchar(32)) CHARSET latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_passwd_3 SELECT AES_ENCRYPT('text', 'key3');
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;
+---------------------------+
| AES_DECRYPT(pass, 'key3') |
+---------------------------+
| text |
+---------------------------+
1 row in set (0.00 sec)

Although this method is beautiful, as long as you set the field character set to latin1, it may bring hidden dangers:

The document says:

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

The general idea is that if you use method ③ to directly store the encrypted string into the char/varchar/text type, it may have potential impact when performing character conversion or when spaces are deleted.

So if it must be stored in char/varchar/text, then refer to method ② and convert it into hexadecimal.

Or as in method ①, store it directly in the binary field.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

Reference Documents:

Chapter 12 Functions and Operators - 12.13 Encryption and Compression Functions