pheromix pheromix - 1 year ago 64
MySQL Question

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 ?

trs trs
Answer Source

AES_ENCRYPT will add padding around the input string (, 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%';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download