The AES_ENCRYPT SQL Function in MySQL and MariaDB - Encrypt with AES-Algorithmus |
|
| | AES_ENCRYPT | Syntax: | AES_ENCRYPT(String, Key) | | Return value: | BINARY | | Function type: | Encryption & Compression | |
| | 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) BINARY | bigint(10) |
|---|
| 4AF21D11E66D5A9830B4297CFD7A78F6 | 128 |
|
|
|
|
| hex(aes_encrypt(repeat('Test', 5), sha2('Key',512))) | |
| len |
|---|
| varchar(64) BINARY | bigint(10) |
|---|
| D07D851220021446BBA595ACCE20B2BB8C7A20F65BDEB36... | 256 |
|
|
|
|
| hex(aes_encrypt(repeat('Test', 10), sha2('Key',512))) | |
| len |
|---|
| varchar(96) BINARY | bigint(10) |
|---|
| D07D851220021446BBA595ACCE20B2BBD07D85122002144... | 384 |
|
|
|
|
| hex(aes_encrypt(repeat('Test', 20), sha2('Key',512))) | |
| len |
|---|
| varchar(192) BINARY | bigint(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 |
| 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) BINARY | int(10) |
|---|
| 4AF21D11E66D5A9830B4297CFD7A78F6 | 16 |
|
|
|
|
| 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: | 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. | | Further MySQL und MariaDB SQL Encryption & Compression | |
| | | More information about the AES_ENCRYPT SQL function: and |
|
|
|
|
