ENHANCED_AES_ENCRYPT

Syntax

ENHANCED_AES_ENCRYPT(plaintext[, iv])

Purpose

• This function is primarily used for column encryption. To enable this feature, set the tde_method parameter. For more information, see tde_method.

• This function requires the ENCRYPT privilege.

• plaintext is the plaintext data to be encrypted using the AES algorithm.

• [, iv] is an optional parameter that specifies the initialization vector.

• This function uses the tenant's current primary key for encryption. The default encryption algorithm is AES-128-ECB, which can be changed by setting the system variable block_encryption_mode. Only AES encryption algorithms are supported.

• This function supports encryption algorithms that require an initialization vector iv, such as AES-128-CFB1. When using these algorithms for encryption or decryption, you must explicitly provide the initialization vector iv as the second parameter to the function. The minimum length of iv is 16 bytes.

• Key rotation does not affect decryption operations.

Examples

Use the INSERT or UPDATE statement to explicitly apply the encryption function to the columns that require encryption.

-- Encrypt the plaintext and write it to the cipher_col column in the tbl table.
-- If any value in the value list uses the encryption function, the corresponding privilege is required.
INSERT INTO tbl(cipher_col, ...) VALUES(ENHANCED_AES_ENCRYPT(plaintext), ...);
-- Update the ciphertext column.
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;

Use the SELECT statement to query the ciphertext column and return the ciphertext.

-- Query the ciphertext column.
SELECT cipher_col FROM tbl;

Directly compare the ciphertext with its corresponding plaintext. The expected result is false. To perform this comparison, you need to decrypt the ciphertext or encrypt the plaintext.

-- cipher_col is the ciphertext column, and 12345 is the plaintext data. The ciphertext data cannot be matched.
SELECT ... FROM tbl WHERE cipher_col = '12345';
-- The LIKE statement behaves similarly and cannot match the ciphertext data.
SELECT ... FROM tbl WHERE cipher_col LIKE '123%';
-- After encrypting the plaintext data '12345', it should match the ciphertext data encrypted with the current key. An index can be used for this.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- The encrypted column can be used for various calculations, including comparisons.
SELECT ... FROM tbl WHERE tbl.cipher_col = another_tbl.another_cipher_col;
SELECT ... FROM tbl GROUP BY cipher_col ORDER BY cipher_col;

References

ENHANCED_AES_DECRYPT

Privilege classification in MySQL mode