AES_ENCRYPT

Syntax

AES_ENCRYPT(
    plaintext,
    key
    [, initialization_vector=null]
    [, encryption_mode=@AES_DEFAULT_ENCRYPTION_MODE])

Arguments

plaintext: the binary string data to encrypt
key: the text or binary string key to use for encryption
initialization_vector (IV): initial state for a cryptographic algorithm. Values for
- ECB (Electronic Code Book mode): not applicable, hence NULL should be passed
- GCM (Galois/Counter Mode): can contain either text or binary value
- 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 "ciphertext" of encrypted data, in binary. Function can return NULL if wrong encryption mode is provided or NULL is provided for IV in GCM mode.

Remarks

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.

The IV may be any size but for GCM, 12 bytes is recommended and for CBC, 16 bytes is recommended. Unique random data should be used for the IV, for each value encrypted.

Examples

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

Encryption with 128 bit keys and ECB operation mode

SQL

SELECT HEX(AES_ENCRYPT('secret message', 'ohai'));

+----------------------------------------------------------------+
| HEX(AES_ENCRYPT('secret message', 'ohai'))                     |
+----------------------------------------------------------------+
| C958FF3BC0134ADE4A8F952338C1FAEC                               |
+----------------------------------------------------------------+

SQL

SELECT HEX(AES_ENCRYPT('secret message', 'ohai', NULL, 'aes-128-ecb'));

+-----------------------------------------------------------------+
| HEX(AES_ENCRYPT('secret message', 'ohai', NULL, 'aes-128-ecb')) |
+-----------------------------------------------------------------+
| C958FF3BC0134ADE4A8F952338C1FAEC                                |
+-----------------------------------------------------------------+

Encryption with 256 bit keys and GCM operation mode

SQL

SELECT HEX(AES_ENCRYPT('secret message', 'ohai', 'singlestore', 'aes-256-gcm'));

+--------------------------------------------------------------------------+ 
|HEX(AES_ENCRYPT('secret message', 'ohai', 'singlestore', 'aes-256-gcm'))  |
+--------------------------------------------------------------------------+
| BCA86F5478A01A180412A4AC7F46950834C038A0F0DFA063EDDBC5EDCD00             |
+--------------------------------------------------------------------------+

Encryption with 256 bit keys and CBC operation mode

SQL

SELECT HEX(AES_ENCRYPT('secret message', '01234567890123456789012345678912', 'sixteen_bytes_IV', 'aes-256-cbc'));

+-----------------------------------------------------------------------------------------------------------+
| HEX(AES_ENCRYPT('secret message', '01234567890123456789012345678912', 'sixteen_bytes_IV', 'aes-256-cbc')) |
+-----------------------------------------------------------------------------------------------------------+
| D32D139606C0F7FDB69D9A2B7FEDE2C9                                                                          |   
+-----------------------------------------------------------------------------------------------------------+

Use Case

How to Encrypt and Decrypt Using an Init Vector

If you do not use an IV, an attacker may be able to learn things about your encrypted data by noticing that the same value encrypted twice with the same key produces the same ciphertext. Instead, use a new IV for each encryption. That way, encrypting the same value twice with the same key and a different IV will result in different cyphertext. A common practice is to store the IV next to the ciphertext in another column. Here’s an example of this approach.

SQL

SET GLOBAL aes_default_encryption_mode = 'aes-256-gcm';
DROP TABLE IF EXISTS t;
CREATE TABLE t(id int, init_vector binary(32), ciphertext longblob);
SET @iv = CONCAT(sys_guid(), sys_guid()); /* make 256-bit iv */
SET @key = CONCAT(sys_guid(), sys_guid()); /* make 256-bit key */
INSERT t VALUES(1, @iv, AES_ENCRYPT("secret 1", @key, @iv));
SET @iv = CONCAT(sys_guid(), sys_guid()); /* make 256-bit iv */
INSERT t VALUES(2, @iv, AES_ENCRYPT("secret 2", @key, @iv));

/* Now get the data back. */
SELECT id, HEX(init_vector), HEX(ciphertext), 
 AES_DECRYPT(ciphertext, @key, init_vector) plaintext
FROM t \G

*** 1. row ***
              id: 2
hex(init_vector): D8C280975DE34621BD168993BAF392163B41E80BFE8B4FA1B1CBA35D7C9B75B8
 hex(ciphertext): B8BCAD02E1EDFE119FC4B07946EB256DE1B50A9DE9F8AE78
       plaintext: secret 2
*** 2. row ***
              id: 1
hex(init_vector): BB342B4E85164A53BF769AA7DA9ACC8F735134782FD3448CBDDE6EC88B8D2DA3
 hex(ciphertext): C49D046BF9544EAB18CA34682E1AD674FEAD60F7B58E0634
      plaintext: secret 1
2 rows in set (0.02 sec)

Related Topics

AES_DECRYPT

Last modified: February 27, 2023