Unable to AES_DECRYPT after AES_ENCRYPT in mysql

Question

I created user table

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

I inserted one row:

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));

To select this row i used:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;

I am getting the following result.What i need to do see my data.No data is visible for me.

I am unable to see my data

Is there any secured way to encrypt my data without any key. — PSR, May 15 '13 at 03:38
try casting, `CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50))` and see if it works. — John Woo, May 15 '13 at 03:39
i have no idea what's the reason sorry. anyway, try: `select *, CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt from user` and use `first_name_decrypt` instead of `first_name`. — John Woo, May 15 '13 at 03:42
I am not using mysql to encrypt the data. I do it in thge application level `:)` — John Woo, May 15 '13 at 03:44

score 39 · Accepted Answer · edited Jan 04 '16 at 12:32

39

According to the Manual:

AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

MySQL 5.1 Doc: AES_ENCRYPT() / AES_DECRYPT()

I don't know why it is still returning a binary string in your case. Anyway, try this:

SELECT *, 
       CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt 
FROM   user

And use first_name_decrypt instead of first_name.

edited Jan 04 '16 at 12:32

pouyan021

177
1
4
19

answered May 15 '13 at 03:51

John Woo

258,903
69
498
492

1

Does the same on our server (5.5) – Michael M Jan 06 '14 at 19:20
2

Caution: `(50)` may work for this example, but not be long enough for others. – Rick James May 04 '18 at 18:07

score 16 · Answer 2 · edited Apr 23 '19 at 12:20

From mysql command line client there is no need to use CAST:

mysql> SELECT AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc');

+-----------------------------------------------+
| AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') |
+-----------------------------------------------+
| admin                                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50));

+------------------------------------------------------------------+
| CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50)) |
+------------------------------------------------------------------+
| admin                                                            |
+------------------------------------------------------------------+
1 row in set (0.02 sec)

As you can see using cast in command line is little bit slower. But I have noticed that if you use some tools like phpmyadmin, then you need to use CAST, otherwise result will be wrong.

score 0 · Answer 3 · answered Aug 27 '20 at 13:18

I had the same issue, turns out I had my column data type set as VARCHAR when I encrypted the data and when I switched over to MEDIUMTEXT, it wouldn't return anything else than a BLOB.

Quoting a part from dev.mysql:

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

Switching to a supported column type worked for me.

score -3 · Answer 4 · edited May 23 '18 at 10:32

-3

if (isset($_POST['user_name']) and isset($_POST['user_password'])){

$user_name = $_POST['user_name'];
$user_password = $_POST['user_password'];


$query = "SELECT * FROM `user_tbl` WHERE user_name='$user_name' and AES_DECRYPT(user_password , '@ert') = '$user_password'";

$result = mysqli_query($connection, $query) or die(mysqli_error($connection));
$count = mysqli_num_rows($result);

edited May 23 '18 at 10:32

ADyson

57,178
14
51
63

answered May 23 '18 at 10:11

Dhivya

1

4

code without an explanation is not really very useful. Please explain what are the important parts, and why it answers the question. – ADyson May 23 '18 at 10:33
1

Additionally, that code is widely open to SQL injections. One should definitely not use it anywhere – Nico Haase Apr 23 '19 at 15:08

Unable to AES_DECRYPT after AES_ENCRYPT in mysql

4 Answers4

Linked

Related