These functions use the AES (Advanced Encryption Standard) algorithm to implement data encryption and decryption.
The key length depends on the encryption mode. For the -128-, -196-, and -256- modes, the key lengths are 16, 24, and 32 bytes, respectively.
The initialization vector length is always 16 bytes (any bytes exceeding 16 will be ignored).

encrypt #

This function encrypts data using the following modes:

• aes-128-ecb、aes-192-ecb、aes-256-ecb
• aes-128-cbc、aes-192-cbc、aes-256-cbc
• aes-128-ofb、aes-192-ofb、aes-256-ofb
• aes-128-gcm、aes-192-gcm、aes-256-gcm
• aes-128-ctr、aes-192-ctr、aes-256-ctr

Syntax

encrypt('mode', 'plaintext', 'key' [, iv, aad])

Parameters

• mode— Encryption mode. String.
• plaintext— The text to be encrypted. String.
• key— Encryption key. String.
• iv— Initialization vector. Required for -gcm mode; optional for other modes. String.
• aad— Additional authenticated data. This is not encrypted, but it affects decryption. Valid only in -gcm mode; using it in other modes will raise an exception. String.

Return value

• Binary ciphertext string. String.

Example
Create table:

CREATE TABLE encryption_test 
( 
    `comment` String, 
    `secret` String 
) 
ENGINE = Memory;

Insert some data (avoid storing keys/ivs in the database, as this defeats the entire encryption concept; storing "tips" is also not secure and is for demonstration purposes only):

--Insert 
INSERT INTO encryption_test VALUES('aes-256-ofb no IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212')), 
('aes-256-ofb no IV, different key', encrypt('aes-256-ofb', 'Secret', 'keykeykeykeykeykeykeykeykeykeyke')), 
('aes-256-ofb with IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')), 
('aes-256-cbc no IV', encrypt('aes-256-cbc', 'Secret', '12345678910121314151617181920212'));

--Query 
SELECT comment, hex(secret) FROM encryption_test;

Result:

┌─comment──────────────────────────┬─hex(secret)──────────────────────┐ 
│ aes-256-ofb no IV                │ B4972BDC4459                     │ 
│ aes-256-ofb no IV, different key │ 2FF57C092DC9                     │ 
│ aes-256-ofb with IV              │ 5E6CB398F653                     │ 
│ aes-256-cbc no IV                │ 1BC0629A92450D9E73A00E7D02CF4142 │ 
└──────────────────────────────────┴──────────────────────────────────┘

Example: -gcm:

