Description:
This bug is causing us problems in the implementation of Sensitive customer information.
Versions
MySQL server is running under SuSE linux 10.0
mySQL version is
MySQL-server-standard-5.0.20a-0.sles9
MySQL-client-standard-5.0.20a-0.sles9
JDBC
mysql-connector-java-3.1.10
mysql-connector-java-5.0.0-beta
The column type coming back from a preparedstatement call with JDBC is incorrect if using a prepared statement and an order by in the select statement where there is a blob column. and calling function aes_decrypt. It seems also only to occur when the query has a JOIN. A simple select from one table does not seem to cause the problem.
See below for how to repeat.
How to repeat:
1) take two tables as defined as follows
create table bugfile(fileid bigint not null, filename blob, pathid bigint)
create table bugpath(pathid bigint not null, path varchar(254))
insert into bugpath (pathid, path) values (1, 'top')
insert into bugfile (fileid, filename, pathid) values (1, aes_encrypt('filename1', 'fish'), 1)
insert into bugfile (fileid, filename, pathid) values (2, aes_encrypt('filename2', 'fish'), 1)
insert into bugfile (fileid, filename, pathid) values (3, aes_encrypt('filename3', 'fish'), 1)
Now perform following select statement
SELECT aes_decrypt(FIC.filename,'fish') as FileName, FP.PATH
FROM bugFile FIC, bugPATH FP
WHERE FP.PATHId=FIC.PATHID
ORDER BY FIC.FileName ASC
create a small JAVA program to connect to the database and execute the statement
If you do this without a prepared statement then you get
filename1
filename2
filename3
as expected
If you then repeat in JAVA program with a prepared statement then you get
[B@15e83f9
[B@2a5330
[B@bb7465
If you now remove the Order By from the statement then the query works fine in both cases
Suggested fix:
Sorry - no suggestions
Description: This bug is causing us problems in the implementation of Sensitive customer information. Versions MySQL server is running under SuSE linux 10.0 mySQL version is MySQL-server-standard-5.0.20a-0.sles9 MySQL-client-standard-5.0.20a-0.sles9 JDBC mysql-connector-java-3.1.10 mysql-connector-java-5.0.0-beta The column type coming back from a preparedstatement call with JDBC is incorrect if using a prepared statement and an order by in the select statement where there is a blob column. and calling function aes_decrypt. It seems also only to occur when the query has a JOIN. A simple select from one table does not seem to cause the problem. See below for how to repeat. How to repeat: 1) take two tables as defined as follows create table bugfile(fileid bigint not null, filename blob, pathid bigint) create table bugpath(pathid bigint not null, path varchar(254)) insert into bugpath (pathid, path) values (1, 'top') insert into bugfile (fileid, filename, pathid) values (1, aes_encrypt('filename1', 'fish'), 1) insert into bugfile (fileid, filename, pathid) values (2, aes_encrypt('filename2', 'fish'), 1) insert into bugfile (fileid, filename, pathid) values (3, aes_encrypt('filename3', 'fish'), 1) Now perform following select statement SELECT aes_decrypt(FIC.filename,'fish') as FileName, FP.PATH FROM bugFile FIC, bugPATH FP WHERE FP.PATHId=FIC.PATHID ORDER BY FIC.FileName ASC create a small JAVA program to connect to the database and execute the statement If you do this without a prepared statement then you get filename1 filename2 filename3 as expected If you then repeat in JAVA program with a prepared statement then you get [B@15e83f9 [B@2a5330 [B@bb7465 If you now remove the Order By from the statement then the query works fine in both cases Suggested fix: Sorry - no suggestions