CODEWITHSUNDEEP
phpsqlalgorithmencryptionencryption-symmetric
Gianluca78
Gianluca78

Reputation: 784

How to encrypt data so as to perform SQL queries with LIKE?

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!

Upvotes: 1

Views: 1226

Answers (2)

Anti-weakpasswords
Anti-weakpasswords

Reputation: 2712

The goal of being able to use a LIKE clause and its wildcards on securely encrypted data is a set of two mutually exclusive desires.

You can securely encrypt your data, OR you can use a LIKE clause with wildcards. You cannot do both at once, because the LIKE clause itself would bypass the encryption!

SELECT * WHERE data LIKE 'a%'
SELECT * WHERE data LIKE 'b%'
...
SELECT * WHERE data LIKE '_a%'
SELECT * WHERE data LIKE '_b%'
...
SELECT * WHERE data LIKE '__a%'
SELECT * WHERE data LIKE '__b%'
...

If you securely encrypt the data in the database, then you need to pull ALL of it back local, decrypt it all, and then do your wildcard searches on the decrypted data.

Upvotes: 1

maxhb
maxhb

Reputation: 8855

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

Upvotes: 2

Related Questions