Php mysql LOWER function with AES_DECRYPT

Question

I have AES encrypted data stored in the database using php mysql. Which I entered using AES_ENCRYPT. Problem is that data is coming from iphone and "first name" of member is coming with first letter in UPPER CASE and stored as Upper. For examle 'Pooja'

I am wondering when I go for searching the following then it does not give me any result.

SELECT *
FROM member
WHERE LOWER(AES_DECRYPT(fname,'xxxxxxxxxxxxxxxxx')) LIKE'%$pooja%'

It gives me 'Pooja' when I dont use LOWER.

Can anybody please correct me here ??

Answer 1

I had the same problem. I was trying to search for an email address in my DB. I had the user [email protected] in my member_table.

SELECT LOWER( AES_DECRYPT( email_address, 'my_key' ) USING 'utf8' )
FROM  `member_table` 
WHERE LOWER( AES_DECRYPT( email_address,  'my_key' ) USING 'utf8' ) LIKE '[email protected]'

However, this did not worked because it always returned me the email address in upper case [email protected] despite the lower function and if a member inserted its email address as email_address.com that query wasn't able to find it.

So the solution was to use CONVERT( AES_DECRYPT( email_address, 'my_key' USING 'utf8' ). This allows me to find a member in an insensitive way (upper, lower or mixed) and I don't even need the LOWER function to compare. See example below:

SELECT LOWER( CONVERT( AES_DECRYPT( email_address, 'my_key' ) USING 'utf8' ) )
FROM  `member_table` 
WHERE CONVERT( AES_DECRYPT( email_address,  'my_key' ) USING 'utf8' ) LIKE '[email protected]'

The problem relates to: AES_ENCRYPT() encrypts the string str using a key key_str and returns a binary string containing the encrypted output, which does not work with LOWER function (As documented in mysql documentation here: https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-decrypt)

Answer 2

Are you sure that you want to have the %$ at the beginning? As others have said, LOWER should ignore case, so try this:

SELECT * FROM member WHERE AES_DECRYPT(fname,'xxxxxxxxxxxxxxxxx') LIKE 'pooja%';

% matches zero or more arbitrary characters, so this query should match all of the following

pooja
poojaaaa
pooja foo pooja bar

but not

pooj
pooj a
apooja
 pooja