AES_ENCRYPT
Encrypts the given plaintext using the AES (Advanced Encryption Standard) algorithm with a 128-bit key or a 256-bit key.
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 variableAES_DEFAULT_ENCRYPTION_MODEwill 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
SELECT HEX(AES_ENCRYPT('secret message', 'ohai'));
****
+----------------------------------------------------------------+
| HEX(AES_ENCRYPT('secret message', 'ohai')) |
+----------------------------------------------------------------+
| C958FF3BC0134ADE4A8F952338C1FAEC |
+----------------------------------------------------------------+
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
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
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.
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