ClickHouse Encryption Functions: encryptAES, decryptAES, MySQL Compatibility

ClickHouse ships a family of SQL functions that encrypt and decrypt individual values with AES (Advanced Encryption Standard): encrypt, decrypt, aes_encrypt_mysql, aes_decrypt_mysql, and tryDecrypt. These operate on column values at query time, which is a different concern from encrypting the files MergeTree writes to disk.

If your goal is to protect data files on disk transparently, see ClickHouse Disk Encryption at Rest. This guide covers the opposite end: application-level, per-value encryption you control inside your SQL, including how to interoperate with data MySQL's AES_ENCRYPT produced.

At-rest vs. query-level encryption

These two mechanisms solve different problems and are often used together:

Disk encryption protects against someone walking off with a disk or a backup. It does not stop a user who can run SELECT — the server decrypts transparently. The encrypt/decrypt functions add a second layer: ciphertext stays encrypted even in query results unless the caller supplies the right key.

The encrypt and decrypt functions

encrypt(mode, plaintext, key[, iv, aad])
decrypt(mode, ciphertext, key[, iv, aad])

• mode — a string like aes-256-gcm, following the pattern aes-<keylen>-<mode>.
• plaintext / ciphertext — the value to encrypt or decrypt (String or FixedString).
• key — the encryption key. Key length must match the mode: 16 bytes for -128-, 24 bytes for -192-, 32 bytes for -256-.
• iv — the initialization vector. Required for every mode except ECB.
• aad — additional authenticated data, only valid in -gcm modes.

The supported modes are:

Keys and IVs are raw byte strings. In practice you'll generate them as hex and decode with unhex(), and wrap output in hex() so binary ciphertext is readable:

SELECT hex(encrypt(
    'aes-256-gcm',
    'My dirty little secret: I ate all the cookies from the jar!',
    unhex('41204a63d38dcb7432c9265ba03e62a872e395057b32d4a11833311a394aaea8'),
    unhex('c5827b8d4e4cea195ee12d62c18d379c')
)) AS ciphertext;

Decryption mirrors it — same mode, same key, same IV:

SELECT decrypt(
    'aes-256-gcm',
    unhex('74BC0316...'),  -- ciphertext bytes
    unhex('41204a63d38dcb7432c9265ba03e62a872e395057b32d4a11833311a394aaea8'),
    unhex('c5827b8d4e4cea195ee12d62c18d379c')
) AS plaintext;

Choosing a mode

• aes-256-gcm is the right default for most use cases. GCM is an AEAD (authenticated encryption) mode: it appends an authentication tag to the ciphertext per RFC 5116, so decryption fails if the data or the optional aad was tampered with. Never reuse the same IV with the same key in GCM — it breaks the security guarantee.
• ECB has no IV and no chaining, so identical plaintext always yields identical ciphertext. That leaks equality and is generally discouraged — its main reason to exist in ClickHouse is MySQL compatibility (see below).
• CTR / OFB / CFB are streaming modes (no padding); CBC is a block mode. Use them when you must match an existing scheme, but prefer GCM when you control both sides because it also detects tampering.

The aad argument is GCM-only. If you pass it to a non-GCM mode, ClickHouse throws. The aad is authenticated but not encrypted, and decryption fails if the supplied aad doesn't match what was used to encrypt.

Encrypting a real column

You can encrypt an existing column in place with a mutation, then decrypt on read. Store the ciphertext as binary (String/FixedString), not hex-encoded text, in production:

ALTER TABLE customers
UPDATE name = encrypt(
    'aes-256-gcm',
    name,
    unhex('5eb83a2d768889a7984fc3aea79f1b75f274825c426641697b20c547f9765c35'),
    unhex('8716176c655163785fa0dc9e96b8eb06')
)
WHERE 1;

Reading it back:

SELECT
    id,
    decrypt(
        'aes-256-gcm',
        name,
        unhex('5eb83a2d768889a7984fc3aea79f1b75f274825c426641697b20c547f9765c35'),
        unhex('8716176c655163785fa0dc9e96b8eb06')
    ) AS name
FROM customers
LIMIT 3;

Note the IV is reused across all rows above for illustration. With GCM that is unsafe — see Best Practices for per-row IV handling.

Handling failures with tryDecrypt

decrypt throws an exception when the GCM authentication tag fails, the ciphertext is malformed, or other structural errors occur. In non-GCM modes, a wrong key or IV may instead produce silent garbage output rather than an exception. That aborts the whole query. tryDecrypt is the safe variant:

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

It returns Nullable(String) — the plaintext on success, or NULL on failure — instead of raising an error. This is useful when a column may contain a mix of values encrypted under different keys (e.g. during a key rotation), or when some rows may be corrupt:

SELECT
    id,
    coalesce(
        tryDecrypt('aes-256-gcm', name, unhex(:new_key), unhex(:iv)),
        tryDecrypt('aes-256-gcm', name, unhex(:old_key), unhex(:iv))
    ) AS name
FROM customers;

If decryption errors are surfacing in production, see ClickHouse DATA_ENCRYPTION error for diagnosing the underlying cause.

MySQL compatibility: aes_encrypt_mysql / aes_decrypt_mysql

MySQL's AES_ENCRYPT() defaults to aes-128-ecb and does two non-standard things when the key or IV is the "wrong" length: it folds an over-long key (XORing the excess bytes back over the start) and ignores IV bytes beyond the first 16. ClickHouse's standard encrypt rejects mismatched lengths, so it cannot read that data directly.

