UPPER(AES_DECRYPT(...)) bug?

[Philip Thompson]

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?

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

[Roberto Mansfield]

Post by Philip Thompson
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
SELECT UPPER('just a lower case string') AS `UPPER_STRING`,
UPPER(AES_DECRYPT(AES_ENCRYPT('Bob Frapples', '1234ABCD'), '1234ABCD'))
AS `UPPER_NAME`;
UPPER_STRING UPPER_NAME
------------------------ ------------
JUST A LOWER CASE STRING BOB FRAPPLES
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

[sandipt]

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

Post by Roberto Mansfield
The bug report is correct. This is not a bug. The docs clearly state at
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

[pushkar]

Hi Philip Thompson,

You better use
SELECT UPPER('just a lower case string') AS `UPPER_STRING`,
UPPER(CONVERT(AES_DECRYPT(AES_ENCRYPT('Bob Frapples', '1234ABCD'),
'1234ABCD')USING latin1)) AS `UPPER_NAME`;