MySQL Bugs: #8564: ResultSet returns string of previous record if string in current record is null

Bug #8564	ResultSet returns string of previous record if string in current record is null
Submitted:	17 Feb 2005 9:35	Modified:	17 Feb 2005 15:35
Reporter:	Ralf Hauser
Status:	Can't repeat
Category:	Connector/J	Severity:	S2 (Serious)
Version:	3.1.6	OS:	Microsoft Windows (winXP, debian, redhat9)
Assigned to:		Target Version:

Description:
When retrieving null strings from the DB, the jdbc simply takes the value from a previous
record where the string was not null.
One peculiarity is that the column is encrypted.
Also, the latest commons-dbcp-1.2.1.jar is used. Thus when stepping through in eclipse, I
see that the java.sql.ResultSet really is a org.apache.commons.dbcp.DelegatingResultSet -
so perhaps the error is there?

selDraftStmt =
	conn.prepareStatement(
		"SELECT TBL_SENDER.msg_id, "
                ...
		+ " AES_DECRYPT(TBL_SENDER.subject,?) AS subject, "
                ...
		+ " FROM  TBL_SENDER
                ...
		+ " WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id" ...);
			selDraftStmt.setString(1, Globals.getInstance().getAESkey());
                ...
		log.debug(
			"selDraftStmt: "
						+ ((DelegatingPreparedStatement) selDraftStmt)
							.getDelegate()
							.toString());
			}
		java.sql.ResultSet crs = selDraftStmt.executeQuery();
                ...

msgHeader.setSubject(crs.getString("subject"));

excerpt from the scheme:
CREATE TABLE TBL_SENDER (
...
subject        CHAR(255) NOT NULL,

but probably, since AES_ENCRYPT is done, the DBMS doesn't notice that the content is null
anyway!

How to repeat:
see the description in http://jira.codehaus.org/browse/DISPL-191

Suggested fix:
return a zero length string ""

Don't think it is really related to http://lists.mysql.com/java/5141

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

We would need the full schema, or a schema and data that makes the testcase repeatable.
Can you reproduce this behavior outside of the connection pool with a standalone
connection?

The JDBC driver has a testsuite that exercises this functionality of result set
navigation quite completely, as well as the fact that 3.1.6 has been in production for
some time, and we have received no bug reports like this before...Seeing as how this
looks pretty serious, if this bug was in the JDBC driver, we should have heard about it
by now.

Without seeing your schema, and the type of data in it, we can't test your particular
situation.

Mark, 
It appears that the main problem is rather on the mysqld side than in connector/J.
I hope I don't waste your time with perhaps even a sql query error on my side, but here
it goes:
SELECT TBL_SENDER.msg_id,  AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject 
FROM TBL_SENDER, TBL_MESSAGE WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id AND
(TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147) ORDER BY  TBL_SENDER.msg_id DESC;

produces the problem based on two records only.

SELECT TBL_SENDER.msg_id,  AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject 
FROM TBL_SENDER, TBL_MESSAGE WHERE TBL_SENDER.msg_id = TBL_MESSAGE.msg_id AND
(TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147);
correctly shows 6147 as null and 6123 with the subject string.
So does 
SELECT TBL_SENDER.msg_id,  AES_DECRYPT(TBL_SENDER.subject,'q2s3d4f5g6o0!;#[') AS subject 
FROM TBL_SENDER WHERE  (TBL_SENDER.msg_id = 6123 OR TBL_SENDER.msg_id = 6147) ORDER BY 
TBL_SENDER.msg_id DESC;

I am using mysqld  Ver 4.0.21-log for pc-linux-gnu on i386 (Source distribution)
on debian. This can also be reproduced with other clients such as the old MySQLFront or
Squirrel 1.2beta6.

When trying to build a sample program to reproduce it with connetor/J, correctly, I was
not able enter the null value even though it was to be encrypted (so this is probably
another error that the null value originally got into my DB, but I haven't found a
reproducible input data yet).

So, the next idea was to simply insert the cypher-text without encrypting it in order to
create you the test data out of the .java file.
Unfortunately, no luck there - despite binding it in a prepared statement array, it
apparently contained characters the jdbc doesn't escape.
Temporarily encoding it didn't help either - so:
String aesEncVal = "%EF%BF%AF%13%EF%BF%90%2F%06Wx%03%EF%BE%92%0F%EF%BF%9E%EF%BF%8F%"
                            +
"04%EF%BF%A6%EF%BE%82%EF%BE%AEk%17%EF%BF%991%EF%BF%AE%EF%BF%8Cch%EF%BF%B2"
                            +
"%EF%BF%97%21%EF%BE%8C%EF%BF%90D%EF%BF%9C%09%3B%EF%BE%90%EF%BF%96n%EF%BE%91"
                            + "%5B%EF%BF%80%0A%3B%EF%BF%BA%EF%BE%BB%3Ad%EF%BF%AB%2F";
insSenderStmt = conn.prepareStatement(
                            "INSERT INTO bug8564  subject " + " VALUES ?);",
                            Statement.RETURN_GENERATED_KEYS);
insSenderStmt.setString(1, URLDecoder
                               .decode(aesEncVal, "UTF-8"));
retVal = insSenderStmt.executeUpdate();
would not insert, but complain about an invalid query. So far I thought that any string
should be insertable with a prepared statement, but obviously I am wrong here or this is
only valid for iso8859-1, but not utf-8?
Any hints would be highly appreciated! Ralf

I am happy to send you the relevant parts of my tables to reproduce. The DB overall is
unfortunately several 100 MB. The problem is: how do I export it such delicate contents
in a way that it doesn't get converted to 7bit or alike?
Also, I would assume that migrating to mysqld 4.023 wouldn't make a big difference?

ok, got around fixing the cryptogram insert issue, the problem is now reproducible as in
http://bugs.mysql.com/8669