MySQL encryption/compression Functions

These problems may change the data value. In general, the above problem may occur when you use non-binary string data types (such as char, varchar, text, and other data types.

• AES_ENCRYPT () and AES_DECRYPT ()

AES_ENCRYPT () and AES_DECRYPT () can encrypt/decrypt data using the official AES algorithm. This algorithm uses a 128-bit key for encoding, but you can extend it to 256-bit. MySQL uses a 128-bit key because the algorithm is faster and secure for most users.

The AES_ENCRYPT (str, key_str) function encrypts a string and returns a binary string. The AES_DECRYPT (crypt_str, key_str) function can decrypt data encrypted using the Advanced Encryption Standard algorithm and return the original string. The input variable can be of any length. If the input variable is NULL, the return result of this function is also NULL.

Because AES is a block-level algorithm, we need to use a whitelist to encode non-even-length strings.

• ENCODE () and DECODE ()

ENCODE (str, pass_str): This function uses pass_str as the password to encrypt the string str. The encrypted result can be decrypted using the DECODE () function. The result returned by this function is of the same length as str. DECODE (crypt_str, pass_str): This function uses pass_str as the password to decrypt the encrypted string crypt_str using ENCODE.

• DES_ENCRYPT () and DES_ENCRYPT ()

DES_ENCRYPT (str [, {key_num | key_str}]): This function uses the Triple DES algorithm together with the given key to encrypt the string.
DES_DECRYPT (crypt_str [, key_str]): This function decrypts a string encrypted using DES_ENCRYPT (). If an error occurs, this function returns NULL.

• COMPRESS () and UNCOMPRESS ()

COMPRESS (string_to_compress): This function compresses a string and returns a binary string. This function must be compiled by MySQL together with a compression library, such as zlib. Otherwise, the return value of this function is always NULL. The compressed string can be decompressed using the UNCOMPRESS () function. UNCOMPRESS (string_to_uncompress): This function decompress a string compressed by the COMPRESS () function. If the variable is not a compressed value, NULL is returned.

• PASSWORD ()

PASSWORD (str): This function is used to encrypt the MySQL password stored in the PASSWORD column of the user table. The PASSWORD () function is used by the authentication system on the MySQL server. You should not use it in your own applications. If you need to use the encryption function, you can use MD5 () or SHA1 () instead.

The encryption result is as follows:

In the MySQL user table of the mysql System database, there is a column named Password, which stores the password data of the user encrypted by the Password function. As follows:

• ENCRYPT ()

ENCRYPT (str [, salt]): This function encrypts str by using a Unix crypt () System Call and returns a binary string. The salt variable should be a string containing more than two characters. If salt is not given, a random value is used. If the crypt () system call is unavailable on the user's operating system (in Windows), this function returns NULL.

• MD5 ()

MD5 (str): This function calculates the 128-bit MD5 checksum of a string. The returned result is a binary string consisting of 32 hexadecimal digits. If the variable is NULL, return NULL.

The encryption result is as follows:

• SHA1 ()/SHA ():

The SHA1 (str)/SHA (str) function computes the 160-bit SHA-1 checksum of the str string. The return value is a binary string consisting of 40 hexadecimal digits. If the variable is NULL, NULL is returned.

The encryption result is as follows: