MySQL Encryption/Compression function

These problems can cause changes in data values. In general, the above problem may occur if you are using a non-binary string data type, such as char,varchar,text data types.

• Aes_encrypt () and Aes_decrypt ()

Aes_encrypt () and Aes_decrypt () can encrypt/decrypt data using the official AES algorithm. The algorithm uses a 128-bit key to encode, but the user can extend it to 256-bit. MySQL chooses a 128-bit key because the algorithm is faster and is safe enough 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 decrypts data encrypted using the official AES (Advanced Encryption Standard) algorithm and returns the original string, and the input variable can be any length. If the input variable is NULL, the function returns the result also null.

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

• ENCODE () and Decode ()

ENCODE (str, PASS_STR): The function uses PASS_STR as a password to encrypt the string str, and its encrypted results can be decrypted by the decode () function. The result returned by the function is a length equal to Str. DECODE (Crypt_str, PASS_STR): The function uses PASS_STR as a password to decrypt the string encode after using CRYPT_STR () encryption.

• Des_encrypt () and Des_encrypt ()

Des_encrypt (str[, {key_num|key_str}]): The function uses the Triple DES algorithm to encrypt the encrypted string with the given key.
Des_decrypt (crypt_str[, Key_str]): This function decrypts a string that is encrypted by Des_encrypt () and returns null if an error occurs.

• COMPRESS () and uncompress ()

COMPRESS (string_to_compress): This function compresses a string and returns a binary string. This function requires that MySQL be compiled with a compression library, such as zlib, otherwise the return value of the function is always null. The compressed string can be decompressed by the uncompress () function. Uncompress (string_to_uncompress): This function compresses a string that is compressed by the compress () function. If the variable is not a compressed value, the result is returned as null.

• PASSWORD ()

PASSWORD (str): This function is used to encrypt the mysql password stored in the PASSWORD column in the user table. The PASSWORD () function is used by the authentication system in the MySQL server, and the user should not use the function in their own application. If you need to use cryptographic functions, consider using MD5 () or SHA1 () instead.

Examples of its cryptographic results are as follows:

In MySQL's system database MySQL user table, there is a column named password, which holds the password data of the user encrypted by the password function. As shown below:

• ENCRYPT ()

ENCRYPT (str[, salt]): This function encrypts str by using the UNIX crypt () system, and returns a binary string. Where the salt variable should be a string that contains more than two characters. If the salt is not given, a random value is used. If the crypt () system call is not available on the user's operating system (as is the case with the Windows operating system), the function returns NULL.

• MD5 ()

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

Examples of its cryptographic results are as follows:

• SHA1 ()/sha ():

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

Examples of its cryptographic results are as follows:

MySQL Encryption/Compression function