Skip to main content

Categories: Cryptography

ENCRYPT

Encrypts data using AES encryption. Supported modes include AES-ECB, AES-CBC, and AES-GCM.

Syntax​

ENCRYPT(data VARBINARY, key VARBINARY, mode VARCHAR) → VARBINARY​

  • data: The data to encrypt.
  • key: The encryption key. Must be 16, 24, or 32 bytes.
  • mode: The encryption mode. Supported modes: AES-ECB (defaults to PKCS7 padding), AES-ECB-PKCS7, AES-ECB-NONE, AES-CBC (defaults to PKCS7 padding), AES-CBC-PKCS7, AES-CBC-NONE, AES-GCM. The AES-ECB mode is vulnerable and should not be used for secure encryption.

Examples

ENCRYPT example 
SELECT ENCRYPT(data, key, 'AES-ECB')
FROM
  (
    SELECT
      BINARY_STRING('Hello World') AS data,
      BINARY_STRING('1234567890123456') AS key
  )

-- xQAXVi52g+wT7xoVN08ssQ==

ENCRYPT(data VARBINARY, key VARBINARY, mode VARCHAR, iv VARBINARY) → VARBINARY​

  • data: The data to encrypt.
  • key: The encryption key. Must be 16, 24, or 32 bytes.
  • mode: The encryption mode. Supported modes: AES-ECB (defaults to PKCS7 padding), AES-ECB-PKCS7, AES-ECB-NONE, AES-CBC (defaults to PKCS7 padding), AES-CBC-PKCS7, AES-CBC-NONE, AES-GCM. The AES-ECB mode is vulnerable and should not be used for secure encryption.
  • iv: The initialization vector (IV) is a cryptographically random value that ensures identical data is encrypted differently each time. Never reuse the same iv with the same key.
    • For AES-GCM, use a 12-byte value.
    • For AES-CBC, AES-CBC-PKCS7, and AES-CBC-NONE, use a 16-byte value.

Examples

ENCRYPT example 
SELECT ENCRYPT(data, key, 'AES-CBC', iv)
FROM
  (
    SELECT
      BINARY_STRING('Hello World') AS data,
      BINARY_STRING('1234567890123456') AS key,
      BINARY_STRING('1234567890123456') AS iv
  )

-- ZyODokM33Io1ZKIA8h7owA==

ENCRYPT(data VARBINARY, key VARBINARY, mode VARCHAR, iv VARBINARY, aad VARBINARY) → VARBINARY​

  • data: The data to encrypt.
  • key: The encryption key. Must be 16, 24, or 32 bytes.
  • mode: Must be AES-GCM. The AES-ECB mode is vulnerable and should not be used for secure encryption.
  • iv: See iv, above
  • aad: The Additional Authenticated Data (AAD) is data cryptographically bound to the ciphertext. If the aad is modified or the ciphertext is swapped between rows, decryption fails. Use aad to bind encrypted data to its context, such as a row identifier, customer ID, or table name. An authentication tag will be generated by the system and concatenated with the resulting ciphertext in the function output.

Examples

ENCRYPT example 
SELECT ENCRYPT(data, key, 'AES-GCM', iv, aad)
FROM
  (
    SELECT
      BINARY_STRING('Hello World') AS data,
      BINARY_STRING('1234567890123456') AS key,
      BINARY_STRING('123456789012') AS iv,
      BINARY_STRING('metadata') AS aad
  )

-- +RQCxLexA2fVnX9N/SMT820KqW52WM9fVx9j

Usage Notes​

  • Retrieve keys at runtime rather than hardcoding them in queries.
  • Rotate keys periodically according to your organization's security policies.
  • Use different keys for different data classifications or tenants.
  • Ensure key length matches your security requirements: 32 bytes for AES-256 (recommended), 24 bytes for AES-192, or 16 bytes for AES-128.