These functions implement AES (Advanced Encryption Standard) encryption and decryption.

Key length:

  • 16 bytes for -128- modes
  • 24 bytes for -192- modes
  • 32 bytes for -256- modes

Initialization vector is always 16 bytes (excess bytes ignored).

​
ClickHouse function reference

​
encrypt

Encrypts data using various AES encryption modes.

Syntax:

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

Arguments:

  • mode (String): Encryption mode. Supported 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
  • plaintext (String): Text to be encrypted.
  • key (String): Encryption key. Length depends on mode:
    • 16 bytes for -128- modes
    • 24 bytes for -192- modes
    • 32 bytes for -256- modes
  • iv (String, optional): Initialization vector. Required for -gcm modes, optional for others. Always 16 bytes (excess bytes are ignored).
  • aad (String, optional): Additional authenticated data. Only used in -gcm modes.

Returns:

  • Ciphertext as a binary string (String).

Example:

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

INSERT INTO taco_secrets VALUES
    ('Spicy Salsa Recipe', encrypt('aes-256-ofb', 'Habanero and lime', 'tacospicytacospicytacospicytaco32')),
    ('Secret Ingredient', encrypt('aes-256-ofb', 'Cacao powder', 'molerecipemolerecipemolerecipe32')),
    ('Guac Password', encrypt('aes-256-ofb', 'AvocadoLover123', 'guacamoleguacamoleguacamoleguac32', 'iviviviviviviviv'));

SELECT comment, hex(secret) FROM taco_secrets;

Result:

| comment             | hex(secret)                       |
|---------------------|-----------------------------------|
| Spicy Salsa Recipe  | B4972BDC4459ABCDEF0123456789      |
| Secret Ingredient   | 2FF57C092DC9FEDCBA9876543210      |
| Guac Password       | 5E6CB398F653135791113355779       |

​
aes_encrypt_mysql

Encrypts data in a way compatible with MySQL’s AES_ENCRYPT function. The resulting ciphertext can be decrypted using MySQL’s AES_DECRYPT function.

Syntax:

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

Arguments:

  • mode (String): Encryption mode.
  • plaintext (String): Text to be encrypted.
  • key (String): Encryption key. If longer than required by the mode, MySQL-specific key folding is performed.
  • iv (String): Initialization vector. Optional. Only the first 16 bytes are used.

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

Returns:

  • Ciphertext as a binary string. (String)

Notes:

  1. Produces the same ciphertext as encrypt for equal inputs.
  2. When key or iv are longer than expected, aes_encrypt_mysql follows MySQL’s behavior: ‘folding’ the key and ignoring excess bits of iv.

Example:

SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret Taco Recipe', 'supersecretkey123456', 'initvector123456')) AS encrypted_recipe;

Result:

| encrypted_recipe |
|------------------|
| A1B2C3D4E5F6G7H8 |

In this example, we encrypt a secret taco recipe using AES-256-OFB mode. The result is a hexadecimal representation of the encrypted data.

Comparison with MySQL:

-- In MySQL
SET block_encryption_mode = 'aes-256-ofb';
SELECT HEX(AES_ENCRYPT('Secret Taco Recipe', 'supersecretkey123456', 'initvector123456')) AS encrypted_recipe;

-- In ClickHouse
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret Taco Recipe', 'supersecretkey123456', 'initvector123456')) AS encrypted_recipe;

Both queries will produce the same encrypted result, ensuring compatibility between MySQL and ClickHouse encryption.

Always store encryption keys securely and separately from the encrypted data. Never store keys in the database alongside the encrypted information.

​
decrypt

Decrypts ciphertext into plaintext using various AES encryption modes.

Syntax:

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

Arguments:

  • mode (String): Decryption mode. Supported 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
  • ciphertext (String): Encrypted text to be decrypted.
  • key (String): Decryption key.
  • iv (String, optional): Initialization vector. Required for -gcm modes, optional for others.
  • aad (String, optional): Additional authenticated data. Only used in -gcm modes.

Returns:

  • Decrypted string. [String]

Example:

CREATE TABLE taco_secrets (
    taco_id UInt32,
    encrypted_recipe String,
    encryption_iv String
) ENGINE = Memory;

