Topic: 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!