MySQL Bugs: #36347: Order by on decrypted data appears to be case sensitive

Bug #36347	Order by on decrypted data appears to be case sensitive
Submitted:	25 Apr 2008 14:46	Modified:	25 Apr 2008 16:31
Reporter:	Mike Harris	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	lowercase, order by, sort, uppercase

Description:
When using an order by clause on a select statement filtering data that is being decrypted on the fly a distinction appears to be made between upper and lower case characters. This behaviour does not occur on none encrypted data.

How to repeat:
create table `tblforename` (
	`ID` double ,
	`fForename` varchar (750)
); 
insert into `tblforename` (`ID`, `fForename`) values('1', 'alice' );
insert into `tblforename` (`ID`, `fForename`) values('2', 'Bob' );
insert into `tblforename` (`ID`, `fForename`) values('3', 'charlie' );
insert into `tblforename` (`ID`, `fForename`) values('4', 'Dave' );

select ID, fForename from tblforename order by fForename

... gives ...

ID | fForename
==============
1  | alice
2  | Bob
3  | charlie
4  | Dave

... however ...

create table `tblencryptedforename` (
	`ID` double ,
	`fForename` varchar (750)
); 
insert into `tblencryptedforename` (`ID`, `fForename`) values('1', aes_encrypt('alice', 'key') );
insert into `tblencryptedforename` (`ID`, `fForename`) values('2', aes_encrypt('Bob', 'key') );
insert into `tblencryptedforename` (`ID`, `fForename`) values('3', aes_encrypt('charlie', 'key') );
insert into `tblencryptedforename` (`ID`, `fForename`) values('4', aes_encrypt('Dave', 'key') );

select ID, aes_decrypt(fForename, 'key') as fdecryptedname from tblencryptedforename order by fdecryptedname

... gives ...

ID | fdecryptedname
===================
2  | Bob
4  | Dave
1  | alice
3  | charlie

Thank you for the bug report. It is documented bahavior these functions return
binary values:

http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

"Note

The encryption and compression functions return binary strings. For many of these functions,...."

c:\dbs>5.0\bin\mysql -uroot db3 -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.60-nt Source distribution

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

mysql> select ID, aes_decrypt(fForename, 'key') as fdecryptedname from tblencryptedforename order
    -> by fdecryptedname;
Field   1:  `ID`
Catalog:    `def`
Database:   `db3`
Table:      `tblencryptedforename`
Org_table:  `tblencryptedforename`
Type:       DOUBLE
Collation:  binary (63)
Length:     22
Max_length: 1
Decimals:   31
Flags:      NUM

Field   2:  `fdecryptedname`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     750
Max_length: 7
Decimals:   31
Flags:      BINARY

+------+----------------+
| ID   | fdecryptedname |
+------+----------------+
|    2 | Bob            |
|    4 | Dave           |
|    1 | alice          |
|    3 | charlie        |
+------+----------------+
4 rows in set (0.00 sec)

mysql> select ID, fForename from tblforename order by fForename
    -> ;
Field   1:  `ID`
Catalog:    `def`
Database:   `db3`
Table:      `tblforename`
Org_table:  `tblforename`
Type:       DOUBLE
Collation:  binary (63)
Length:     22
Max_length: 1
Decimals:   31
Flags:      NUM

Field   2:  `fForename`
Catalog:    `def`
Database:   `db3`
Table:      `tblforename`
Org_table:  `tblforename`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     750
Max_length: 7
Decimals:   0
Flags:

+------+-----------+
| ID   | fForename |
+------+-----------+
|    1 | alice     |
|    2 | Bob       |
|    3 | charlie   |
|    4 | Dave      |
+------+-----------+
4 rows in set (0.00 sec)

mysql>