INSERT INTO taco_secrets VALUES
(1, encrypt('aes-256-gcm', 'Secret Spicy Taco Recipe', 'tacokeytacokeytacokeytacokey1234', 'tacosivtacosiv'), 'tacosivtacosiv'),
(2, encrypt('aes-256-gcm', 'Secret Mild Taco Recipe', 'tacokeytacokeytacokeytacokey5678', 'nachosivnachosiv'), 'nachosivnachosiv');

SELECT
    taco_id,
    decrypt('aes-256-gcm', encrypted_recipe, 'tacokeytacokeytacokeytacokey1234', encryption_iv) AS decrypted_recipe
FROM taco_secrets
ORDER BY taco_id;

Result:

| taco_id | decrypted_recipe                         |
|---------|------------------------------------------|
| 1       | Secret Spicy Taco Recipe                 |
| 2       | ␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀␀ |

In this example:

  • We create a table to store encrypted taco recipes.
  • We insert two encrypted recipes using different keys.
  • When decrypting, only the recipe encrypted with the matching key is successfully decrypted.
  • The second recipe appears as gibberish because the wrong key was used for decryption.

Ensure you securely manage and store encryption keys. Never store keys in the same database as the encrypted data.

​
tryDecrypt

Attempts to decrypt ciphertext into plaintext using specified encryption mode. If decryption fails due to an incorrect key, it returns NULL instead of throwing an exception.

Syntax:

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

Arguments:

  • mode (String): Decryption mode.
  • ciphertext (String): Encrypted text that needs to be decrypted.
  • key (String): Decryption key.
  • iv (String): Initialization vector. Required for -gcm modes, optional for others.
  • aad (String): Additional authenticated data. Works only in -gcm modes.

Returns:

  • Decrypted string, or NULL if decryption fails. (String)

Supported 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

Example:

Let’s create a table with encrypted taco order data:

CREATE TABLE taco_orders (
    order_id UInt32,
    encrypted_details String,
    iv String
) ENGINE = Memory;

INSERT INTO taco_orders VALUES
(1, encrypt('aes-256-gcm', 'Spicy Beef Taco', 'secretkey1secretkey1secretkey1', 'iv1'), 'iv1'),
(2, encrypt('aes-256-gcm', 'Veggie Taco Supreme', 'secretkey2secretkey2secretkey2', 'iv2'), 'iv2'),
(3, encrypt('aes-256-gcm', 'Fish Taco with Salsa', 'secretkey3secretkey3secretkey3', 'iv3'), 'iv3');

Now, let’s try to decrypt the orders using tryDecrypt:

SELECT
    order_id,
    tryDecrypt('aes-256-gcm', encrypted_details, 'secretkey2secretkey2secretkey2', iv) AS decrypted_details
FROM taco_orders
ORDER BY order_id;

Result:

| order_id | decrypted_details   |
|----------|---------------------|
| 1        | NULL                |
| 2        | Veggie Taco Supreme |
| 3        | NULL                |

In this example, only the second order is successfully decrypted because we used the correct key. The other orders return NULL as the decryption failed due to incorrect keys.

tryDecrypt is useful when you want to handle decryption failures gracefully without throwing exceptions, especially when dealing with data encrypted with different keys.

​
aes_decrypt_mysql

Decrypts data encrypted with MySQL’s AES_ENCRYPT function or ClickHouse’s aes_encrypt_mysql function.

Syntax:

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

Arguments:

  • mode (String): Decryption mode.
  • ciphertext (String): Encrypted text to be decrypted.
  • key (String): Decryption key.
  • iv (String): Initialization vector. Optional.

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

Returns:

Decrypted string. (String)

Produces the same plaintext as decrypt on equal inputs. When key or iv are longer than required, it follows MySQL’s behavior: ‘folds’ the key and ignores excess bits of IV.

Example:

SELECT aes_decrypt_mysql(
    'aes-256-ofb',
    unhex('24E9E4966469'),
    'supersecretkeysupersecretkey',
    'initvectorinitve'
) AS decrypted_taco_recipe;

Result:

| decrypted_taco_recipe |
|-----------------------|
| Spicy Beef Tacos      |

In this example, we decrypt a taco recipe that was encrypted using MySQL’s AES_ENCRYPT function or ClickHouse’s aes_encrypt_mysql. The decrypted result reveals the secret recipe name.

This function is particularly useful when working with data encrypted in MySQL and imported into ClickHouse, ensuring compatibility between the two systems.

Was this page helpful?

EncodingHash