CODEWITHSUNDEEP
phpmysqldatabase
vikas
vikas

Reputation: 735

Php mysql LOWER function with AES_DECRYPT

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 ??

Upvotes: 3

Views: 2946

Answers (3)

Sergio
Sergio

Reputation: 310

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)

Upvotes: 6

webster
webster

Reputation: 1

Had this problem too a few minutes ago. Have you tried to convert the decrypted string using CONVERT(my_decrypted_string USING 'latin1')?

AES_DECRYPT seems to return a binary string or something like that. With this type LOWER() does not work:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower

Upvotes: 0

middus
middus

Reputation: 9121

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

Upvotes: 1

Related Questions