Relational Data - Functions - Encryption Functions: Difference between revisions
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:22 GMT-0400 (Eastern Daylight Time)) |
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 15:05:49 GMT-0400 (Eastern Daylight Time)) |
||
Line 1: | Line 1: | ||
These functions implement encryption and decryption of data with AES (Advanced Encryption Standard) algorithm. | |||
<pre> | Key length depends on encryption mode. It is 16, 24, and 32 bytes long for <code>-128-</code>, <code>-196-</code>, and <code>-256-</code> 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 | |||
'''Syntax''' | |||
<syntaxhighlight lang="sql">encrypt('mode', 'plaintext', 'key' [, iv, aad])</syntaxhighlight> | |||
'''Arguments''' | |||
* <code>mode</code> — Encryption mode. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>plaintext</code> — Text that need to be encrypted. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>key</code> — Encryption key. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>iv</code> — Initialization vector. Required for <code>-gcm</code> modes, optional for others. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>aad</code> — Additional authenticated data. It isn’t encrypted, but it affects decryption. Works only in <code>-gcm</code> modes, for others would throw an exception. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Returned value''' | |||
* Ciphertext binary string. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Examples''' | |||
Create this table: | |||
Query: | |||
<syntaxhighlight lang="sql">CREATE TABLE encryption_test | |||
( | |||
`comment` String, | |||
`secret` String | |||
) | |||
ENGINE = Memory;</syntaxhighlight> | |||
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: | |||
<syntaxhighlight lang="sql">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'));</syntaxhighlight> | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT comment, hex(secret) FROM encryption_test;</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─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 │ | |||
└──────────────────────────────────┴──────────────────────────────────┘</syntaxhighlight> | |||
Example with <code>-gcm</code>: | |||
Query: | |||
<syntaxhighlight lang="sql">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%';</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─comment──────────────┬─hex(secret)──────────────────────────────────┐ | |||
│ aes-256-gcm │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │ | |||
│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │ | |||
└──────────────────────┴──────────────────────────────────────────────┘</syntaxhighlight> | |||
== aes_encrypt_mysql == | |||
Compatible with mysql encryption and resulting ciphertext can be decrypted with [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt AES_DECRYPT] function. | |||
Will produce the same ciphertext as <code>encrypt</code> on equal inputs. But when <code>key</code> or <code>iv</code> are longer than they should normally be, <code>aes_encrypt_mysql</code> will stick to what MySQL’s <code>aes_encrypt</code> does: ‘fold’ <code>key</code> and ignore excess bits of <code>iv</code>. | |||
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''' | |||
<syntaxhighlight lang="sql">aes_encrypt_mysql('mode', 'plaintext', 'key' [, iv])</syntaxhighlight> | |||
'''Arguments''' | |||
* <code>mode</code> — Encryption mode. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>plaintext</code> — Text that needs to be encrypted. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>key</code> — Encryption key. If key is longer than required by mode, MySQL-specific key folding is performed. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>iv</code> — Initialization vector. Optional, only first 16 bytes are taken into account [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Returned value''' | |||
* Ciphertext binary string. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Examples''' | |||
Given equal input <code>encrypt</code> and <code>aes_encrypt_mysql</code> produce the same ciphertext: | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') = aes_encrypt_mysql('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') AS ciphertexts_equal;</syntaxhighlight> | |||
Result: | |||
<pre>┌─ciphertexts_equal─┐ | |||
│ 1 │ | |||
└───────────────────┘</pre> | |||
But <code>encrypt</code> fails when <code>key</code> or <code>iv</code> is longer than expected: | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123');</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">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').</syntaxhighlight> | |||
While <code>aes_encrypt_mysql</code> produces MySQL-compatible output: | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext;</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─ciphertext───┐ | |||
│ 24E9E4966469 │ | |||
└──────────────┘</syntaxhighlight> | |||
Notice how supplying even longer <code>IV</code> produces the same result | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertext</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─ciphertext───┐ | |||
│ 24E9E4966469 │ | |||
└──────────────┘</syntaxhighlight> | |||
Which is binary equal to what MySQL produces on same inputs: | |||
<syntaxhighlight lang="sql">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)</syntaxhighlight> | |||
== 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''' | |||
<syntaxhighlight lang="sql">decrypt('mode', 'ciphertext', 'key' [, iv, aad])</syntaxhighlight> | |||
'''Arguments''' | |||
* <code>mode</code> — Decryption mode. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>ciphertext</code> — Encrypted text that needs to be decrypted. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>key</code> — Decryption key. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>iv</code> — Initialization vector. Required for <code>-gcm</code> modes, Optional for others. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>aad</code> — Additional authenticated data. Won’t decrypt if this value is incorrect. Works only in <code>-gcm</code> modes, for others would throw an exception. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Returned value''' | |||
* Decrypted String. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Examples''' | |||
Re-using table from [[#encrypt|encrypt]]. | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT comment, hex(secret) FROM encryption_test;</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─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 │ | |||
└──────────────────────────────────┴──────────────────────────────────┘</syntaxhighlight> | |||
Now let’s try to decrypt all that data. | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT comment, decrypt('aes-256-cfb128', secret, '12345678910121314151617181920212') as plaintext FROM encryption_test</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─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��@ | |||
└──────────────────────────────────┴───────────┘</syntaxhighlight> | |||
Notice how only a portion of the data was properly decrypted, and the rest is gibberish since either <code>mode</code>, <code>key</code>, or <code>iv</code> were different upon encryption. | |||
<span id="trydecrypt"></span> | |||
== tryDecrypt == | |||
Similar to <code>decrypt</code>, but returns NULL if decryption fails because of using the wrong key. | |||
'''Examples''' | |||
Let’s create a table where <code>user_id</code> is the unique user id, <code>encrypted</code> is an encrypted string field, <code>iv</code> is an initial vector for decrypt/encrypt. Assume that users know their id and the key to decrypt the encrypted field: | |||
<syntaxhighlight lang="sql">CREATE TABLE decrypt_null ( | |||
dt DateTime, | |||
user_id UInt32, | |||
encrypted String, | |||
iv String | |||
) ENGINE = Memory;</syntaxhighlight> | |||
Insert some data: | |||
<syntaxhighlight lang="sql">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');</syntaxhighlight> | |||
Query: | |||
<syntaxhighlight lang="sql">SELECT | |||
dt, | |||
user_id, | |||
tryDecrypt('aes-256-gcm', encrypted, 'keykeykeykeykeykeykeykeykeykey02', iv) AS value | |||
FROM decrypt_null | |||
ORDER BY user_id ASC</syntaxhighlight> | |||
Result: | |||
<pre>┌──────────────────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 │ ᴺᵁᴸᴸ │ | |||
└─────────────────────┴─────────┴────────┘</pre> | |||
== aes_decrypt_mysql == | |||
Compatible with mysql encryption and decrypts data encrypted with [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt AES_ENCRYPT] function. | |||
Will produce same plaintext as <code>decrypt</code> on equal inputs. But when <code>key</code> or <code>iv</code> are longer than they should normally be, <code>aes_decrypt_mysql</code> will stick to what MySQL’s <code>aes_decrypt</code> does: ‘fold’ <code>key</code> and ignore excess bits of <code>IV</code>. | |||
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''' | |||
<syntaxhighlight lang="sql">aes_decrypt_mysql('mode', 'ciphertext', 'key' [, iv])</syntaxhighlight> | |||
'''Arguments''' | |||
* <code>mode</code> — Decryption mode. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>ciphertext</code> — Encrypted text that needs to be decrypted. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>key</code> — Decryption key. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
* <code>iv</code> — Initialization vector. Optional. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Returned value''' | |||
* Decrypted String. [[Relational_Data_-_Data_Types_-_String#string|String]]. | |||
'''Examples''' | |||
Let’s decrypt data we’ve previously encrypted with MySQL: | |||
<syntaxhighlight lang="sql">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)</syntaxhighlight> | |||
Query: | Query: | ||
<syntaxhighlight lang="sql">SELECT aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext</syntaxhighlight> | |||
SELECT aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext</ | |||
Result: | Result: | ||
Latest revision as of 19:05, 28 August 2024
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
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
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-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'));
Query:
SELECT comment, hex(secret) FROM encryption_test;
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:
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-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.
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-ofb no IV │ B4972BDC4459 │
│ aes-256-ofb no IV, different key │ 2FF57C092DC9 │
│ aes-256-ofb 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-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��@
└──────────────────────────────────┴───────────┘
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:
CREATE TABLE decrypt_null (
dt DateTime,
user_id UInt32,
encrypted String,
iv String
) ENGINE = Memory;
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');
Query:
SELECT
dt,
user_id,
tryDecrypt('aes-256-gcm', encrypted, 'keykeykeykeykeykeykeykeykeykey02', iv) AS value
FROM decrypt_null
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
Let’s decrypt data we’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 │
└───────────┘