Cloudera Docs

Encryption and decryption examples

Learn about examples for encrypting and decrypting data using various AES modes and demonstrates common error scenarios.

Basic encryption and decryption

This is the most common use case. You encrypt a string and then decrypt it to get the original value.

aes_encrypt(string, key, mode, iv) aes_decrypt(binary, key, mode, iv)

Encrypting a string with AES_128_GCM

Use this query to encrypt a string. The base64encode function converts the binary encrypted output into a text string that is easy to store and display.

SELECT base64encode(aes_encrypt('ABC', '1234567890123456','AES_128_GCM','1234567890123456'));

This example uses the following elements:

  • 'ABC': The string to encrypt.
  • '1234567890123456': The 128-bit encryption key.
  • 'AES_128_GCM': The encryption mode.
  • '1234567890123456': The initialization vector (IV).

Decrypting the encrypted a string

To reverse the process, use the base64decode function to convert the text back to binary, and then use the aes_decrypt function with the same key, mode, and IV to restore the original ABC string.

SELECT aes_decrypt(base64decode('x+am+BIqtrEK9FpC/zrvpOycjQ=='),'1234567890123456','AES_128_GCM','1234567890123456');

Using different modes

The following examples showcase different AES modes, each with their own characteristics:

  • The AES_128_ECB mode is the basic mode of operation.
    • Decrypt with ECB mode
      select aes_decrypt(base64decode('y6Ss+zCYObpCbgfWfyNWTw=='),'1234567890123456','AES_128_ECB','');
-- RESULTS
'ABC'
  • The AES_256_GCM mode requires a longer 256-bit key.
    • Encrypt with AES_256_GCM
      select base64encode(aes_encrypt('ABC', '12345678901234567890123456789012','AES_256_GCM','1234567890123456'));
    • Decrypt with AES_256_GCM
      select aes_decrypt(base64decode('F/DLkSwEikFOlqzXVCysy1JX7Q=='),'12345678901234567890123456789012','AES_256_GCM','1234567890123456');

Common error scenarios

The following examples highlight important constraints and errors:

NULL Values – This example shows that the key is NULL, which will result in an error: 
-- This will fail because the key is NULL
 select base64encode(aes_encrypt('ABC',NULL,'AES_256_GCM','1234567890123456'));
Invalid Mode – This example shows using an incorrect mode string that will result in an error: 
-- This will fail due to an invalid mode name
select base64encode(aes_encrypt('ABC', '12345678901234567890123456789012','AES_256_CTB','1234567890123456'));
Incorrect Key or IV Length – This example shows that the key must be either 128 or 256 bits long and the IV must also adhere to specific length requirements, otherwise an error will occur:
-- This will fail due to an incorrect key length
select base64encode(aes_encrypt('ABC', '123456789012345678901234567890121','AES_256_GCM','1234567890123456'));
Case Insensitivity – This example shows that the mode string is not case-sensitive. 
-- This works, even with lowercase mode
select base64encode(aes_encrypt('ABC', '12345678901234567890123456789012','aes_256_gcm','1234567890123456'));