Skip to main content
Skip to main content
Edit this page

encryption-functions

These functions implement encryption and decryption of data with AES (Advanced Encryption Standard) 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).

Note that these functions work slowly until ClickHouse 21.1.

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
  • aes-128-cfb, aes-128-cfb1, aes-128-cfb8

Syntax

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

Create this table:

Query:

Insert 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:

Query:

Result:

Example with -gcm:

Query:

Result:

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

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:

Result:

But encrypt fails when key or iv is longer than expected:

Query:

Result:

While aes_encrypt_mysql produces MySQL-compatible output:

Query:

Result:

Notice how supplying even longer IV produces the same result

Query:

Result:

Which is binary equal to what MySQL produces on same inputs:

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
  • aes-128-cfb, aes-128-cfb1, aes-128-cfb8

Syntax

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

Examples

Re-using table from encrypt.

Query:

Result:

Now let's try to decrypt all that data.

Query:

Result:

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.

tryDecrypt​

Similar to decrypt, but returns NULL if decryption fails because of using the wrong key.

Examples

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:

Insert some data:

Query:

Result:

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

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

Examples

Let's decrypt data we've previously encrypted with MySQL:

Query:

Result: