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¶

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.

Example¶

Encrypt a secret string using different AES modes.

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:

┌─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¶

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.

Example¶

Encrypt a string using aes_encrypt_mysql with a key and IV that are longer than typically expected, demonstrating MySQL-compatible handling.

SELECT
    hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext

Result:

┌─ciphertext───┐
│ 24E9E4966469 │
└──────────────┘

Supplying an even longer IV produces the same result, confirming MySQL's behavior of ignoring excess IV bytes:

SELECT
    hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertext

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¶

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.

Example¶

Decrypt data that was previously encrypted using encrypt.

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:

┌─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¶

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, or NULL if decryption fails.

Example¶

Demonstrate tryDecrypt returning NULL when an incorrect key is provided, while successfully decrypting with the correct key.

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:

┌──────────────────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¶

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.

Example¶

Decrypt data that was previously encrypted with MySQL-compatible settings.

SELECT
    aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext

Result:

┌─plaintext─┐
│ Secret    │
└───────────┘