CODEWITHSUNDEEP
mysql
pheromix
pheromix

Reputation: 19297

How to perform a LIKE search with encrypted data?

The data in my column , lib_question , are encrypted using AES_ENCRYPT. Now I want to search records from the table where this column is LIKE a particular entry typed by the user. I tried this but it does not work :

select id_question 
from question 
where lib_question like CONCAT('%',AES_ENCRYPT('contribuer', HEX('AVtr34ENMG')),'%');

I remember entering the word contribuer into the field corresponding to the lib_question column through the application.

So how to make the LIKE statement ?

Upvotes: 2

Views: 1822

Answers (1)

trs
trs

Reputation: 1127

AES_ENCRYPT will add padding around the input string (https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes-encrypt), which means your comparison will never match on the encrypted strings.

Have you tried comparing the decrypted values? That should be more reliable.

select id_question 
from question 
where AES_DECRYPT(lib_question, HEX('AVtr34ENMG')) like '%contribuer%';

Upvotes: 6

Related Questions