Encrypt and Decrypt a string in MySQL?

To encrypt and decrypt in MySQL, use the AES_ENCRYPT() and AES_DECRYPT() in MySQL −

insert into yourTableName values(AES_ENCRYPT(yourValue,yourSecretKey));

select cast(AES_DECRYPT(yourColumnName, yourSecretKey) as char) from
yourTableName;

To understand the above syntax, let us first create a table −

mysql> create table demo63
−> (
−> value blob
−> );
Query OK, 0 rows affected (2.60 sec)

Insert some records into the table with the help of insert command. We are encrypting while inserting −

mysql> insert into demo63 values(AES_ENCRYPT('John','PASS'));
Query OK, 1 row affected (0.18 sec)

mysql> insert into demo63 values(AES_ENCRYPT('David','PASS'));
Query OK, 1 row affected (0.41 sec)

mysql> insert into demo63 values(AES_ENCRYPT('123456','PASS'));
Query OK, 1 row affected (0.41 sec)

Display records from the table using select statement −

mysql> select *from demo63;

This will produce the following output −

+------------------------------------+
| value                              |
+------------------------------------+
| 0x16D8A4F11321D761920783BF96BB8314 |
| 0x0A85099F705F21D27B0129C54C4473AA |
| 0xAB7C6D6068BC100B0F04D1C4EA068AC9 |
+------------------------------------+
3 rows in set (0.00 sec)

Following is the query to decrypt −

mysql> select cast(aes_decrypt(value, 'PASS') AS char) from demo63;

This will produce the following output −

+------------------------------------------+
| cast(aes_decrypt(value, 'PASS') AS char) |
+------------------------------------------+
| John                                     |
| David                                    |
| 123456                                   |
+------------------------------------------+
3 rows in set (0.00 sec)

AmitDiwan

Updated on: 20-Nov-2020

6K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started