How the AES_DECRYPT() function works in Mariadb?

The AES_DECRYPT() function in MariaDB is used to decrypt data that was previously encrypted using the AES_ENCRYPT() function. It utilizes the Advanced Encryption Standard (AES) algorithm, which is a symmetric encryption algorithm meaning the same key is used for both encryption and decryption.

Syntax

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

AES_DECRYPT(ciphertext, key_str[, init_vector])

ciphertext is the encrypted data to be decrypted. key_str is the encryption key used to encrypt the data, and it must be the same key used for encryption. The optional init_vector parameter is used for block encryption modes that require it.

Examples

Basic Decryption

To decrypt a previously encrypted string:

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

0x48656C6C6F2C20576F726C6421

This example shows the decryption of a simple string that was encrypted with the key ’encryption_key'.

Decryption with NULL

If the provided key is incorrect, the function returns NULL:

SELECT AES_DECRYPT(AES_ENCRYPT('Hello, World!', 'encryption_key'), 'wrong_key');

NULL

This demonstrates that using the wrong key for decryption results in a NULL value.

Decryption with Initialization Vector

Using an initialization vector for decryption:

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

0x48656C6C6F2C20576F726C6421

This example uses an initialization vector ‘init_vector’ for both encryption and decryption.

Decryption of Binary Data

Decrypting binary data:

SELECT AES_DECRYPT(AES_ENCRYPT('binary_data', 'encryption_key'), 'encryption_key');

0x62696E6172795F64617461

This shows the decryption of binary data encrypted with the key ’encryption_key’.

Decryption from a Table

If decryption is needed from a table column:

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

+----------------------------------------------------------------------------------------------------+
| AES_DECRYPT(encrypted_message, 'encryption_key')                                                   |
+----------------------------------------------------------------------------------------------------+
| 0x536563726574204D657373616765                                                                     |
+----------------------------------------------------------------------------------------------------+

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

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

MariaDB AES_ENCRYPT() function is used to encrypt a string or binary data.
MariaDB ENCODE() function encodes a string.
MariaDB DECODE() function decodes a string encoded by ENCODE().

Conclusion

The AES_DECRYPT() function is a crucial component of data security in MariaDB, allowing for the safe decryption of sensitive information. It is important to use a strong and secure key for encryption and to keep it confidential. Proper use of the AES_DECRYPT() function ensures that encrypted data remains protected and is only accessible to users with the correct key. Always remember to follow best practices for data encryption and decryption to maintain the integrity and security of your data.