Encryption functions¶

These following functions implement encryption and decryption of data with the AES algorithm.

Key length depends on encryption mode. It is 16, 24, and 32 bytes long for -128-, -196-, and -256- modes respectively.

Initialization vector length is always 16 bytes. Bytes in excess of 16 are ignored.

encrypt¶

This function encrypts data using these 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])

Arguments¶

mode: Encryption mode. String.
plaintext: Text that need to be encrypted. String.
key: Encryption key. String.
iv: Initialization vector. Required for -gcm modes, optional for others. String.
aad: Additional authenticated data. It isn't encrypted, but it affects decryption. Works only in -gcm modes, for others would throw an exception. String.

Returned value¶

Ciphertext binary string. String.

Examples¶

Mock some data (please avoid storing the keys/ivs in the database as this undermines the whole concept of encryption), also storing 'hints' is unsafe too and used only for illustrative purposes:

Query:

SELECT comment, hex(secret) FROM
(
  select c1 as comment, c2 as secret from 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'))
  )
)

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 with -gcm:

Query:

SELECT comment, hex(secret) FROM
(
  select c1 as comment, c2 as secret from 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')),
    ('aes-256-gcm', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')),
    ('aes-256-gcm with AAD', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad'))
  )
)
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 and resulting ciphertext can be decrypted with AES_DECRYPT function.

Will produce the same ciphertext as encrypt on equal inputs. But when key or iv are longer than they should normally be, aes_encrypt_mysql will stick to what MySQL's aes_encrypt does: 'fold' key and ignore excess bits 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])

Arguments¶

mode: Encryption mode. String.
plaintext: Text that needs to be encrypted. String.
key: Encryption key. If key is longer than required by mode, MySQL-specific key folding is performed. String.
iv: Initialization vector. Optional, only first 16 bytes are taken into account String.

Returned value¶

Ciphertext binary string. String.

Examples¶

Given equal input encrypt and aes_encrypt_mysql produce the same ciphertext:

Query:

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

Result:

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

But encrypt fails when key or iv is longer than expected:

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').

While aes_encrypt_mysql produces MySQL-compatible output:

Query:

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

Result:

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

Notice how supplying even longer IV produces the same result

Query:

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

Result:

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

Which is binary equal to what MySQL produces on same inputs:

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 decrypts ciphertext into a plaintext using these 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¶

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

Arguments¶

mode: Decryption mode. String.
ciphertext: Encrypted text that needs to be decrypted. String.
key: Decryption key. String.
iv: Initialization vector. Required for -gcm modes, Optional for others. String.
aad: Additional authenticated data. Won't decrypt if this value is incorrect. Works only in -gcm modes, for others would throw an exception. String.

Returned value¶

Decrypted String. String.

tryDecrypt¶

Similar to decrypt, but returns NULL if decryption fails because of using the wrong key.

Examples¶

Create a table where user_id is the unique user id, encrypted is an encrypted string field, iv is an initial vector for decrypt/encrypt. Assume that users know their id and the key to decrypt the encrypted field:

Query:

SELECT
    dt,
    user_id,
    tryDecrypt('aes-256-gcm', encrypted, 'keykeykeykeykeykeykeykeykeykey02', iv) AS value
FROM (
    select c1::DateTime as dt, c2::UInt32 as user_id, c3::String as encrypted, c4::String as iv from 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')
    )
)
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 decrypts data encrypted with AES_ENCRYPT function.

Will produce same plaintext as decrypt on equal inputs. But when key or iv are longer than they should normally be, aes_decrypt_mysql will stick to what MySQL's aes_decrypt does: 'fold' key and ignore excess bits of 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])

Arguments¶

mode: Decryption mode. String.
ciphertext: Encrypted text that needs to be decrypted. String.
key: Decryption key. String.
iv: Initialization vector. Optional. String.

Returned value¶

Decrypted String. String.

Examples¶

Decrypt data you've 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    │
└───────────┘

Data types

Table engines

Functions

Encryption functions¶

encrypt¶

Syntax¶

Arguments¶

Returned value¶

Examples¶

aes_encrypt_mysql¶

Syntax¶

Arguments¶

Returned value¶

Examples¶

decrypt¶

Syntax¶

Arguments¶

Returned value¶

tryDecrypt¶

Examples¶

aes_decrypt_mysql¶

Syntax¶

Arguments¶

Returned value¶

Examples¶