Doris provides the following built-in encryption and masking functions. For detailed usage, please refer to the SQL manual.

AES_ENCRYPT

An AES encryption function.

This function behaves the same as the AES_ENCRYPT function in MySQL. It uses the AES_128_ECB algorithm by default, with PKCS7 padding mode. Encryption is performed using the OpenSSL library as the underlying mechanism. Reference: <https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt>

1. select to_base64(aes_encrypt('text','F3229A0B371ED2D9441B830D21A390C3'));

3. +--------------------------------+
4. | to_base64(aes_encrypt('text')) |
5. +--------------------------------+
6. | wr2JEDVXzL9+2XtRhgIloA==       |
7. +--------------------------------+
8. 1 row in set (0.01 sec)

AES_DECRYPT

An AES decryption function. This function behaves the same as the AES_DECRYPT function in MySQL. It uses the AES_128_ECB algorithm by default, with PKCS7 padding mode. Decryption is performed using the OpenSSL library as the underlying mechanism.

1. select aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='),'F3229A0B371ED2D9441B830D21A390C3');
2. +------------------------------------------------------+
3. | aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA==')) |
4. +------------------------------------------------------+
5. | text                                                 |
6. +------------------------------------------------------+
7. 1 row in set (0.01 sec)

MD5

Calculates the MD5 128-bit hash.

1. MySQL [(none)]> select md5("abc");
2. +----------------------------------+
3. | md5('abc')                       |
4. +----------------------------------+
5. | 900150983cd24fb0d6963f7d28e17f72 |
6. +----------------------------------+
7. 1 row in set (0.013 sec)

MD5SUM

Calculates the MD5 128-bit hash for multiple strings.

1. MySQL > select md5("abcd");
2. +----------------------------------+
3. | md5('abcd')                      |
4. +----------------------------------+
5. | e2fc714c4727ee9395f324cd2e7f331f |
6. +----------------------------------+
7. 1 row in set (0.011 sec)

9. MySQL > select md5sum("ab","cd");
10. +----------------------------------+
11. | md5sum('ab', 'cd')               |
12. +----------------------------------+
13. | e2fc714c4727ee9395f324cd2e7f331f |
14. +----------------------------------+
15. 1 row in set (0.008 sec)

SM4_ENCRYPT

SM4 encryption function.

1. MySQL > select TO_BASE64(SM4_ENCRYPT('text','F3229A0B371ED2D9441B830D21A390C3'));
2. +--------------------------------+
3. | to_base64(sm4_encrypt('text')) |
4. +--------------------------------+
5. | aDjwRflBrDjhBZIOFNw3Tg==       |
6. +--------------------------------+
7. 1 row in set (0.010 sec)

9. MySQL > set block_encryption_mode="SM4_128_CBC";
10. Query OK, 0 rows affected (0.001 sec)

12. MySQL > select to_base64(SM4_ENCRYPT('text','F3229A0B371ED2D9441B830D21A390C3', '0123456789'));
13. +----------------------------------------------------------------------------------+
14. | to_base64(sm4_encrypt('text', 'F3229A0B371ED2D9441B830D21A390C3', '0123456789')) |
15. +----------------------------------------------------------------------------------+
16. | G7yqOKfEyxdagboz6Qf01A==                                                         |
17. +----------------------------------------------------------------------------------+
18. 1 row in set (0.014 sec)

SM3

Calculates the SM3 256-bit hash for multiple strings.

1. MySQL > select sm3("abcd");
2. +------------------------------------------------------------------+
3. | sm3('abcd')                                                      |
4. +------------------------------------------------------------------+
5. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
6. +------------------------------------------------------------------+
7. 1 row in set (0.009 sec)

SM3SUM

Calculates the SM3 256-bit hash for multiple strings.

1. MySQL > select sm3("abcd");
2. +------------------------------------------------------------------+
3. | sm3('abcd')                                                      |
4. +------------------------------------------------------------------+
5. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
6. +------------------------------------------------------------------+
7. 1 row in set (0.009 sec)

9. MySQL > select sm3sum("ab","cd");
10. +------------------------------------------------------------------+
11. | sm3sum('ab', 'cd')                                               |
12. +------------------------------------------------------------------+
13. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
14. +------------------------------------------------------------------+
15. 1 row in set (0.009 sec)

SHA

Calculates the SHA1 hash using the SHA1 algorithm.

SHA2

Calculates the SHA2 hash using SHA2 algorithm.

1. mysql> select sha2('abc', 224);
2. +----------------------------------------------------------+
3. | sha2('abc', 224)                                         |
4. +----------------------------------------------------------+
5. | 23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7 |
6. +----------------------------------------------------------+
7. 1 row in set (0.13 sec)

9. mysql> select sha2('abc', 384);
10. +--------------------------------------------------------------------------------------------------+
11. | sha2('abc', 384)                                                                                 |
12. +--------------------------------------------------------------------------------------------------+
13. | cb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7 |
14. +--------------------------------------------------------------------------------------------------+
15. 1 row in set (0.13 sec)

17. mysql> select sha2(NULL, 512);
18. +-----------------+
19. | sha2(NULL, 512) |
20. +-----------------+
21. | NULL            |
22. +-----------------+
23. 1 row in set (0.09 sec)

DIGITAL_MASKING

An alias function with the original function being CONCAT(LEFT(id,3),'****',RIGHT(id,4)). Performs data masking on the input digital_number and returns the masked result.

The digital_number is of type BIGINT.

1. mysql select digital_masking(13812345678);
2. +------------------------------+
3. | digital_masking(13812345678) |
4. +------------------------------+
5. | 138****5678                  |
6. +------------------------------+