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

(PHP variable) $query = (MYSQL statement string) – user963206 Sep 30 '11 at 16:34 — user963206, Sep 30 '11 at 16:34

score 10 · Accepted Answer · answered Sep 30 '11 at 16:39

10

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.

answered Sep 30 '11 at 16:39

ceejayoz

176,543
40
303
368

Tried it but wont work but I think your on the right track... (slow is not a issue as small web app) – user963206 Sep 30 '11 at 16:52
I'll 're·phrase' the above comment - You are a LEGEND. I had a minor '=' in the way, thank you. – user963206 Sep 30 '11 at 17:00
This is not working, can anyone suggest any other way to decrypt the values. – Vicky Dec 21 '15 at 10:05
@Vicky If this isn't working you have a different problem, and should start your own question, with example code and other details. – ceejayoz Dec 21 '15 at 13:47

score 6 · Answer 2 · edited Oct 26 '12 at 14:41

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)."%'";

MYSQL SELECT WHERE LIKE WITH AES_ENCRYPT

2 Answers2

Linked

Related