# Encryption and decryption functions
<a name="encryption-decryption-functions"></a>

Encryption and decryption functions help SQL developers protect sensitive data from unauthorized access or misuse by converting it between a readable, plaintext form and an unreadable, ciphertext form.

AWS Clean Rooms Spark SQL supports the following encryption and decryption functions:

**Topics**
+ [AES\$1ENCRYPT function](AES_ENCRYPT.md)
+ [AES\$1DECRYPT function](AES_DECRYPT.md)

# AES\$1ENCRYPT function
<a name="AES_ENCRYPT"></a>

The AES\$1ENCRYPT function is used for encrypting data using the Advanced Encryption Standard (AES) algorithm.

## Syntax
<a name="AES_ENCRYPT-syntax"></a>

```
aes_encrypt(expr, key[, mode[, padding[, iv[, aad]]]])
```

## Arguments
<a name="AES_ENCRYPT-arguments"></a>

 *expr*   
The binary value to encrypt.

 *key*   
The passphrase to use to encrypt the data.  
Key lengths of 16, 24 and 32 bits are supported.

 *mode*   
Specifies which block cipher mode should be used to encrypt messages.   
Valid modes: ECB (Electronic CodeBook), GCM (Galois/Counter Mode), CBC (Cipher-Block Chaining).

 *padding*   
Specifies how to pad messages whose length isn't a multiple of the block size.   
Valid values: PKCS, NONE, DEFAULT.   
The DEFAULT padding means PKCS (Public Key Cryptography Standards) for ECB, NONE for GCM and PKCS for CBC.  
Supported combinations of (*mode*, *padding*) are ('ECB', 'PKCS'), ('GCM', 'NONE') and ('CBC', 'PKCS').

 *iv*   
Optional initialization vector (IV). Only supported for CBC and GCM modes.   
Valid values: 12-bytes long for GCM and 16 bytes for CBC.

 *aad*   
Optional additional authenticated data (AAD). Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption.

## Return type
<a name="AES_ENCRYPT-returm-type"></a>

The AES\$1ENCRYPT function returns an encrypted value of *expr* using AES in given mode with the specified padding.

## Examples
<a name="AES_ENCRYPT-example"></a>

The following example demonstrates how to use the Spark SQL AES\$1ENCRYPT function to securely encrypt a string of data (in this case, the word "Spark") using a specified encryption key. The resulting ciphertext is then Base64-encoded to make it easier to store or transmit.

```
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
  4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn
```

The following example demonstrates how to use the Spark SQL AES\$1ENCRYPT function to securely encrypt a string of data (in this case, the word "Spark") using a specified encryption key. The resulting ciphertext is then represented in hexadecimal format, which can be useful for tasks such as data storage, transmission, or debugging.

```
SELECT hex(aes_encrypt('Spark', '0000111122223333'));
 83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94
```

The following example demonstrates how to use the Spark SQL AES\$1ENCRYPT function to securely encrypt a string of data (in this case, "Spark SQL") using a specified encryption key, encryption mode, and padding mode. The resulting ciphertext is then Base64-encoded to make it easier to store or transmit.

```
SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
 3lmwu+Mw0H3fi5NDvcu9lg==
```

# AES\$1DECRYPT function
<a name="AES_DECRYPT"></a>

The AES\$1DECRYPT function is used for decrypting data using the Advanced Encryption Standard (AES) algorithm.

## Syntax
<a name="AES_DECRYPT-syntax"></a>

```
aes_decrypt(expr, key[, mode[, padding[, aad]]])
```

## Arguments
<a name="AES_DECRYPT-arguments"></a>

 *expr*   
The binary value to decrypt.

 *key*   
The passphrase to use to decrypt the data.  
The passphrase must match the key originally used to produce the encrypted value and be 16, 24, or 32 bytes long.

 *mode*   
Specifies which block cipher mode should be used to decrypt messages.   
Valid modes: ECB, GCM, CBC.

 *padding*   
Specifies how to pad messages whose length isn't a multiple of the block size.   
Valid values: PKCS, NONE, DEFAULT.   
The DEFAULT padding means PKCS for ECB, NONE for GCM and PKCS for CBC.

 *aad*   
Optional additional authenticated data (AAD). Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption.

## Return type
<a name="AES_DECRYPT-return-type"></a>

Returns a decrypted value of *expr* using AES in mode with padding.

## Examples
<a name="AES_DECRYPT-examples"></a>

The following example demonstrates how to use the Spark SQL AES\$1ENCRYPT function to securely encrypt a string of data (in this case, the word "Spark") using a specified encryption key. The resulting ciphertext is then Base64-encoded to make it easier to store or transmit. 

```
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
  4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn
```

The following example demonstrates how to use the Spark SQL AES\$1DECRYPT function to decrypt data that has been previously encrypted and Base64-encoded. The decryption process requires the correct encryption key and parameters (encryption mode and padding mode) to successfully recover the original plaintext data.

```
SELECT aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='), '1234567890abcdef', 'ECB', 'PKCS');
 Spark SQL
```