©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The AES_ENCRYPT SQL Function in MySQL and MariaDB - Encrypt with AES-Algorithmus

AES_ENCRYPTSyntax:AES_ENCRYPT(String, Key)
Return value:BINARY
Function type:Encryption & Compression
Function Description

The AES_ENCRYPT() function encrypts the "String" with the specified "Key" using the AES algorithm.

AES = Advanced Encryption Standard, also known as Rijndael.

The result is a binary string of single or multiple 128-bit (16-character) length, depending on the length of the "String" to be encrypted.

Numeric values ​​in the "String" and "Key" arguments are interpreted as a character string.

If the value of the "String" or "Key" arguments is NULL, the AES_ENCRYPT() function returns NULL.


The function AES_DECRYPT() can be used for decryption.

SQL Examples for the AES_ENCRYPT function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

select hex(aes_encrypt('Test''Key')), 
       bit_length(aes_encrypt('Test''Key')) AS len;
       
select hex(aes_encrypt(repeat('Test', 5), sha2('Key',512))), 
       bit_length(aes_encrypt(repeat('Test', 5), 'Key')) as len;

select hex(aes_encrypt(repeat('Test', 10), sha2('Key',512))), 
       bit_length(aes_encrypt(repeat('Test', 10), 'Key')) as len;

select hex(aes_encrypt(repeat('Test', 20), sha2('Key',512))), 
       bit_length(aes_encrypt(repeat('Test', 20), 'Key')) as len;

select hex(aes_encrypt('123456''789'));
select hex(aes_encrypt(123456, 789));

select hex(aes_encrypt('''key'));
select aes_encrypt(null'key');
select aes_encrypt('Test'null);

hex(aes_encrypt('Test', 'Key'))
len
varchar(32) BINARYbigint(10)
4AF21D11E66D5A9830B4297CFD7A78F6128
hex(aes_encrypt(repeat('Test', 5), sha2('Key',512)))
len
varchar(64) BINARYbigint(10)
D07D851220021446BBA595ACCE20B2BB8C7A20F65BDEB36...256
hex(aes_encrypt(repeat('Test', 10), sha2('Key',512)))
len
varchar(96) BINARYbigint(10)
D07D851220021446BBA595ACCE20B2BBD07D85122002144...384
hex(aes_encrypt(repeat('Test', 20), sha2('Key',512)))
len
varchar(192) BINARYbigint(10)
D07D851220021446BBA595ACCE20B2BBD07D85122002144...768
hex(aes_encrypt('123456', '789'))
varchar(32) BINARY
F5646561ECA0A52CE14174A8CE4FFB47
hex(aes_encrypt(123456, 789))
varchar(32) BINARY
F5646561ECA0A52CE14174A8CE4FFB47
hex(aes_encrypt('', 'key'))
varchar(32) BINARY
C717530F41F320757B4AA1BFAF11C42E
aes_encrypt(null, 'key')
varchar(16) BINARY
NULL
aes_encrypt('Test', null)
varchar(16) BINARY
NULL

Examples for calculating the length of the return value

 1
 2
 3
 4
 5
 6
 7
 8

select hex(aes_encrypt('Test''Key')), octet_length(aes_encrypt('Test''Key'));

select 16 * ceil((octet_length(repeat('test', 1)) + 1) / 16);
select 16 * ceil((octet_length(repeat('test', 5)) + 1) / 16);
select 16 * ceil((octet_length(repeat('test', 10)) + 1) / 16);
select 16 * ceil((octet_length(repeat('test', 20)) + 1) / 16);

hex(aes_encrypt('Test', 'Key'))
octet_length(aes_encrypt('Test', 'Key'))
varchar(32) BINARYint(10)
4AF21D11E66D5A9830B4297CFD7A78F616
16 * ceil((octet_length(repeat('test', 1)) + 1) / 16)
bigint(15)
16
16 * ceil((octet_length(repeat('test', 5)) + 1) / 16)
bigint(15)
32
16 * ceil((octet_length(repeat('test', 10)) + 1) / 16)
bigint(15)
48
16 * ceil((octet_length(repeat('test', 20)) + 1) / 16)
bigint(15)
96
The examples were created with the MyWAY SQL manager: Download

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

MySQL and MariaDB provide an AES_ENCRYPT() function used to encrypt data using the AES -Encryption algorithm. The function takes the data and a key as input and returns the encrypted value as a binary string.

The AES_ENCRYPT() function in MySQL and MariaDB is commonly used to encrypt sensitive data that needs to be stored securely in the database, for example sensitive data such as personal information, passwords, credit card numbers or other sensitive data. Encryption makes data unreadable without the key, even if the database is compromised.

The function also provides security when data is transmitted over insecure networks or communication channels, or under data protection regulations that require the protection of sensitive information, as well as when encrypting database backups.
ADDDATEADDTIMEAES_DECRYPTAES_ENCRYPTANY_VALUEASCIIASIN

Further MySQL und MariaDB SQL Encryption & Compression

AES_DECRYPTAES_DECRYPT(String, Key)
More about AES_DECRYPT Function

COMPRESSCOMPRESS(String)
More about COMPRESS Function

DECODEDECODE(Binary string, Password)
More about DECODE Function

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

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

ENCODEENCODE(String, Password)
More about ENCODE Function

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

FROM_BASE64FROM_BASE64(String)
More about FROM_BASE64 Function

MD5MD5(String)
More about MD5 Function

OLD_PASSWORDOLD_PASSWORD(String)
More about OLD_PASSWORD Function

PASSWORDPASSWORD(String)
More about PASSWORD Function

RANDOM_BYTESRANDOM_BYTES(Length)
More about RANDOM_BYTES Function

SHASHA(String)
More about SHA Function

SHA1SHA1(String)
More about SHA1 Function

SHA2SHA2(String, Length)
More about SHA2 Function

TO_BASE64TO_BASE64(String)
More about TO_BASE64 Function

UNCOMPRESSUNCOMPRESS(Binary string)
More about UNCOMPRESS Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the AES_ENCRYPT SQL function: mysql.com and mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer