如何加密数据以便使用LIKE执行SQL查询？

Question

Please be patient, I'm not an expert about cryptography. My question is probably very basic but I googled a lot and I'm still confused.

In a PHP project, I need to encrypt/decrypt the data saved in the database. In a previous project I used the aes128 encryption and everything went well. But now I have a different need. I need to perform queries in the database using the operator LIKE. And obviously the encryption of a portion of a string is not included in the encryption of the whole string.

Googling around, I realized that maybe I have to use symmetric-key algorithm (like the Caesar's cipher). But I did a test with the php-encryption library (https://github.com/defuse/php-encryption) and I got the following result:

MAMMA = ÿŸNq!!83=S™÷á;Bª¯‚óØ š‹ æ%§0 %? _† Ÿ&0c—âÐÜÉ/:LSçï; յ嬣§.öÒ9

MAMMAMIA = (Ò Î{yG : [&¶›J'Õ6÷ííG£V­­{ÉsÙ=qÝ×.:ÍÔ j…Qž¹×j¶óóþ¡ÔnÛŠ *å­n\hhN

The encryption of the first word is not included in the encryption of the second. Evidently the simmetric algorithm is not right for my need.

What I can use to reach my goal using PHP? Thanks!

Answer 1

The easiest way is to use mysql encrypt/decrypt functionality and do both "on the fly".

To insert and encrypt data:

insert into mytable (secret) values AES_ENCRYPT('SomeTextToHide','myPassword');

To search for encrypted values using like

select * from mytable where AES_DECRYPT(secret,'myPassword') like '%text%';