UPPER(AES_DECRYPT(...)) bug?

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

Thanks,
~Philip


"Personally, most of my web applications do not have to factor 13.7
billion years of space drift in to the calculations, so PHP's rand
function has been great for me..." ~S. Johnson

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

must be kidding.. of course its a bug !! The docs clearly state as "The
encryption returns binary string" and if you notice function discussed here
is for Decryption...

Refer :
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions. html#function_aes-encrypt

It clearly says, "AES_DECRYPT() decrypts the encrypted string and returns
the original string." If AES_DECRYPT returns plain string and UPPER, LOWER,
LCASE etc functions are accepting plain string as parameter then it must
behave the way Philip mentioned... I personally spent 4-5 hours figuring
out what can I do to make it work right but nothing... I want to apply it
in search functionality but data is encrypted using AES_ENCRYPT.. so after
decrypting when I try to bring both side of "=" in where condition to either
lower or upper in order to give broader search result, it gives nothing
expected...

So Yes !! It is a bug for sure !!

btw, Philip did you get any workaround for this problem ? As I have to limit
my search to exact case match as of now which I don't want.. Any suggeston
appreciated...

Thanks


Roberto Mansfield wrote:
>
>
> 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.
>
> -Roberto
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

--
View this message in context: http://old.nabble.com/UPPER%28AES_DECRYPT%28...%29%29-bug--t p16425571p28287901.html
Sent from the Php - Database mailing list archive at Nabble.com.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php