How the AES_ENCRYPT() function works in Mariadb?

The AES_ENCRYPT() function is a built-in encryption function in MariaDB that allows you to encrypt a string of data using the Advanced Encryption Standard (AES), which is a symmetric encryption algorithm. This means the same key is used for both encryption and decryption.

Syntax

The syntax for the MariaDB AES_ENCRYPT() function is as follows:

AES_ENCRYPT(str, key_str[, init_vector])

str is the string to be encrypted, key_str is the encryption key, and the optional init_vector is used for block encryption modes that require an initialization vector.

Examples

Basic Encryption

To encrypt a simple string:

SELECT HEX(AES_ENCRYPT('Hello, World!', 'encryption_key'));

0539730D7E3E27CC9B3E6865C298E17F

This example encrypts the string ‘Hello, World!’ with the key ’encryption_key’ and returns the result in hexadecimal format.

Encryption with Initialization Vector

Using an initialization vector for encryption:

SELECT HEX(AES_ENCRYPT('Hello, World!', 'encryption_key', 'init_vector'));

0539730D7E3E27CC9B3E6865C298E17F

This example includes an initialization vector ‘init_vector’ in the encryption process.

Encrypting Binary Data

Encrypting binary data such as an image or file content:

SELECT HEX(AES_ENCRYPT(_binary'binary_data', 'encryption_key'));

9135FB1E141087C1124FCEBA26A268A8

This shows how to encrypt binary data using the AES_ENCRYPT() function.

Encrypting Data from a Table

If you need to encrypt data stored in a table:

DROP TABLE IF EXISTS messages;
CREATE TABLE messages (id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(255));
INSERT INTO messages (message) VALUES ('Secret Message');
SELECT HEX(AES_ENCRYPT(message, 'encryption_key')) FROM messages WHERE id = 1;

+---------------------------------------------+
| HEX(AES_ENCRYPT(message, 'encryption_key')) |
+---------------------------------------------+
| 677816D87540582943F17E598EE0736D            |
+---------------------------------------------

This creates a table with messages and then encrypts the message with the id of 1.

Handling NULL Values

If either argument is NULL, the function returns NULL:

SELECT AES_ENCRYPT(NULL, 'encryption_key');

NULL

This demonstrates that the function returns NULL if the input string is NULL.

Here are a few functions related to MariaDB’s AES_ENCRYPT():

MariaDB AES_DECRYPT() function is used to decrypt a string encrypted by AES_ENCRYPT().
MariaDB ENCODE() function encodes a string.
MariaDB DECODE() function decodes a string encoded by ENCODE().

Conclusion

The AES_ENCRYPT() function in MariaDB is a powerful tool for ensuring data confidentiality. It is essential to use a strong and secure key for encryption and to keep it confidential. When implemented correctly, AES_ENCRYPT() helps protect sensitive data from unauthorized access, making it an integral part of a robust security strategy in database management. Always remember to follow best practices for data encryption to maintain the integrity and security of your data.