aes_encrypt_mysql and aes_decrypt_mysql replicate MySQL's behavior exactly:

aes_encrypt_mysql(mode, plaintext, key[, iv])
aes_decrypt_mysql(mode, ciphertext, key[, iv])

They support the ECB, CBC, and OFB mode families (not GCM/CTR); aes_decrypt_mysql additionally supports aes-128-cfb128, while aes_encrypt_mysql does not. On equal, correctly-sized inputs they produce the same result as encrypt/decrypt; the difference only shows up with over-long keys or IVs, where they "fold" the key and truncate the IV the way MySQL does.

Decrypting a column that MySQL encrypted — for example, read live over the mysql() table function — looks like this:

SELECT
    id,
    aes_decrypt_mysql(
        'aes-128-ecb',
        name,
        'this is an encryption key used in mysql to encrypt name column',
        'this is an iv used in mysql'
    ) AS name
FROM mysql('172.17.0.3', 'online_store', 'customers', 'root', 'password')
LIMIT 3;

Use aes_*_mysql only when you need MySQL interoperability. For ClickHouse-native data, prefer encrypt/decrypt with a GCM mode.

Best Practices

1. Prefer aes-256-gcm unless an external system forces another scheme. It authenticates the ciphertext as well as encrypting it.

2. Never reuse a (key, IV) pair in GCM. Derive a unique IV per row — for instance from a row identifier or a random value stored alongside the ciphertext. ClickHouse always uses a 16-byte IV (bytes beyond 16 are ignored). All examples in this guide use 16-byte IVs accordingly.

3. Keep keys out of query logs. Literal keys passed inline appear in system.query_log. Pass keys as parameters, restrict query_log access, or front the encryption with a service that injects keys rather than embedding them in ad-hoc SQL.

4. Store ciphertext as binary, not hex. hex() / unhex() are display and transport conveniences. Storing hex doubles the column size. Use String or a fixed-width FixedString for the raw bytes.

5. Don't encrypt columns you need to filter or join on. Encrypted values are opaque — you can't use them in WHERE, GROUP BY, or as a primary key efficiently. ECB makes equality lookups possible but at a real security cost. Encrypt only the fields that must stay secret at rest in query results.

6. Combine layers. Disk encryption protects backups and stolen media; encrypt/decrypt protects against over-broad query access. Sensitive PII often warrants both.

Common Issues

• Wrong key length for the mode. aes-256-* needs a 32-byte key; supplying 16 bytes throws. Generate keys at the exact length the mode requires.
• Passing aad to a non-GCM mode. aad is GCM-only and raises an exception elsewhere.
• Missing IV on CBC/CTR/OFB/CFB/GCM, or supplying one to ECB. ECB takes no IV; every other mode requires one.
• decrypt aborting a whole query on one bad row. Switch to tryDecrypt and handle the resulting NULLs.
• MySQL data won't decrypt with decrypt. MySQL's key folding means you must use aes_decrypt_mysql with MySQL's default aes-128-ecb.
• GCM tag mismatch after re-encrypting with a reused IV. Treat IV reuse as a correctness and security bug, not just a warning.

How Pulse helps

Field-level encryption is easy to get subtly wrong — a reused GCM IV, a key that leaked into query_log, or a column you can no longer filter on because it's now opaque. Pulse (pulse.support) monitors ClickHouse clusters and surfaces exactly these operational issues: it flags decryption errors appearing in logs, watches mutation progress when you encrypt a column in place with ALTER TABLE ... UPDATE, and helps you correlate query-log exposure with access policy. When an encryption-related error spikes, Pulse points you at the query and the node so you can act before it cascades. For teams running ClickHouse with compliance requirements, that observability layer turns a fragile manual setup into something you can actually operate.

Frequently Asked Questions

Q: What's the difference between encrypt/decrypt and disk encryption at rest?

encrypt/decrypt operate on individual column values at query time, with keys you pass into the SQL call — ciphertext stays encrypted in query results unless the caller has the key. Disk encryption at rest transparently encrypts whole MergeTree parts on disk with server-configured keys, but any user who can query the server sees plaintext. See ClickHouse Disk Encryption at Rest.

Q: Which AES mode should I use?

aes-256-gcm for most cases — it authenticates the data (detects tampering) in addition to encrypting it. Use CBC/CTR/OFB/CFB only to match an external scheme, and ECB essentially only for MySQL compatibility, since it leaks plaintext equality.

Q: How long does the key need to be?

It depends on the mode: 16 bytes for -128-, 24 bytes for -192-, and 32 bytes for -256-. A mismatch throws an error. The IV is required for all modes except ECB; for GCM, ClickHouse uses 16 bytes (bytes beyond 16 are ignored).

Q: How do I decrypt data that MySQL's AES_ENCRYPT produced?

Use aes_decrypt_mysql with the mode MySQL used (aes-128-ecb by default). It replicates MySQL's key folding and IV truncation, which the standard decrypt function does not. Plain decrypt will fail on MySQL-encrypted data with non-standard key lengths.

Q: How do I avoid a single bad row aborting my whole SELECT?

Use tryDecrypt, which returns NULL instead of throwing when the key is wrong or the ciphertext is invalid. It's also the clean way to handle a column that contains values encrypted under more than one key during rotation.

Q: Can I filter or group by an encrypted column?

Not efficiently. Encrypted output is opaque, so it can't be used for range filters, joins, or as a meaningful primary key. ECB allows exact-equality matching because identical plaintext yields identical ciphertext, but that property is itself a security weakness. Encrypt only the fields that genuinely need to stay secret.