--- title: Encryption Functions reference meta: description: Functions for data encryption and decryption. headingMaxLevels: 2 --- # Encryption functions These functions provide capabilities for encrypting and decrypting data using the Advanced Encryption Standard (AES) algorithm. The required key length varies by encryption mode: 16 bytes for `-128-` modes, 24 bytes for `-196-` modes, and 32 bytes for `-256-` modes. The initialization vector (IV) length is always 16 bytes; any bytes beyond this length are ignored. ## encrypt This function encrypts a given plaintext string using the Advanced Encryption Standard (AES) algorithm with a specified mode, key, and optional initialization vector (IV) or additional authenticated data (AAD). It returns the encrypted data as a binary string. ### Syntax ```sql encrypt('mode', 'plaintext', 'key' [, iv, aad]) ``` ### Arguments - `mode`: String. The encryption algorithm and mode to use (e.g., `aes-256-cbc`, `aes-128-gcm`). - `plaintext`: String. The data to be encrypted. - `key`: String. The secret key used for encryption. Its length must match the chosen mode. - `iv`: String. An optional initialization vector. It is required for GCM modes and can be used with others. - `aad`: String. Optional additional authenticated data, used only with GCM modes. This data is not encrypted but is authenticated during decryption. ### Returns The encrypted data as a binary [String](../data-types/string). ### Example Encrypt a secret string using different AES modes. ```sql 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 with IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')), ('aes-256-gcm with AAD', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad')) ) ) ``` Result: ```result ┌─comment──────────────┬─hex(secret)──────────────────────────────────┐ │ aes-256-ofb no IV │ B4972BDC4459 │ │ aes-256-ofb with IV │ 5E6CB398F653 │ │ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │ └──────────────────────┴──────────────────────────────────────────────┘ ``` ## aes_encrypt_mysql This function encrypts data using AES, providing compatibility with MySQL's `AES_ENCRYPT` function. It handles key and IV lengths in a MySQL-specific way, folding longer keys and ignoring excess IV bytes. ### Syntax ```sql aes_encrypt_mysql('mode', 'plaintext', 'key' [, iv]) ``` ### Arguments - `mode`: String. The encryption algorithm and mode to use. - `plaintext`: String. The data to be encrypted. - `key`: String. The encryption key. If the key is longer than required by the mode, it will be folded to match MySQL's behavior. - `iv`: String. An optional initialization vector. Only the first 16 bytes are considered, similar to MySQL. ### Returns The encrypted data as a binary [String](../data-types/string). ### Example Encrypt a string using `aes_encrypt_mysql` with a key and IV that are longer than typically expected, demonstrating MySQL-compatible handling. ```sql SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext ``` Result: ```result ┌─ciphertext───┐ │ 24E9E4966469 │ └──────────────┘ ``` Supplying an even longer IV produces the same result, confirming MySQL's behavior of ignoring excess IV bytes: ```sql SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertext ``` Result: ```result ┌─ciphertext───┐ │ 24E9E4966469 │ └──────────────┘ ``` ## decrypt This function decrypts a ciphertext string back into its original plaintext using the specified AES mode, key, and optional initialization vector (IV) or additional authenticated data (AAD). ### Syntax ```sql decrypt('mode', 'ciphertext', 'key' [, iv, aad]) ``` ### Arguments - `mode`: String. The decryption algorithm and mode to use. This must match the mode used during encryption. - `ciphertext`: String. The encrypted data to be decrypted. - `key`: String. The secret key used for decryption. This must match the key used during encryption. - `iv`: String. An optional initialization vector. Required for GCM modes and must match the IV used during encryption. - `aad`: String. Optional additional authenticated data, used only with GCM modes. This must match the AAD used during encryption for successful decryption. ### Returns The decrypted data as a [String](../data-types/string). ### Example Decrypt data that was previously encrypted using `encrypt`. ```sql SELECT comment, decrypt(mode, secret, key, iv, aad) AS plaintext FROM ( SELECT c1 AS comment, c2 AS secret, c3 AS mode, c4 AS key, c5 AS iv, c6 AS aad FROM VALUES ( ('aes-256-ofb no IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212'), 'aes-256-ofb', '12345678910121314151617181920212', NULL, NULL), ('aes-256-ofb with IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv'), 'aes-256-ofb', '12345678910121314151617181920212', 'iviviviviviviviv', NULL), ('aes-256-gcm with AAD', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad'), 'aes-256-gcm', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad') ) ) ``` Result: ```result ┌─comment──────────────┬─plaintext─┐ │ aes-256-ofb no IV │ Secret │ │ aes-256-ofb with IV │ Secret │ │ aes-256-gcm with AAD │ Secret │ └──────────────────────┴───────────┘ ``` ## tryDecrypt This function attempts to decrypt a ciphertext string using the specified AES mode, key, and optional IV/AAD. If the decryption fails (e.g., due to an incorrect key or IV), it returns `NULL` instead of throwing an error. ### Syntax ```sql tryDecrypt('mode', 'ciphertext', 'key' [, iv, aad]) ``` ### Arguments - `mode`: String. The decryption algorithm and mode. - `ciphertext`: String. The encrypted data to be decrypted. - `key`: String. The decryption key. - `iv`: String. An optional initialization vector. - `aad`: String. Optional additional authenticated data. ### Returns The decrypted data as a [String](../data-types/string), or `NULL` if decryption fails. ### Example Demonstrate `tryDecrypt` returning `NULL` when an incorrect key is provided, while successfully decrypting with the correct key. ```sql 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: ```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 This function decrypts data that was encrypted using MySQL's `AES_ENCRYPT` function or `aes_encrypt_mysql`. It handles key and IV lengths in a MySQL-compatible manner, folding longer keys and ignoring excess IV bytes. ### Syntax ```sql aes_decrypt_mysql('mode', 'ciphertext', 'key' [, iv]) ``` ### Arguments - `mode`: String. The decryption algorithm and mode. - `ciphertext`: String. The encrypted data to be decrypted. - `key`: String. The decryption key. - `iv`: String. An optional initialization vector. ### Returns The decrypted data as a [String](../data-types/string). ### Example Decrypt data that was previously encrypted with MySQL-compatible settings. ```sql SELECT aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext ``` Result: ```result ┌─plaintext─┐ │ Secret │ └───────────┘ ```