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

Categories: Cryptography

DECRYPT

Decrypts data using AES decryption with various modes, including AES-ECB, AES-CBC, and AES-GCM. Supports PKCS7 padding or no padding options. For AES-GCM mode, verifies the authentication tag.

Syntax​

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

  • data: The encrypted data to decrypt.
  • key: The decryption 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

DECRYPT example 
SELECT DECRYPT(encrypted_data, key, 'AES-ECB')
FROM
  (
    SELECT
      UNBASE64('xQAXVi52g+wT7xoVN08ssQ==') AS encrypted_data,
      BINARY_STRING('1234567890123456') AS key
  )

-- /* Binary */ Hello World

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

  • data: The encrypted data to decrypt.
  • key: The decryption 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 used during encryption.

Examples

DECRYPT example 
SELECT DECRYPT(encrypted_data, key, 'AES-CBC', iv)
FROM
  (
    SELECT
      UNBASE64('ZyODokM33Io1ZKIA8h7owA==') AS encrypted_data,
      BINARY_STRING('1234567890123456') AS key,
      BINARY_STRING('1234567890123456') AS iv
  )

-- /* Binary */ Hello World

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

  • data: The encrypted data to decrypt.
  • key: The decryption key. Must be 16, 24, or 32 bytes.
  • mode: Must be AES-GCM
  • iv: The Initialization Vector used during encryption.
  • aad: The Additional Authenticated Data used during AES-GCM encryption. Must match the aad used during encryption for authentication to succeed. DECRYPT will verify ciphertext integrity by using the authentication tag concatenated during encryption.

Examples

DECRYPT example 
SELECT DECRYPT(encrypted_data, key, 'AES-GCM', iv, aad)
FROM
  (
    SELECT
      UNBASE64('+RQCxLexA2fVnX9N/SMT820KqW52WM9fVx9j') AS encrypted_data,
      BINARY_STRING('1234567890123456') AS key,
      BINARY_STRING('123456789012') AS iv,
      BINARY_STRING('metadata') AS aad
  )

-- /* Binary */ Hello World

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 by Dremio 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.