Bug #28072 lcase function fails when parameter is aes_decrypt
Submitted: 24 Apr 2007 13:16 Modified: 24 Apr 2007 13:48
Reporter: Aurelien Marchand Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.26-log OS:Linux (Gentoo)
Assigned to: CPU Architecture:Any
Tags: AES_DECRYPT, fail, lcase, LOWER, UNHEX

[24 Apr 2007 13:16] Aurelien Marchand
The lcase function fails when processing the result of aes_decrypt. It works properly otherwise.

How to repeat:
If you try the following query:
SELECT lcase(aes_decrypt(unhex('A9C5DE18F48FDED71B882DAEFC6B4B114C49C97B27145252F88AE2309224DCE7'),'thisisapassword'));

it will return a string ("TESTEMAIL@EXAMPLE.COM") in uppercase even though you passed it through lcase().

Running "SELECT lcase('TESTEMAIL@EXAMPLE.COM')" does return the proper result "testemail@example.com".

Suggested fix:
make sure lcase converts strings to lowercase even though the string might not end with \0 (but I can't say for sure that's the issue)
[24 Apr 2007 13:48] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Thank you for the report.

But output of aes_decrypt  has binary collation. So behaviour is expected.

Examine, for example, output of mysql --debug command:

$mysql -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 354
Server version: 5.0.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT aes_decrypt(unhex('A9C5DE18F48FDED71B882DAEFC6B4B114C49C97B27145252F88AE2309224DCE7'),'thisisapassword');
Field   1:  `aes_decrypt(unhex('A9C5DE18F48FDED71B882DAEFC6B4B114C49C97B27145252F88AE2309224DCE7'),'thisisapassword')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     32
Max_length: 21
Decimals:   31
Flags:      BINARY 

| aes_decrypt(unhex('A9C5DE18F48FDED71B882DAEFC6B4B114C49C97B27145252F88AE2309224DCE7'),'thisisapassword') |
| TESTEMAIL@EXAMPLE.COM                                                                                    | 
1 row in set (0.00 sec)