case insensitive search in aes_encrypted column?

[calibi.yau]

Hi all

 

I'm really hoping someone here can help me out with this one...

 

I'm trying to search through a table via php but having difficulty with case - The problem is the data I'm searching through is encrypted (aes_encrypt) and it doesn't seem to matter what I do the search is case sensitive - I want the search to ignore case.

 

Here's the jist of what I have:

 

$query = "SELECT id, username, aes_decrypt(lastname,'$vark') AS lastname FROM $dbtable WHERE lastname=aes_encrypt('$lastnameSearch','$vark') ORDER BY id DESC" ;

 

It doesn't seem to matter what I do, I'll only get a match when the value of $lastname matches the case of the original entry. I've tried everything I can think of... LIKE, UPPER, LCASE, BINARY, changing everything to uppercase, to lowercase, decrypting the column data as opposed to encrypting $lastname then comparing...

 

I'd like for users not to have to get the case correct when doing a search (kind of defeats the purpose of a search).

 

I'm really hoping that I don't have to force the original data to a single case when encrypting it in the first place.

 

Thanks in advance.

 

[Dennis1986]

What field type is lastname ?

AES doesn't like varchar fields, try with blob instead.

[calibi.yau]

What field type is lastname ?

AES doesn't like varchar fields, try with blob instead.

 

Hey Dennis1986, thanks for the reply... Ya, unfortunately for me though it won't be that easy - the lastname field type is BLOB

[calibi.yau]

Okay, so because the aes_encrypt is binary, and the lastname field type is binary (blob), my comparisons will automatically be case sensitive.

 

Converting from binary to a case insensitive type should solve my problem... but it's still not working for me... is my syntax wrong?

 

$query = "SELECT id, username, CONVERT(aes_decrypt(lastname,'$vark') USING latin1) AS lastname FROM $dbtable WHERE CONVERT(lastname USING latin1) LIKE CONVERT(aes_encrypt('$lastnameSearch','$vark') USING latin1) ORDER BY id DESC" ;

 

Any help would be greatly appreciated. Thanks!