MYSQL SELECT WHERE LIKE WITH AES_ENCRYPT

Question

How would I perform a Mysql SELECT with WHERE and LIKE serach if field is AES_ENCYPTED?

Example:

SELECT AES_DECRYPT(place,'"+salt+"'),AES_DECRYPT(web_address,'"+salt+"') 
FROM access 
WHERE place= LIKE '%(AES_ENCRYPT('"+searchStr+"','"+salt+"'))',%')

Basically, perform a search on an encrypted column with the LIKE wildcard on both ends of the $searchStr

Solution 1

You can't search on an encrypted column without first decrypting it.

You'll need to do WHERE AES_DECRYPT(like, salt) LIKE '%something%' but it's going to be quite slow.

Solution 2

I have been looking for a simple way to use the SELECT LIKE for an AES_ENCRYPTED field with MySQL. The one that works the best is:

SELECT * FROM table 
WHERE CONVERT(AES_DECRYPT(`haystack`,'key') USING utf8) LIKE '%needle%'

I have tested this on MySQL 5 using PHP 5.

This runs very well when processing several thousand rows, but may not be ideal for very large tables due to the decryption and conversion.

This is the basic PHP code:

$key   = md5("yourchosenkey".$salt);     
$query = "SELECT * FROM ".$tableName." ". 
         "WHERE CONVERT(AES_DECRYPT(`haystack`,'".$key."') USING utf8) ".
         "LIKE '%".addslashes($needle)."%'";

Comments

[Admin]

Tried it but wont work but I think your on the right track... (slow is not a issue as small web app)

[Admin]

I'll 're·phrase' the above comment - You are a LEGEND. I had a minor '=' in the way, thank you.

[Vicky]

This is not working, can anyone suggest any other way to decrypt the values.

[ceejayoz]

@Vicky If this isn't working you have a different problem, and should start your own question, with example code and other details.