Cloudera Docs

Encryption and Decryption Examples

This topic provides 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 back.

Syntax:
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's easy to store.

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

In this example:

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

The base64encode function is used to convert the binary encrypted output into a text string that can be easily stored or displayed.

Decrypting the Encrypted String:

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

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

This is the reverse process. base64decode converts the text back to binary, and aes_decrypt uses the same key, mode, and IV to restore the original string, 'ABC'.

Using Different Modes

The examples showcase different AES modes, each with its own characteristics.

AES_128_ECB: A basic mode of operation.
-- Decrypting with ECB mode 
select aes_decrypt(base64decode('y6Ss+zCYObpCbgfWfyNWTw=='),'1234567890123456','AES_128_ECB','');
-- RESULTS
'ABC'
AES_256_GCM: This 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 examples also highlight important constraints and errors.

NULL Values: The examples show that if the key is NULL, an error will occur. 
-- This will fail because the key is NULL
 select base64encode(aes_encrypt('ABC',NULL,'AES_256_GCM','1234567890123456'));
Invalid Mode: Using an incorrect mode string 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: The key must be either 128 or 256 bits long. The IV must also adhere to specific length requirements. 
-- This will fail due to an incorrect key length
select base64encode(aes_encrypt('ABC', '123456789012345678901234567890121','AES_256_GCM','1234567890123456'));
Case Insensitivity: The mode string is not case-sensitive. 
-- This works, even with lowercase mode
select base64encode(aes_encrypt('ABC', '12345678901234567890123456789012','aes_256_gcm','1234567890123456'));