CODEWITHSUNDEEP
mysqlsqlaes
user612703
user612703

Reputation: 83

LIKE query with AES_DECRYPT not working

I have a table containing several ENCRYPTED blob fields. The data was encrypted with AES_ENCRYPT. I am now trying to generate ajax suggestions from these fields.

The following query does not work

    SELECT id
           , AES_DECRYPT(first_field,'secret_salt')
           , AES_DECRYPT(second_field,'secret_salt')
    FROM   table
    WHERE  ( AES_DECRYPT(first_field,'secret_salt')  "%user search value%" 
       OR    AES_DECRYPT(second_field,'secret_salt') LIKE "%user search value%"
           ) 
      AND  status = 1

I have been searching and trying for the last 3 days. The following query does work, but is not according to my needs

    SELECT id
           , AES_DECRYPT(first_field,'secret_salt')
           , AES_DECRYPT(second_field,'secret_salt') 
    FROM   table
    WHERE  status = 1

I found this query but it does not work either.

    SELECT  id, AES_DECRYPT(first_field,'secret_salt'), AES_DECRYPT(second_field,'secret_salt')
    FROM    table
    WHERE   first_field LIKE "%AES_ENCRYPT('user search value','secret_salt')%"

Upvotes: 2

Views: 2315

Answers (1)

maplechori
maplechori

Reputation: 71

Try using

WHERE CAST(AES_DECRYPT(first_field, 'secret_salt') AS CHAR) LIKE '%foo%'

Upvotes: 6

Related Questions