Version: 0.3.x

Encryption

Notice: Some of the examples below are referenced from ClickHouse Documentation but have been adapted and modified to work in ByConity.

danger

no encrypt functions but we have decrypt functions. syntax unsure

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-cfb1, aes-192-cfb1, aes-256-cfb1
aes-128-cfb8, aes-192-cfb8, aes-256-cfb8
aes-128-cfb128, aes-192-cfb128, aes-256-cfb128
aes-128-ofb, aes-192-ofb, aes-256-ofb
aes-128-gcm, aes-192-gcm, aes-256-gcm

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, optinal 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 .

Examples

Re-using table from encrypt .

Query:

SELECT comment, hex(secret) FROM encryption_test;

Result:

┌─comment──────────────┬─hex(secret)──────────────────────────────────┐

│ aes-256-gcm          │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │

│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │

└──────────────────────┴──────────────────────────────────────────────┘

┌─comment─────────────────────────────┬─hex(secret)──────────────────────┐

│ aes-256-cfb128 no IV                │ B4972BDC4459                     │

│ aes-256-cfb128 no IV, different key │ 2FF57C092DC9                     │

│ aes-256-cfb128 with IV              │ 5E6CB398F653                     │

│ aes-256-cbc no IV                   │ 1BC0629A92450D9E73A00E7D02CF4142 │

└─────────────────────────────────────┴──────────────────────────────────┘

Now let's try to decrypt all that data.

Query:

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

Result:

┌─comment─────────────────────────────┬─plaintext─┐

│ aes-256-cfb128 no IV                │ Secret    │

│ aes-256-cfb128 no IV, different key │ �4�

                                           �         │

│ aes-256-cfb128 with IV              │ ���6�~        │

 │aes-256-cbc no IV                   │ �2*4�h3c�4w��@

└─────────────────────────────────────┴───────────┘

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.

decrypt​

decrypt