Several ways to encrypt and decrypt MySQL (summary)

Written in front

I encountered a problem before, which is how to encrypt MySQL information. In fact, there are two ways of encrypting data. One is to encrypt data outside the database and then store it in the database. The second is to encrypt data inside the database. The difference between the two is that the second one is more convenient to use than the first one, because if it is encrypted externally, each search if it is based on the encrypted item, you need to calculate the encrypted data first and then put it into SQL, and the returned data is also encrypted data, which needs to be decrypted externally; while the second one can directly pass the original value and the encrypted key in SQL, and decryption can also be completed in SQL. This makes it impossible to complete some combined SQL queries using the first method, such as when the query field is the result of another query, because it must go through an external encryption process.

Two-way encryption

There are three methods of two-way encryption:

ENCODE/DECODE

Two values ​​are passed in, one is the record to be encrypted, and the other is the encryption and decryption key. The length of the binary character after encryption is the same as the original length, and it is stored in the blob type

BLOB type fields are used to store binary data. In MySQL, BLOB is a type series, including: TinyBlob, Blob, MediumBlob, LongBlob. The only difference between these types is the maximum size of the stored file. Four MySQL BLOB types Type Size (Unit: Bytes) TinyBlob Maximum 255 Blob Maximum 65K Medium.

encryption:

SELECT ENCODE('mytext','mykeystring');

result:

mysql> SELECT ENCODE('mytext','mykeystring');
+--------------------------------+
| ENCODE('mytext','mykeystring') |
+--------------------------------+
| ">¿¡È | 
+--------------------------------+
1 row in set (0.00 sec)

Decryption:

SELECT DECODE(ENCODE('mytext','mykeystring'),'mykeystring');

result:

mysql> SELECT DECODE(ENCODE('mytext','mykeystring'),'mykeystring');
+------------------------------------------------------+
| DECODE(ENCODE('mytext','mykeystring'),'mykeystring') |
+------------------------------------------------------+
| mytext | 
+------------------------------------------------------+
1 row in set (0.00 sec)

AES_ENCRYPT/AES_DECRYPT

This encryption algorithm uses AES (Advanced Encryption Standard) and key_str encryption. The length of key_str can reach 256 bits. The encrypted result is a binary string stored in blob type.

encryption:

SELECT AES_ENCRYPT('mytext', 'mykeystring');

result:

mysql> SELECT AES_ENCRYPT('mytext', 'mykeystring');
+--------------------------------------+
| AES_ENCRYPT('mytext', 'mykeystring') |
+--------------------------------------+
| •› ¨í ƒðb áÒ9•j | 
+--------------------------------------+
1 row in set (0.00 sec)

Decryption:

SELECT AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'), 'mykeystring');

DES_ENCRYPT/DES_DECRYPT

This encryption method uses 3DES (Triple Encryption Data Algorithm, which means there is a gap in the encryption level). You can choose to use key_num or key_str when encrypting.

For example:

SELECT DES_ENCRYPT('mytext',5),DES_ENCRYPT('mytext','mypassward');

The output is:

mysql> SELECT DES_ENCRYPT('mytext',5),DES_ENCRYPT('mytext','mypassward');
+-------------------------+------------------------------------+
| DES_ENCRYPT('mytext',5) | DES_ENCRYPT('mytext','mypassward') |
+-------------------------+------------------------------------+
| … ÿc}æ¤~ | ÿ ]ï×ñ”Å | 
+-------------------------+------------------------------------+
1 row in set (0.00 sec)

Use DES_DECRYPT when decrypting

However, there is a sentence in w3resource: This function works only with Secure Sockets Layer (SSL) if support for SSL is available in MySql configuration. My personal understanding is that if this encryption method is used, you must use SSL secure connection to connect to the database, otherwise the higher encryption level will be wasted.

One-way encryption

In fact, there has always been a debate online about whether one-way encryption is encryption, for example, is MD5 encryption? , I will treat it as an encryption algorithm here, no need to argue

MD5 encryption

The result of MD5 encryption is a 32-bit hexadecimal binary string.

SELECT MD5('w3resource');

The result is:

mysql> SELECT MD5('w3resource'); 
+----------------------------------+
| MD5('w3resource') |
+----------------------------------+
| b273cb2263eb88f61f7133cd308b4064 | 
+----------------------------------+
1 row in set (0.04 sec)

ENCRYPT

ENCRYPT is implemented using the Unix crypt() system call, which returns a binary string. Because it is based on Unix system calls, it will return NULL on Windows.

encryption:

SELECT ENCRYPT('w3resource', 'encode');

mysql> SELECT ENCRYPT('w3resource', 'encode');
+---------------------------------+
| ENCRYPT('w3resource', 'encode') |
+---------------------------------+
| NULL | 
+---------------------------------+
1 row in set (0.00 sec)

SHA1 encryption

SHA1 returns a binary string of 40 hexadecimal digits. If the input is NULL, the output is also NULL.

SELECT SHA1('w3resource');

mysql> SELECT SHA1('w3resource');
+------------------------------------------+
| SHA1('w3resource') |
+------------------------------------------+
|d228359c41174cede6b3c401eb8d11746a4ad1eb | 
+------------------------------------------+
1 row in set (0.00 sec)

PASSWORD

This is generally used to encrypt passwords.

When the input is NULL, the output is also NULL

mysql> SELECT PASSWORD('w3resource');
+-------------------------------------------+
| PASSWORD('w3resource') |
+-------------------------------------------+
| *EE0804DDC2CC3E85A47191ECCCBA29B775DFFA77 | 
+-------------------------------------------+
1 row in set (0.00 sec)

Reference
https://www.w3resource.com/mysql/encryption-and-compression-functions/decode().php
https://blog.csdn.net/Gpwner/article/details/51598344?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

This concludes this article on several ways to MySQL encryption and decryption (summary). For more information about MySQL encryption and decryption, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!