Ubuntu
mysql-dfsg-4.1 package

MySQL 4.1 Prepared Statements stopped working for Java/J2EE apps (Regression from Hoary)

Bug #3448 reported by Andreas Schildbach on 2005-10-21

This bug report is a duplicate of: Bug #36255: MySQL Prepared Statements stopped working for Java/J2EE apps (Regression from Hoary). Edit Remove

Affects		Status	Importance	Assigned to	Milestone
	mysql-dfsg-4.1 (Ubuntu)	New	Medium	MOTU

Bug Description

As requested in http://bugzilla.ubuntu.com/show_bug.cgi?id=18167, I am filing this here:

I have found what I believe to be a major regression from Hoary. In short:

SELECT * FROM mytable WHERE id=1

returns 1 row, as expected. However,

SELECT * FROM mytable WHERE id=?

and setting parameter 1 to 1 ("ps.setInt(1, 1);") does not return any rows. This
should not happen, am I right?

I created the table with:

CREATE TABLE mytable ( id INT PRIMARY KEY, class VARCHAR(32) )
INSERT INTO mytable ( id, class ) VALUES ( 1, 'hallo' )
INSERT INTO mytable ( id, class ) VALUES ( 2, 'hugo' )

I am issuing the statements using Connector/J 3.1.11. I have switched on
autoGenerateTestcaseScript in the driver, and the output is:

/* conn id 0 */ SET NAMES latin1;
/* conn id 0 */ SET character_set_results = NULL;
/* conn id 0 */ SHOW VARIABLES;
/* conn id 0 */ SHOW COLLATION;
/* conn id 0 */ SET autocommit=1;
/* conn id 0 */ PREPARE debug_stmt_3 FROM "SELECT * FROM mytable WHERE id=?";
/* conn id 0 */ SET @debug_stmt_param3_0='1';
/* conn id 0 */ EXECUTE debug_stmt_3 USING @debug_stmt_param3_0;
/* conn id 0 */ DEALLOCATE PREPARE debug_stmt_3;

If I enter these commands into "mysql", it returns one row as expected.

I tried repairing the table, both with "REPAIR TABLE" and with myisamchk. The
table was ok, and the problem persisted.

The problem is independant of the JDK version, I've tried SUN JDK 1.5.0_04,
1.5.0_05 and 1.4.2_08. I also tried gij 4.0.1 (which comes with Ubuntu Breezy).

The problem seems to be independant of the Connecter/J version, too. I tried
3.1.9 and 3.1.11.

The problem persists if I create a completely new database (in fact I created a
new /var/lib/mysql, so there were no other databases around).

The problem does _not_ happen on MySQL 4.0.24 or MySQL 4.1.10a (Hoary).
Downgrading is the only workaround I found.

I consider this major, as every application now should use PreparedStatements
(certainly all apps using Hibernate do), because of security reasons (SQL
injection).

See original description

Andreas Schildbach (schildbach) on 2005-10-21

description:

updated

Revision history for this message

Marc Portier (marc-portier) wrote on 2005-10-24:

I've experienced the same, and tracked it down to the databinding of values in server-side prepared statements on 4.1.x of mysql server (although apparently only the debian package for 4.1.12 seems to be affected)

2 possible stop-gaps:
- use connector/j 3.0.x in stead (it doesn't exploit the server side prepared statements yet)
- disable the use of server-side prepared statements by adding the ?useServerPrepStmts=false to the jdbc url

NOTE: in this case the jdbc driver DOES support prepared statements, but emulates them at the client side. (so you shouldn't be worried for sql injection attacks)

Nevertheless I don't consider these as workarounds since disabling server-side prepared statements might have a mayor impact on performance of your app. (just check) It's really just a stop-gap.

I also took the trouble of mentioning over in the mysql-jdbc forum and was told this is probably related to gcc/glibc bug in the debian distro

more 2 read over there:
http://forums.mysql.com/read.php?39,49989,49989#msg-49989

description:	updated
description:	updated

Revision history for this message

Francois Levasseur (francois-levasseur) wrote on 2006-02-15:

Hi all,

I have the same problem with PHP 5.x (including lastest php 5.1.2).

The prepared statement does not work with the PHP5's mysqli extention.

<?php
$mysqli = new mysqli("localhost", "francois", "", "mydatabase");

if ($stmt = $mysqli->prepare("SELECT colA FROM mytable WHERE colA=?")) {

$val = 'foo';
$stmt->bind_param("s", $val);

$stmt->execute();

        $stmt->bind_result($res_val);
        if ($stmt->fetch()) echo "working! val=$res_val";
        $stmt->close();
}
$mysqli->close();
?>

There is no result.

Note that when I execute the same script on Ubuntu but I use a remote mysql server (host on gentoo for instance), this code works.

The problem is realy in MySQL (may be an incompatibility with GCC?).

I have'nt tried official binary or mysql 5.x on drapper.

I' waiting for the next update for now...

Daniel Robitaille (robitaille) on 2006-03-15

Changed in mysql-dfsg-4.1:
assignee:	nobody → motu

Revision history for this message

Marc Portier (marc-portier) wrote on 2006-06-12:

I've just tested this on the dapper release with the 4.1.15-1ubuntu5.
The problem is still there.

Given the fact that this has been reported to spread in php and openoffice [1] as well, I also think it would be appropriate to increase the importance of this issue.

I'm willing to spend more time into solving this, but would at least need some guidance about building packages from source and the like.

Finally it would be nice if at least some of 'the powers that be' endulge in notifying this is heard somehow? I 'm ready to accept I'm barking up the wrong tree here, but would at least like to see some feedback.

--
[1] http://ubuntuforums.org/showthread.php?p=424347#post424347

Revision history for this message

Marc Portier (marc-portier) wrote on 2006-06-16:

Tested on dapper: the prefered mysql-server package there (version 5) does not show this problem, users might be interested to know that upgrading to that could be another way out.