Decrypts the given ciphertext using the AES (Advanced Encryption Standard) algorithm, with a 128-bit key or a 256-bit key.


    [, initialization_vector=null]
    [, encryption_mode=@AES_DEFAULT_ENCRYPTION_MODE])


  • ciphertext: the binary data to decrypt.

  • key: the text or binary key to use for decryption.

  • initialization_vector (IV): initial state for a cryptographic algorithm

    • For ECB (Electronic Code Book mode): not applicable, hence the argument should not be used or NULL should be passed.

    • For GCM (Galois/Counter Mode): can contain either text or binary value.

    • For CBC (Cipher Block Chaining): can contain either text or binary value.

  • encryption_mode: optionally an encryption mode string that can be aes-128-ecb, aes-256-ecb, aes-128-gcm, aes-256-gcm, aes-128-cbc, aes-256-cbc.   If not provided, the value from the global variable AES_DEFAULT_ENCRYPTION_MODE will be used as the default.

Return Type

The plain text, or NULL if the key does not decrypt the ciphertext because either the IV isn't the same as used for encryption or incorrect encryption mode is provided.


  • Encryption Algorithm: AES

  • Key Size: 128 bit or 256-bit

  • Operation Mode:

    • ECB: Electronic Code Book mode

    • GCM: Galois/Counter Mode

    • CBC: Cipher Block Chaining

  • IV (initialization vector): Not used, Used for GCM and CBC.


Note: the UNHEX function is used in this example to make it easier to handle binary data.

Decryption with 128 bit keys and ECB operation mode

SELECT AES_DECRYPT(unhex('C958FF3BC0134ADE4A8F952338C1FAEC'), 'ohai');
| AES_DECRYPT(unhex('C958FF3BC0134ADE4A8F952338C1FAEC'), 'ohai'           |     
| secret message                                                          |   
SELECT AES_DECRYPT(UNHEX('C958FF3BC0134ADE4A8F952338C1FAEC'), 'ohai', NULL, 'aes-128-ecb');
| AES_DECRYPT(UNHEX('C958FF3BC0134ADE4A8F952338C1FAEC'), 'ohai', NULL, 'aes-128-ecb')  |
| secret message                                                                       |

See How to Encrypt and Decrypt using an Init Vector

Related Topics


Last modified: February 27, 2023

Was this article helpful?