MySQL Encryption and Compression Functions

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

MySQL AES_DECRYPT() function

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.

Example :

Code :

  1. SELECT   AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'),  
  2. 'mykeystring');  

Explanation

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

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

description
^[@ú~,Iš‡ݐy
Oœ??^]Z_¦_<m
˜¯(o 2¤®QŠ¦”jD,=ET£9Z!
^[@£~,IsØY?y
Oo??^]Z_Ý_<m
~_(o 2rQSÝ"jD,=ETœ9Z!
^[@ú~,Iš‡ݐy

Code

  1. SELECT description, AES_DECRYPT(description,'passw')   
  2. FROM testtable;  

Explanation

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

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" 
<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>