Encryption and compression functions

Supported functions

Storage note: These functions can return a value of the BYTE data type. Store results in a BLOB or VARBINARY column. If you use CHAR, VARCHAR, or TEXT, trailing spaces may be stripped and the value may not display correctly after character set conversion.

AES_ENCRYPT(str, key_str [, init_vector])

Encrypts str using key_str as the key and returns the ciphertext. init_vector is optional depending on the encryption mode.

The block_encryption_mode system variable controls the encryption mode. Set it in the format aes-keylen-mode, where keylen is the key bit length (128, 192, or 256) and mode is one of the following:

When a mode requires an initialization vector, init_vector must be at least 16 bytes — any excess is truncated. When a mode does not require one, init_vector is ignored.

For more information, see block_encryption_mode.

Example:

SET block_encryption_mode = 'aes-128-ofb';
SET @iv = RANDOM_BYTES(16);
SET @key = SHA2('secret key', 224);
SET @crypto = AES_ENCRYPT('polardb-x', @key, @iv);
SELECT @crypto;

+---------------------------+
| @crypto                   |
+---------------------------+
|  ß÷s,(ÿýÂåîA}ýO          |
+---------------------------+

AES_DECRYPT(crypt_str, key_str [, init_vector])

Decrypts crypt_str using key_str and, if applicable, init_vector. Returns the plaintext string. Uses the same encryption mode and key length as specified by block_encryption_mode.

Example (continued from AES_ENCRYPT above):

SELECT AES_DECRYPT(@crypto, @key, @iv);

+---------------------------------+
| AES_DECRYPT(@crypto, @key, @iv) |
+---------------------------------+
| polardb-x                       |
+---------------------------------+

RANDOM_BYTES(len)

Returns a random binary string of len bytes. Valid values of len are integers from 1 to 1024.

Example:

SELECT HEX(RANDOM_BYTES(16));

+----------------------------------+
| HEX(RANDOM_BYTES(16))            |
+----------------------------------+
| C83CF8A2499F407E15F34F6E32948CEA |
+----------------------------------+

MD5(str)

Returns a 128-bit MD5 checksum for str as a string of 32 hexadecimal digits.

Example:

SELECT MD5('polardb-x');

+----------------------------------+
| MD5('polardb-x')                 |
+----------------------------------+
| fa4900656bcd39dc90024e733fa4531f |
+----------------------------------+

SHA1(str), SHA(str)

Returns a 160-bit SHA-1 checksum for str as a string of 40 hexadecimal digits. This function provides more secure encryption than the MD5() function.

Example:

SELECT SHA1('polardb-x');

+------------------------------------------+
| SHA1('polardb-x')                        |
+------------------------------------------+
| a2e83af051f032b500f13c369976298208d821d1 |
+------------------------------------------+

SHA2(str, hash_length)

Calculates an SHA-2 family hash for str. hash_length specifies the output bit length: 224, 256, 384, 512, or 0 (equivalent to 256). This function provides more secure encryption than the MD5() function and the SHA1() function.

The resulting hexadecimal string length corresponds to the bit length:

Example:

SELECT SHA2('polardb-x', 384);

+--------------------------------------------------------------------------------------------------+
| SHA2('polardb-x', 384)                                                                           |
+--------------------------------------------------------------------------------------------------+
| 20222037666be5234d9af3c391f9c3a1a3e39b910f3f8081c32d972acca890c818d6c70025ff6c6d4b648bd91d66a3fe |
+--------------------------------------------------------------------------------------------------+