	©	SQL :: Manager
				Home	Program Information	Technical details	Free download	Donate for a cup of coffee

The AES_DECRYPT SQL Function in MySQL and MariaDB - Decrypt with AES-Algorithmus

AES_DECRYPT	Syntax:	AES_DECRYPT(String, Key)
	Return value:	CHAR
	Function type:	Encryption & Compression

Function Description

The AES_DECRYPT() function decrypts a "Binary string" with the given "Key" using the AES algorithm.

The return is a decrypted string or NULL if an error occurs.

If "Key" contains a numeric value, it is interpreted as a character string.

If "Binary string" contains no characters, NULL is returned.

If the value of the "Binary string" or "Key" argument is NULL, the AES_DECRYPT() function returns NULL.

The function AES_ENCRYPT() can be used for encryption.

SQL Examples for the AES_DECRYPT function


select aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), 'Key');
select aes_decrypt(unhex('F5646561ECA0A52CE14174A8CE4FFB47'), 789);
select aes_decrypt(unhex('C717530F41F320757B4AA1BFAF11C42E'), 'key');
    /* crypted empty String */

select aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), 'Error');
select aes_decrypt(null, 'Key');
select aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), null);

aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), 'Key')
varchar(16) BINARY
Test

aes_decrypt(unhex('F5646561ECA0A52CE14174A8CE4FFB47'), 789)
varchar(16) BINARY
123456

aes_decrypt(unhex('C717530F41F320757B4AA1BFAF11C42E'), 'key')
varchar(16) BINARY

aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), 'Error')
varchar(16) BINARY
NULL

aes_decrypt(null, 'Key')
varchar(0) BINARY
NULL

aes_decrypt(unhex('4AF21D11E66D5A9830B4297CFD7A78F6'), null)
varchar(16) BINARY
NULL

The examples were created with the MyWAY SQL manager: Download

How to use the AES_DECRYPT() function in MySQL and MariaDB databases

Both MySQL and MariaDB provide an AES_DECRYPT() function that is used to decrypt data that have been encrypted with the AES encryption algorithm. The function takes the encrypted data and a key as input and returns the decrypted value as a string. It is important to note that the AES encryption algorithm used by the function requires that the data has been encrypted using the corresponding AES_ENCRYPT() function with the same key, otherwise the decryption process will fail and the function returns NULL.

The feature is specific to MySQL and MariaDB and may not be available in other database systems.

Further MySQL und MariaDB SQL Encryption & Compression

AES_ENCRYPT	AES_ENCRYPT(String, Key)
	More about AES_ENCRYPT Function

COMPRESS	COMPRESS(String)
	More about COMPRESS Function

DECODE	DECODE(Binary string, Password)
	More about DECODE Function

DES_DECRYPT	DES_DECRYPT(Binary string [, Key])
	More about DES_DECRYPT Function

DES_ENCRYPT	DES_ENCRYPT(String [, Key])
	More about DES_ENCRYPT Function

ENCODE	ENCODE(String, Password)
	More about ENCODE Function

ENCRYPT	ENCRYPT(String [, Salt])
	More about ENCRYPT Function

FROM_BASE64	FROM_BASE64(String)
	More about FROM_BASE64 Function

MD5	MD5(String)
	More about MD5 Function

OLD_PASSWORD	OLD_PASSWORD(String)
	More about OLD_PASSWORD Function

PASSWORD	PASSWORD(String)
	More about PASSWORD Function

RANDOM_BYTES	RANDOM_BYTES(Length)
	More about RANDOM_BYTES Function

SHA	SHA(String)
	More about SHA Function

SHA1	SHA1(String)
	More about SHA1 Function

SHA2	SHA2(String, Length)
	More about SHA2 Function

TO_BASE64	TO_BASE64(String)
	More about TO_BASE64 Function

UNCOMPRESS	UNCOMPRESS(Binary string)
	More about UNCOMPRESS Function

Numeric functions	String functions	Regular Expressions	Date and Time functions
Comparison functions	Encryption & Compression	Conversion functions	NULL functions
Aggregate functions	Window functions	JSON functions	Geometric functions
Sequence functions	Information functions	Dynamic Columns	Miscellaneous functions

More information about the AES_DECRYPT SQL function: mysql.com and mariadb.com

Updated: 2023-09-17

2020-2024 Heino Cunze-Fischer