PolarDB:Encryption functions and compression functions

Supported encryption functions and compression functions

PolarDB-X supports most of the encryption functions and compression functions in MySQL 5.7. The following table describes these functions.

An encryption function or a compression function can return a value of the BYTE data type. If you need to store the result, we recommend that you use a BLOB column or a VARBINARY column. If you use the CHAR, VARCHAR, or TEXT data type to store a result value, the trailing spaces in the return value can be removed, and the value cannot be displayed after the character set is converted.

AES_DECRYPT(crypt_str, key_str [, init_vector])

Returns a plaintext based on the crypt_str ciphertext, the key_str key, and the init_vector initialization vector. init_vector is optional. For more information about the AES algorithm and how to use the AES algorithm, see AES_ENCRYPT.

AES_ENCRYPT(str, key_str [, init_vector])

Returns a ciphertext based on the str plaintext, the key_str key, and the init_vector initialization vector. init_vector is optional. The block_encryption_mode system variable determines the specific encryption mode for the AES algorithm. You must specify values for the block_encryption_mode system variable in the aes-keylen-mode format. keylen represents the bit length of a key. Valid values of keylen are 128, 192, and 256. mode represents the encryption mode. PolarDB-X supports the following encryption modes. For more information, see block_encryption_mode.

If an encryption mode requires the init_vector argument, the value of the init_vector argument must be 16 bytes or more and the excess content is truncated. If an encryption mode does not require the init_vector argument, the init_vector argument is ignored.

Sample code:

mysql> SET block_encryption_mode = 'aes-128-ofb';
mysql> SET @iv = RANDOM_BYTES(16);
mysql> SET @key = SHA2('secret key', 224);
mysql> set @crypto = AES_ENCRYPT('polardb-x', @key, @iv);
mysql> select @crypto;
+---------------------------+
| @crypto                   |
+---------------------------+
|  ß÷s,(ÿýÂåîA}ýO          |
+---------------------------+
mysql> 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.

Sample code:

mysql> select HEX(RANDOM_BYTES(16));
+----------------------------------+
| HEX(RANDOM_BYTES(16))            |
+----------------------------------+
| C83CF8A2499F407E15F34F6E32948CEA |
+----------------------------------+

MD5(str)

Returns a 128-bit MD5 checksum.

Sample code:

mysql> select MD5('polardb-x');
+----------------------------------+
| MD5('polardb-x')                 |
+----------------------------------+
| fa4900656bcd39dc90024e733fa4531f |
+----------------------------------+

SHA1(str), SHA(str)

Returns a 160-bit SHA-1 checksum. This function provides more secure encryption than the MD5() function.

Sample code:

mysql> select SHA1('polardb-x');
+------------------------------------------+
| SHA1('polardb-x')                        |
+------------------------------------------+
| a2e83af051f032b500f13c369976298208d821d1 |
+------------------------------------------+

SHA2(str, hash_length)

Calculates the SHA-2 family of hash functions. The hash_length argument specifies the desired bit length of the result. Valid values of the hash_length argument are 224, 256, 384, 512, and 0. In this case, the value 0 specifies the same length as the value 256. This function provides more secure encryption than the MD5() function and the SHA1() function.

Sample code:

mysql> select SHA2('polardb-x', 384);
+--------------------------------------------------------------------------------------------------+
| SHA2('polardb-x', 384)                                                                           |
+--------------------------------------------------------------------------------------------------+
| 20222037666be5234d9af3c391f9c3a1a3e39b910f3f8081c32d972acca890c818d6c70025ff6c6d4b648bd91d66a3fe |
+--------------------------------------------------------------------------------------------------+