Re: UPPER(AES_DECRYPT(...)) bug?

Philip Thompson wrote: > Hi all. > > Here's my disclaimer: this question is solely MySQL-related - I just > happen to be programming in PHP. > > I have found some functionality which *appears* to be a bug, but I > didn't want to report it before asking some intelligent people. I have > this simple query: > > SELECT UPPER('just a lower case string') AS > UPPER_STRING, > UPPER(AES_DECRYPT(AES_ENCRYPT('Bob Frapples', '1234ABCD'), > '1234ABCD')) > AS UPPER_NAME; > > Expected result: > > UPPER_STRING UPPER_NAME > ------------------------ ------------ > JUST A LOWER CASE STRING BOB FRAPPLES > > Real result: > > UPPER_STRING UPPER_NAME > ------------------------ ------------ > JUST A LOWER CASE STRING Bob Frapples > > > There is a similar bug report > <http://bugs.mysql.com/bug.php?id=28072> > that uses LCASE instead of UPPER. But, they have the same *lack of > desired functionality*. MySQL people say it's not a bug due to how > binary blah blah blah. Whatever - I don't buy that. It's not working *as > it should*. On top of that, the docs for this encrypt stuff is quite > minimal. > > My questions to you: do you think this is a bug? And... is there an > alternative to searching on fields that are encrypted using the LIKE > operator? The bug report is correct. This is not a bug. The docs clearly state at the top of the page: The encryption and compression functions return binary strings. If you need a normal string, try the cast() or convert() function to convert your binary string before comparing or using it in a function. -Roberto