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-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
encrypt('mode', 'plaintext', 'key' [, iv, aad])
Arguments
modeβ Encryption mode. String.plaintextβ Text thats need to be encrypted. String.keyβ Encryption key. String.ivβ Initialization vector. Required for-gcmmodes, optinal for others. String.aadβ Additional authenticated data. It isn't encrypted, but it affects decryption. Works only in-gcmmodes, for others would throw an exception. String.
Returned value
- Ciphertext binary string. String.
Examples
Create this table:
Query:
CREATE TABLE encryption_test
(
`comment` String,
`secret` String
)
ENGINE = Memory;
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:
INSERT INTO encryption_test VALUES('aes-256-cfb128 no IV', encrypt('aes-256-cfb128', 'Secret', '12345678910121314151617181920212')),\
('aes-256-cfb128 no IV, different key', encrypt('aes-256-cfb128', 'Secret', 'keykeykeykeykeykeykeykeykeykeyke')),\
('aes-256-cfb128 with IV', encrypt('aes-256-cfb128', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')),\
('aes-256-cbc no IV', encrypt('aes-256-cbc', 'Secret', '12345678910121314151617181920212'));
Query:
SELECT comment, hex(secret) FROM encryption_test;
Result:
ββ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 β
βββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββ
Example with -gcm:
Query:
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%';
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-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
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-cfb128', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') = aes_encrypt_mysql('aes-256-cfb128', '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-cfb128', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123');
Result:
Received exception from server (version 21.1.2):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: Invalid key size: 33 expected 32: While processing encrypt('aes-256-cfb128', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123').
While aes_encrypt_mysql produces MySQL-compatitalbe output:
Query:
SELECT hex(aes_encrypt_mysql('aes-256-cfb128', '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-cfb128', '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-cfb128';
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-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-gcmmodes, optinal for others. String.aadβ Additional authenticated data. Won't decrypt if this value is incorrect. Works only in-gcmmodes, 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.
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-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
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. Optinal. String.
Returned value
- Decrypted String. String.
Examples
Let's decrypt data we've previously encrypted with MySQL:
mysql> SET block_encryption_mode='aes-256-cfb128';
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-cfb128', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext
Result:
ββplaintextββ
β Secret β
βββββββββββββ