pheromix pheromix - 5 months ago 16
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

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%';