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