Skip to main content

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

Examples

DECRYPT example 
SELECT DECRYPT(encrypted_data, key, 'AES-ECB')
FROM
  (
    SELECT
      FROM_BASE64('xQAXVi52g+wT7xoVN08ssQ==') AS encrypted_data,
      CONVERT_TO('1234567890123456', 'UTF8') 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 decryption 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.
  • iv: The initialization vector (IV) or nonce used during encryption. Required for AES-CBC and AES-GCM modes. Must be 16 bytes for AES-CBC mode, 12 bytes recommended for AES-GCM mode.

Examples

DECRYPT example 
SELECT DECRYPT(encrypted_data, key, 'AES-CBC', iv)
FROM
  (
    SELECT
      FROM_BASE64('ZyODokM33Io1ZKIA8h7owA==') AS encrypted_data,
      CONVERT_TO('1234567890123456', 'UTF8') AS key,
      CONVERT_TO('1234567890123456', 'UTF8') 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 (IV) or nonce used during encryption. Required for AES-CBC and AES-GCM modes. Must be 16 bytes for AES-CBC mode, 12 bytes recommended for AES-GCM mode.
  • aad: The additional authenticated data (AAD) 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
      FROM_BASE64('+RQCxLexA2fVnX9N/SMT820KqW52WM9fVx9j') AS encrypted_data,
      CONVERT_TO('1234567890123456', 'UTF8') AS key,
      CONVERT_TO('123456789012', 'UTF8') AS iv,
      CONVERT_TO('metadata', 'UTF8') AS aad
  )

-- /* Binary */ Hello World

Usage Notes​

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