Skip to main content
Version: current [26.x]

Categories: Cryptography

ENCRYPT

Encrypts data using AES encryption with various modes, including AES-ECB, AES-CBC, and AES-GCM. Supports PKCS7 padding or no padding options.

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 Electronic Cookbook (ECB) cipher mode. Supported modes:
    • AES-ECB-NONE – No padding is added. The data to be encrypted must be a multiple of 16 bytes
    • AES-ECB-PKCS7 – The system pads the data to be encrypted to a multiple of 16 bytes. The padding bytes indicate how many bytes were added.
    • AES-ECB – defaults to PKCS7 padding

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 Galois/Counter (GCM) or Cipher Block Chaining (CBC) cipher mode. Supported modes:
    • AES-GCM – Recommended for most use cases
    • AES-CBC-NONE – No padding is added. The data to be encrypted must be a multiple of 16 bytes
    • AES-CBC-PKCS7 – The system pads the data to be encrypted to a multiple of 16 bytes. The padding bytes indicate how many bytes were added.
    • AES-CBC – defaults to PKCS7 padding
  • iv: The Initialization Vector is a cryptographically random value that ensures identical data encrypts 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
  • iv: See iv, above
  • aad: The Additional Authenticated Data 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​

Best Practices for Key Handling​

  • 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.

Legacy Functions​

AES_ENCRYPT and AES_DECRYPT are deprecated and will be removed in a future release. Dremio recommends ENCRYPT and DECRYPT. Ciphertext created with AES_ENCRYPT can be decrypted with DECRYPT in AES-ECB mode and does not need to be recreated with ENCRYPT unless a stronger cipher mode is required.