Shulou(Shulou.com)06/01 Report--

How does MySQL correctly use AES_ENCRYPT () and AES_DECRYPT () to encrypt and decrypt? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Preface

Recently, I encountered a requirement in my work like this: I need to use the AES_ENCRYPT () function to encrypt plaintext and store it in MySQL, but I have encountered some problems. Let's introduce it in detail.

It is said that the encrypted ciphertext will be decrypted and taken out as NULL.

I took a look at the watch structure she sent me:

Then she encrypts a string through the AES_DECRYPT () function, and then insert goes in. After the successful execution, a warning is displayed:

Query OK, 1 row affected, 1 warning (0.00 sec)

(it's not wrong but warning, probably because of sql_mode.)

At this time, she ignored the warning, and then decrypted it through AES_DECRYPT (), and found that the plaintext extracted was NULL.

Looking back at the table structure, we find that its field property is "varchar" & & the character set is ut8. Check that the warning is below:

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 to see the use of these two functions:

-- encrypt the 'hello world'' string with the key 'key',' and store the encrypted string 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 (all to the integer power of 2) mysql > SELECT CHAR_LENGTH (@ pass) +-+ | CHAR_LENGTH (@ pass) | +-+ | 16 | +-+ 1 row in set (0.00 sec)-- use AES_DECRYPT () to decrypt mysql > SELECT AES_DECRYPT (@ pass, 'key') +-- + | AES_DECRYPT (@ pass, 'key') | +-+ | hello world | +-+ 1 row in set (0.00 sec)

So how to save it?

Method ①:

Set the field properties to the four blob types of varbinary/binary/, such as binary field properties.

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

And encrypt the plaintext _ text3', the key is key, and store it 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 ('plaintext 1,' key'), AES_ENCRYPT ('text2',' key'), AES_ENCRYPT ('plaintext _ text3',' key')) Query OK, 1 row affected (0.01sec) 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') | +-+ | plaintext 1 | 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, which is shorter here, so only 16 is given.

Method ②:

Hexadecimal the ciphertext and then store it in the varchar/char column.

HEX () is needed here to deposit it and UNHEX () to take it out.

Create a field for a string property.

Encrypt the 'hello world' first with the key' key2' for AES, and then hexadecimal the encrypted string through the HEX function.

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

Mysql > CREATE TABLE t_passwd_2 (pass1 char (32)); Query OK, 0 rows affected (0.01sec) mysql > INSERT INTO t_passwd_2 VALUES (HEX ('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 string length after AES_ENCRYPT encryption varies depending on the length of plaintext, so the string length after HEX also varies.

In actual use, a reasonable value needs to be evaluated according to the business.

Method ③:

Store it directly in varchar without hexadecimalization.

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

In fact, just change the character set to latin1:

I won't sign up for warning when I'm in 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: 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 hidden dangers:

This sentence is written on the document:

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 main idea is that if you use the method ③ to store the encrypted string directly in the char/varchar/text type, it may have a potential impact during character conversion or when the space is deleted.

So if it has to be in char/varchar/text, refer to the method ② and hexadecimal it.

Or, like the method ①, it is directly stored in the binary field.

Thank you for reading! After reading the above, do you have a general idea of how MySQL correctly uses AES_ENCRYPT () and AES_DECRYPT () to encrypt and decrypt? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.