MySQL uses AES_ENCRYPT () and AES_DECRYPT () for encryption and decryption,

MySQL uses AES_ENCRYPT () and AES_DECRYPT () for encryption and decryption,

Preface

Recently, I have encountered a requirement as follows:AES_ENCRYPT()The function encrypts plaintext and stores it in MySQL, but encountered some problems ...... The following is a detailed introduction.

It indicates that the encrypted ciphertext is decrypted to NULL.

Let's take a look at the table structure she sent:

Then, she encrypted a string through the AES_DECRYPT () function, and then inserted it in. After the execution is successful,warning：
Query OK, 1 row affected, 1 warning (0.00 sec)

(Warning is not reported, probably because of SQL _mode)

At this time, she ignores this warning and then passesAES_DECRYPT()After decryption, it is found that the obtained plaintext is NULL.

After looking back at the table structure, we found that the field attribute is "varchar" & the character set is ut8, and the check 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)

I checked the document and checked the usage of the two functions:

-- Encrypt 'Hello world' with the key 'key'. The encrypted string exists in @ pass mysql> SET @ pass = AES_ENCRYPT ('Hello world', 'key '); query OK, 0 rows affected (0.00 sec) -- take a look at the length of the encrypted string (both to the power of an integer of 2) mysql> SELECT CHAR_LENGTH (@ pass ); + -------------------- + | CHAR_LENGTH (@ pass) | + ---------------------- + | 16 | + -------------------- + 1 row in set (0.00 sec) -- use AES_DECRYPT () decrypt mysql> SELECT AES_DECRYPT (@ pass, 'key'); + ------------------------- + | AES_DECRYPT (@ pass, 'key ') | + --------------------------- + | hello world | + --------------------------- + 1 row in set (0.00 sec)

So how should we save it?

Method ①:

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

Create three fields: varbinary, binary, and blob.

Encrypt 'plaintext 1', 'text2', and 'plaintext _ text3', and save the key as a key to the table.

Finally, retrieve it.

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 ('plaintext 1', '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; + ------------------------- + signature + | AES_DECRYPT (pass1, 'key') | AES_DECRYPT (pass2, 'key ') | AES_DECRYPT (pass3, 'key ') | + plaintext + ------------------------- + | plaintext 1 | text2 | plaintext _ text3 | + plaintext + ------------------------- + ----------------------------- + 1 row in set (0.00 sec)

Of course, the length in the attribute brackets depends on the length of the plain text. Here, the plain text is short, so only 16 is given.

Method 2:

Convert the ciphertext into hexadecimal format and store it in the varchar/char column.

HEX () is used for storage.UNHEX().

Create a string attribute field.

Encrypt 'Hello world' with the key 'key2' for AES encryption, and then use the HEX function to generate the encrypted string in hexadecimal format.

Finally, the encrypted string is taken out through UNHEX, and then decrypted using the AES data 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, the length of the AES_ENCRYPT encrypted string varies according to the length of the plaintext, so the length of the string after HEX will also change.
In actual use, a reasonable value must be evaluated based on the business.

Method ③:

Directly stored in varchar without hexadecimal conversion.

Back to the beginning of the problem, it is not feasible to store the encrypted string to the utf8 Character Set and set the attribute to varchar.

In fact, you can change the character set to latin1:

It will not report the warning during the insert operation.

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: 0mysql> 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, you only need to set the field character set to latin1, but it may bring risks:

The document has written the following sentence:

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 wocould change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT ).

If Method ③ is used, the encrypted string is directly stored in the char/varchar/text type. When the character conversion or space is deleted, it may have potential impact.

Therefore, if char, varchar, or text must exist, refer to method 2 and perform hexadecimal conversion.

Or, like method ①, directly exists in the binary field.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.

Reference:

Chapter 12 Functions and Operators-12.13 Encryption and Compression Functions