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 │ └───────────┘
Updated