MySQL AES_DECRYPT() function

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

AES_DECRYPT() function

MySQL AES_DECRYPT() function decrypts an encrypted string using AES algorithm to return the original string. It returns NULL if detects invalid data.

Syntax:

AES_DECRYPT(crypt_str, key_str);

Arguments:

Name	Description
crypt_str	An encrypted string.
key_str	String to use to decrypt crypt_str.

Syntax Diagram:

MySQL Version: 5.6

Example:

Code:

SELECT   AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'),
'mykeystring');

Explanation:

The above MySQL statement decrypts the encrypted string 'mytext' using mykeystring and returns the original string mytext.

Sample Output:

mysql> SELECT AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'),'mykeystring'); +----------------------------------------------------------------+ | AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'),'mykeystring') | +----------------------------------------------------------------+ | mytext | +----------------------------------------------------------------+ 1 row in set (0.00 sec)

Sample table: testtable

Code:

SELECT description, AES_DECRYPT(description,'passw') FROM testtable;

Explanation:

The above MySQL statement retrieves the decrypted data from encrypted 'description' column from 'testtable'.

Sample Output:

mysql> SELECT description, AES_DECRYPT(description,'passw') -> FROM testtable; +---------------------------+----------------------------------+ | description | AES_DECRYPT(description,'passw') | +---------------------------+----------------------------------+ | ^5[@·˜,IÜç¦Éý | mytext | | Ô£^]Žþª_‹m | NULL | | ÿ»(õ 2ñ«QèªöjD¸=ËTú9Ž! | NULL | +---------------------------+----------------------------------+ 3 rows in set (0.04 sec)

PHP script:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>example-aes_decrypt - php mysql examples | w3resource</title> </head> <body> <?php echo "<h2>Retrieve decrypted data from encrypted 'description' column from 'testtable' : </h2>"; echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>"; echo "<tr style='font-weight: bold;'>"; echo "<td width='100' align='center'>Year and week of 2007-12-31</td><td width='100' align='center'>Year and week of 2008-01-01</td>"; echo "</tr>"; include("../dbopen.php"); $result = mysql_query("SELECT description, AES_DECRYPT(description,'passw') FROM testtable"); while($row=mysql_fetch_array($result)) { echo "<tr>"; echo "<td align='center' width='200'>" . $row['description'] . "</td>"; echo "<td align='center' width='200'>" . $row["AES_DECRYPT(description,'passw')"] . "</td>"; echo "</tr>"; } echo "</table>"; ?> </body> </html>

Online Practice Editor:

Previous: YEARWEEK()
Next: AES_ENCRYPT()