Encryption functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/encryption-functions.md)

These functions implement encryption and decryption of data with AES (Advanced Encryption Standard) algorithm.

The key length depends on the encryption mode: `16`, `24`, and `32` bytes long for `-128-`, `-196-`, and `-256-` modes respectively.

The initialization vector length is always 16 bytes (bytes in excess of 16 are ignored).

## HMAC[​](#HMAC "Direct link to HMAC")

Introduced in: v25.12.0

Computes the HMAC (Hash-based Message Authentication Code) for the given message using the specified hash algorithm and secret key.

Supported hash algorithms:

- blake2b512
- blake2s256
- md4
- md5
- md5-sha1
- mdc2
- ripemd (aliases: RIPEMD160, ripemd)
- ripemd160
- rmd160 (aliases: RIPEMD160, rmd160)
- sha1
- sha224
- sha256
- sha3-224
- sha3-256
- sha3-384
- sha3-512
- sha384
- sha512
- sha512-224
- sha512-256
- shake128
- shake256
- sm3
- ssl3-md5 (aliases: MD5, ssl3-md5)
- ssl3-sha1 (aliases: SHA1, ssl3-sha1)
- whirlpool

**Syntax**

```
HMAC(mode, message, key)
```

**Arguments**

- `mode` — Hash algorithm name (case-insensitive). Supported: md5, sha1, sha224, sha256, sha384, sha512. [`String`](/docs/sql-reference/data-types/string)
- `message` — Message to be authenticated. [`String`](/docs/sql-reference/data-types/string)
- `key` — Secret key for HMAC. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a binary string containing the HMAC digest. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic HMAC-SHA256**

```
SELECT hex(HMAC('sha256', 'The quick brown fox jumps over the lazy dog', 'secret_key'));
```

```
┌─hex(HMAC('sha256', 'The quick brown fox jumps over the lazy dog', 'secret_key'))─┐
│ 31FD15FA0F61FD40DC09D919D4AA5B4141A0B27C1D51E74A6789A890AAAA187C                 │
└──────────────────────────────────────────────────────────────────────────────────┘
```

**Different hash algorithms**

```
SELECT
    hex(HMAC('md5', 'message', 'key')) AS hmac_md5,
    hex(HMAC('sha1', 'message', 'key')) AS hmac_sha1,
    hex(HMAC('sha256', 'message', 'key')) AS hmac_sha256;
```

```
┌─hmac_md5─────────────────────────┬─hmac_sha1────────────────────────────────┬─hmac_sha256──────────────────────────────────────────────────────┐
│ 4E4748E62B463521F6775FBF921234B5 │ 2088DF74D5F2146B48146CAF4965377E9D0BE3A4 │ 6E9EF29B75FFFC5B7ABAE527D58FDADB2FE42E7219011976917343065F58ED4A │
└──────────────────────────────────┴──────────────────────────────────────────┴──────────────────────────────────────────────────────────────────┘
```

**Case-insensitive mode**

```
SELECT
    hmac('SHA256', 'message', 'key') = HMAC('sha256', 'message', 'key') AS same_result,
    HMAC('SHA256', 'message', 'key') = Hmac('Sha256', 'message', 'key') AS also_same;
```

```
┌─same_result─┬─also_same─┐
│           1 │         1 │
└─────────────┴───────────┘
```

## aes\_decrypt\_mysql[​](#aes_decrypt_mysql "Direct link to aes_decrypt_mysql")

Introduced in: v20.12.0

Decrypts data encrypted by MySQL's [`AES_ENCRYPT`](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt) function.

Produces the same plaintext as [`decrypt`](#decrypt) for the same inputs.
When `key` or `iv` are longer than they should normally be, `aes_decrypt_mysql` will stick to what MySQL's `aes_decrypt` does which is to 'fold' `key` and ignore the excess bits of `IV`.

Supports the following 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`](/docs/sql-reference/data-types/string)
- `ciphertext` — Encrypted text that needs to be decrypted. [`String`](/docs/sql-reference/data-types/string)
- `key` — Decryption key. [`String`](/docs/sql-reference/data-types/string)
- `iv` — Optional. Initialization vector. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the decrypted String. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Decrypt MySQL data**

```
-- Let's decrypt data we'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)

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

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

## aes\_encrypt\_mysql[​](#aes_encrypt_mysql "Direct link to aes_encrypt_mysql")

Introduced in: v20.12.0

Encrypts text the same way as MySQL's `AES_ENCRYPT` function does.
The resulting ciphertext can be decrypted with MySQL's `AES_DECRYPT` function.
Produces the same ciphertext as the `encrypt` function for the same inputs.
When `key` or `iv` are longer than they should normally be, `aes_encrypt_mysql` will stick to what MySQL's `aes_encrypt` does which is to 'fold' `key` and ignore the excess bits of `iv`.

The supported encryption modes are:

- 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`](/docs/sql-reference/data-types/string)
- `plaintext` — Text that should be encrypted. [`String`](/docs/sql-reference/data-types/string)
- `key` — Encryption key. If the key is longer than required by `mode`, MySQL-specific key folding is performed. [`String`](/docs/sql-reference/data-types/string)
- `iv` — Optional. Initialization vector. Only the first 16 bytes are taken into account. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Ciphertext binary string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Equal input comparison**

```
-- Given equal 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;
```

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

**Encrypt fails with long key**

```
-- But encrypt fails when key or iv is longer than expected:
SELECT encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123');
```

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

**MySQL compatibility**

```
-- aes_encrypt_mysql produces MySQL-compatible output:
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext;
```

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

**Longer IV produces the same result**

```
-- Notice how supplying even longer IV produces the same result
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertext
```

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

## decrypt[​](#decrypt "Direct link to decrypt")

Introduced in: v20.12.0

This function decrypts an AES-encrypted binary string 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
- aes-128-cfb, aes-128-cfb1, aes-128-cfb8

**Syntax**

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

**Arguments**