Encryption functions

**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**

- `mode` — Decryption mode. [`String`](/docs/sql-reference/data-types/string)
- `ciphertext` — Encrypted text that should be decrypted. [`String`](/docs/sql-reference/data-types/string)
- `key` — Decryption key. [`String`](/docs/sql-reference/data-types/string)
- `iv` — Initialization vector. Required for `-gcm` modes, optional for others. [`String`](/docs/sql-reference/data-types/string)
- `aad` — Additional authenticated data. Won't decrypt if this value is incorrect. Works only in `-gcm` modes, for others throws an exception. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Correctly decrypting encrypted data**

```
-- Re-using the table from the encrypt function example
SELECT comment, hex(secret) FROM encryption_test;
```

```
┌─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 │
└──────────────────────────────────┴──────────────────────────────────┘
```

**Incorrectly decrypting encrypted data**

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

```
-- Notice how only a portion of the data was properly decrypted, and the rest is gibberish since either `mode`, `key`, or `iv` were different upon encryption.
┌─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��@
└──────────────────────────────────┴───────────┘
```

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

Introduced in: v20.12.0

Encrypts plaintext into ciphertext using AES in one of 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**

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

**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. [`String`](/docs/sql-reference/data-types/string)
- `iv` — Initialization vector. Required for `-gcm` modes, optional for others. [`String`](/docs/sql-reference/data-types/string)
- `aad` — Additional authenticated data. It isn't encrypted, but it affects decryption. Works only in `-gcm` modes, for others it throws an exception. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Example encryption**

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

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

SELECT comment, hex(secret) FROM encryption_test;
```

```
┌─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 mode**

```
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%';
```

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

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

Introduced in: v22.10.0

Similar to the `decrypt` function, but returns `NULL` if decryption fails when using the wrong key.

**Syntax**

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

**Arguments**

- `mode` — Decryption mode. [`String`](/docs/sql-reference/data-types/string)
- `ciphertext` — Encrypted text that should be decrypted. [`String`](/docs/sql-reference/data-types/string)
- `key` — Decryption key. [`String`](/docs/sql-reference/data-types/string)
- `iv` — Optional. Initialization vector. Required for `-gcm` modes, optional for other modes. [`String`](/docs/sql-reference/data-types/string)
- `aad` — Optional. Additional authenticated data. Won't decrypt if this value is incorrect. Works only in `-gcm` modes, for other modes throws an exception. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the decrypted String, or `NULL` if decryption fails. [`Nullable(String)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Create table and insert data**

```
-- Let's 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:
CREATE TABLE decrypt_null
(
    dt DateTime,
    user_id UInt32,
    encrypted String,
    iv String
)
ENGINE = MergeTree;

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