--Insert 
INSERT INTO encryption_test VALUES('aes-256-gcm', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')),  
('aes-256-gcm with AAD', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad')); 

SELECT comment, hex(secret) FROM encryption_test WHERE comment LIKE '%gcm%';

Result:

┌─comment──────────────┬─hex(secret)──────────────────────────────────┐ 
│ aes-256-gcm          │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │ 
│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │ 
└──────────────────────┴──────────────────────────────────────────────┘

aes_encrypt_mysql #

Compatible with MySQL encryption; the generated ciphertext can be decrypted using the AES_DECRYPT function.
With the same input, it will generate the same ciphertext as encrypt. However, when key or iv is longer than the normal length, aes_encrypt_mysql follows MySQL's aes_encrypt behavior: it performs key folding on key and ignores extra bytes of iv.
Supported encryption modes:

• aes-128-ecb、aes-192-ecb、aes-256-ecb
• aes-128-cbc、aes-192-cbc、aes-256-cbc
• aes-128-ofb、aes-192-ofb、aes-256-ofb

Syntax

aes_encrypt_mysql('mode', 'plaintext', 'key' [, iv])

Parameters

• mode— Encryption mode. String.
• plaintext— The text to be encrypted. String.
• key— Encryption key. If the key length exceeds the length required by the mode, MySQL-specific key folding is performed. String.
• iv— Initialization vector. Optional; only the first 16 bytes of the string are considered.

Return value

• Binary ciphertext string. String.

Example
Given the same input, encrypt and aes_encrypt_mysql produce the same ciphertext:

SELECT encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') = aes_encrypt_mysql('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') AS ciphertexts_equal;

Result:

┌─ciphertexts_equal─┐ 
│                 1 │ 
└───────────────────┘

However, when encrypt is given a keyiv longer than expected, it fails:

--Query 
SELECT encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123');

Result:

Received exception from server (version 22.6.1): 
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: Invalid key size: 33 expected 32: While processing encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123').

To generate MySQL-compatible output with aes_encrypt_mysql:

--Query 
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext;

Result:

┌─ciphertext───┐ 
│ 24E9E4966469 │ 
└──────────────┘

Caution: Even if a longer IV is supplied, the result is the same

--Query 
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertext

Result:

┌─ciphertext───┐ 
│ 24E9E4966469 │ 
└──────────────┘

The binary result is the same as that generated by MySQL for the same input:

mysql> SET  block_encryption_mode='aes-256-ofb'; 
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT aes_encrypt('Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456') as ciphertext; 
+------------------------+ 
| ciphertext             | 
+------------------------+ 
| 0x24E9E4966469         | 
+------------------------+ 
1 row in set (0.00 sec)

decrypt #

This function uses the following modes to decrypt ciphertext into plaintext:

• aes-128-ecb、aes-192-ecb、aes-256-ecb
• aes-128-cbc、aes-192-cbc、aes-256-cbc
• aes-128-ofb、aes-192-ofb、aes-256-ofb
• aes-128-gcm、aes-192-gcm、aes-256-gcm
• aes-128-ctr、aes-192-ctr、aes-256-ctr

Syntax

decrypt('mode', 'ciphertext', 'key' [, iv, aad])

Parameters

• mode — decryption mode. String.
• ciphertext — ciphertext to be decrypted. String.
• key — decryption key. String.
• iv — initialization vector. Required for -gcm mode; optional for other modes. String.
• aad — additional authentication data. If this value is incorrect, decryption will not occur. Valid only in -gcm mode; an exception will be raised in other modes. String.

Return value

• Decrypted string. String.

Example
Reuse the existing encryption table.

SELECT comment, hex(secret) FROM encryption_test;

Result:

┌─comment──────────────┬─hex(secret)──────────────────────────────────┐ 
│ aes-256-gcm          │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │ 
│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │ 
└──────────────────────┴──────────────────────────────────────────────┘ 
┌─comment──────────────────────────┬─hex(secret)──────────────────────┐ 
│ aes-256-ofb no IV                │ B4972BDC4459                     │ 
│ aes-256-ofb no IV, different key │ 2FF57C092DC9                     │ 
│ aes-256-ofb with IV              │ 5E6CB398F653                     │ 
│ aes-256-cbc no IV                │ 1BC0629A92450D9E73A00E7D02CF4142 │ 
└──────────────────────────────────┴──────────────────────────────────┘

Now let us try to decrypt all data.

--Query 
SELECT comment, decrypt('aes-256-cfb128', secret, '12345678910121314151617181920212') as plaintext FROM encryption_test

Result:

┌─comment──────────────┬─plaintext──┐ 
│ aes-256-gcm          │ OQ�E 
                             �t�7T�����   │ 
│ aes-256-gcm with AAD │ OQ�E 
                             ���si����;�o�� │ 
└──────────────────────┴────────────┘ 
┌─comment──────────────────────────┬─plaintext─┐ 
│ aes-256-ofb no IV                │ Secret    │ 
│ aes-256-ofb no IV, different key │ �4� 
                                        �         │ 
│ aes-256-ofb with IV              │ ���6�~        │ 
 │aes-256-cbc no IV                │ �2*4�h3c�4w��@ 
└──────────────────────────────────┴───────────┘

Caution: Only part of the data is decrypted correctly; the rest is garbled because the mode, key, or iv were different during encryption.

tryDecrypt #

Similar to decrypt, but returns NULL if decryption fails due to an incorrect key.
Example
Let us create a table where user_id is the unique user ID, encrypted is the encrypted string field, and iv is the initialization vector for decryption/encryption. Assume the user knows their ID and the key to decrypt the encrypted field:

CREATE TABLE decrypt_null ( 
  dt DateTime, 
  user_id UInt32, 
  encrypted String, 
  iv String 
) ENGINE = Memory;

Insert some data:

INSERT INTO decrypt_null VALUES 
    ('2022-08-02 00:00:00', 1, encrypt('aes-256-gcm', 'value1', 'keykeykeykeykeykeykeykeykeykey01', 'iv1'), 'iv1'), 
    ('2022-09-02 00:00:00', 2, encrypt('aes-256-gcm', 'value2', 'keykeykeykeykeykeykeykeykeykey02', 'iv2'), 'iv2'), 
    ('2022-09-02 00:00:01', 3, encrypt('aes-256-gcm', 'value3', 'keykeykeykeykeykeykeykeykeykey03', 'iv3'), 'iv3');

Query

SELECT 
    dt, 
    user_id, 
    tryDecrypt('aes-256-gcm', encrypted, 'keykeykeykeykeykeykeykeykeykey02', iv) AS value 
FROM decrypt_null 
ORDER BY user_id ASC

Result:

┌──────────────────dt─┬─user_id─┬─value──┐ 
│ 2022-08-02 00:00:00 │       1 │ ᴺᵁᴸᴸ   │ 
│ 2022-09-02 00:00:00 │       2 │ value2 │ 
│ 2022-09-02 00:00:01 │       3 │ ᴺᵁᴸᴸ   │ 
└─────────────────────┴─────────┴────────┘

aes_decrypt_mysql #

Compatible with MySQL encryption and decryption for data encrypted using the AES_ENCRYPT function.
decrypt will produce the same plaintext for identical input. However, when the key or iv are longer than the normal length, aes_decrypt_mysql follows MySQL's aes_decrypt approach: it 'folds' the key and ignores extra digits in the IV.
Supported decryption modes:

• aes-128-ecb、aes-192-ecb、aes-256-ecb
• aes-128-cbc、aes-192-cbc、aes-256-cbc
• aes-128-cfb128
• aes-128-ofb、aes-192-ofb、aes-256-ofb

Syntax

aes_decrypt_mysql('mode', 'ciphertext', 'key' [, iv])

Parameters

• mode — decryption mode. String.
• ciphertext — ciphertext to be decrypted. String.
• key — decryption key. String.
• iv — initialization vector. Optional. String.

Return value

• Decrypted string. String.

Example
Let us decrypt data previously encrypted with MySQL:

mysql> SET  block_encryption_mode='aes-256-ofb'; 
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT aes_encrypt('Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456') as ciphertext; 
+------------------------+ 
| ciphertext             | 
+------------------------+ 
| 0x24E9E4966469         | 
+------------------------+ 
1 row in set (0.00 sec)

Query

SELECT aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext

Result:

┌─plaintext─┐ 
│ Secret    │ 
└───────────┘