Encryption functions and compression functions
This topic describes the encryption functions and compression functions that are supported by PolarDB-X.
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.
| Function | Description |
|---|---|
| AES_DECRYPT | Uses the Advanced Encryption Standard (AES) algorithm to decrypt data. |
| AES_ENCRYPT | Uses the AES algorithm to encrypt data. |
| RANDOM_BYTES | Returns a random byte vector. |
| MD5 | Returns a 128-bit Message Digest 5 (MD5) checksum. |
| SHA1, SHA | Returns a 160-bit Secure Hash Algorithm 1 (SHA-1) checksum. |
| SHA2 | Returns an SHA-2 checksum. |
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.
| Encryption mode | Is an initialization vector required |
|---|---|
| ECB | No |
| CBC | Yes |
| CFB1 | Yes |
| CFB8 | Yes |
| CFB128 | Yes |
| OFB | Yes |
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 |
+--------------------------------------------------------------------------------------------------+