[SOLVED] using aes_decrypt, ws_concat in query

[ryan_z]

Hello, whatever I do I keep getting a 0 result.  Is what I'm trying to do possible? The query is working fine, once I add a where clause, it sems to break it.  Thank you in advance for any help.

 

SELECT id, concat_ws(' ',aes_decrypt(fname,'111111111111111111111111'), aes_decrypt(lname,'111111111111111111111111')) as fullname,
aes_decrypt(sid_1,'111111111111111111111111') as sid1,
class,
aes_decrypt(image,'111111111111111111111111') as image
FROM `4` where concat_ws(' ',aes_decrypt(fname,'111111111111111111111111'), aes_decrypt(lname,'111111111111111111111111'))
LIKE '%a%' LIMIT 10

[ryan_z]

I just figured out the issue.  The fields that are using encrypted data are set as varbinary.  I had to convert the data to a character set to search it.  Here's my corrected query:

 

SELECT id,
concat_ws(' ',aes_decrypt(fname,'111111111111111111111111'), aes_decrypt(lname,'111111111111111111111111')) as fullname,
aes_decrypt(stid_1,'111111111111111111111111') as sid1,
class,
aes_decrypt(image,'111111111111111111111111') as image
FROM `4` where
CONVERT(concat_ws(' ',aes_decrypt(fname,'111111111111111111111111'), aes_decrypt(lname,'111111111111111111111111')) USING utf8) collate utf8_general_ci
LIKE '%a%' LIMIT 10