CODEWITHSUNDEEP
mysqlaes
Ahmed
Ahmed

Reputation: 1533

MySQL AES_Decrypt not working

I've been trying so many different variations to get AES_Decrypt to work. I started of with field types VARBINARY then tried BLOB but still no luck, keeps returning NULL. Eventually I tried this:

SELECT AES_DECRYPT(AES_ENCRYPT('blah blah blah','1234'), '1234') as test

This technically should have returned blah blah blah, but instead it returns 

 626c616820626c616820626c6168

So not sure what is going on or what I am doing wrong.

I have inserted encrypted data as follows:

INSERT INTO private (short_name, mobile, name) 
VALUES (
  'AS1', 
  AES_ENCRYPT('0111222333','1234'), 
  AES_ENCRYPT('My Name','1234')
)

Then I try to decrypt it like this:

SELECT AES_DECRYPT('mobile', '1234') AS mobile FROM private

It just does not seem to work. Followed lots of the instructions online but still no luck.

Any ideas?

Upvotes: 0

Views: 3507

Answers (2)

Ahmed
Ahmed

Reputation: 1533

So it seems that I need to use CAST unless using the mysql command line client.

So doing this seems to work:

SELECT CAST(AES_DECRYPT(AES_ENCRYPT('blah blah blah','1234'), '1234') AS CHAR (150))

Upvotes: 2

Bhushan
Bhushan

Reputation: 595

SELECT AES_DECRYPT(AES_ENCRYPT('blah blah blah','1234'), '1234') as test

is returning binary string as output. If you convert it to string then it will blah blah blah. You can use online hex to binary converter to verify it. You have to set it to text in your GUI tool.

Upvotes: 0

Related Questions