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

Answer 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.

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