php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #47782 Anomalous results from stored procedure with a PREPAREd statement
Submitted: 2009-03-26 00:08 UTC Modified: 2009-04-29 16:54 UTC
From: phpbug at smithii dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.2.9, 5.3.0RC2 OS: Linux, Windows
Private report: No CVE-ID: None
 [2009-03-26 00:08 UTC] phpbug at smithii dot com
Description:
------------
Using MySQL 5.0.77, and calling a stored procedure with a PREPAREd statement, execute()/bind_result()/fetch() return anomalous results.


Reproduce code:
---------------
Using MySQL 5.0.77, and calling any stored procedure with a PREPAREd statement, such as:

DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT ',QUOTE(p));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
END;
//
DELIMITER ;

via this script:

<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
$sql = 'CALL echo(?)';
$s = $mysqli->prepare($sql);
$i = $argv[1];
printf("i=%s\n", $i);
$s->bind_param('s', $i);
$s->execute();
$s->bind_result($o);
while ($s->fetch()) {
   printf("o=%s (%s)\n", $o, bin2hex($o));
}
$s->close();

produces anomalous results at least 50% of the time. For example:

$ php echo.php abcd
i=abcd
o=cd  ♦ (6364000004)

If I remove the PREPAREd statement:

DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
    SELECT p;
END;
//
DELIMITER ;

everything works fine.

Replacing execute()/bind_result()/fetch(), with query()/fetch_assoc() also fixes the issue.

Other details:

mysqli_get_client_info=5.0.51a
mysqli_get_client_version=50051
mysqli_get_server_info=5.0.77-community-nt
mysqli_get_server_version=50077
mysqli_get_host_info=localhost via TCP/IP
mysqli_get_proto_info=10


Expected result:
----------------
$ php echo.php abcd
i=abcd
o=abcd (63646566)


Actual result:
--------------
$ php echo.php abcd
i=abcd
o=cd  ♦ (6364000004)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-04-15 10:09 UTC] phpbug at smithii dot com
The following script produces errors on 5.2.9 and 5.3.0RC2, on both Linux and Windows:

<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
if (!$mysqli) die(mysqli_connect_error());
$sqls[] = <<<EOT
CREATE PROCEDURE echo0(p VARCHAR(255)) 
BEGIN 
	SELECT p; 
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo1(p VARCHAR(255))
BEGIN
	SET @sql = CONCAT('SELECT ', QUOTE(p));
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo2(p VARCHAR(255))
BEGIN
	PREPARE stmt FROM 'SELECT ?';
	SET @p = p;
	EXECUTE stmt USING @p;
	DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo3(p VARCHAR(255))
BEGIN
	PREPARE stmt FROM 'SELECT 1234';
	EXECUTE stmt;
	DROP PREPARE stmt;
END
EOT;
$inp = strval($argv[1]);
foreach ($sqls as $i => $sql) {
	$mysqli->query("DROP PROCEDURE IF EXISTS echo$i");
	$mysqli->query($sql) || die($mysqli->error);
	$sql = "CALL echo$i(?)";
	printf("Executing: %s with '%s'\n", $sql, $inp);
	$s = $mysqli->prepare($sql);
	if (!$s) die($mysqli->error);
	printf("inp=%s (%s)\n", $inp, bin2hex($inp));
	$s->bind_param('s', $inp) || die($mysqli->error);
	$s->execute() || die($mysqli->error);
	$s->bind_result($out) || die($mysqli->error);
	while ($s->fetch()) {
	   printf("out=%s (%s)\n", $out, bin2hex($out));
	}
	$s->close();
}

Here's the script's output:

Executing: CALL echo0(?) with '1234'
inp=1234 (31323334)
out=1234 (31323334)
Executing: CALL echo1(?) with '1234'
inp=1234 (31323334)
out=34                                                 (3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34                                                 (3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
Executing: CALL echo3(?) with '1234'
inp=1234 (31323334)
out=3420978 (33343230393738)
 [2009-04-26 15:41 UTC] jani@php.net
For calling stored procedures you have to use mysqli_multi_query.
 [2009-04-26 15:58 UTC] phpbug at smithii dot com
jani@php.net: I can call stored procedures just fine with execute()/bind_result()/fetch(). This is the method the Zend Framework uses, and I don't want to rewrite their classes. I'm sure there are many other PHP frameworks that use these functions as well.

Also, the mysqli_multi_query() function does not allow me to prepare() a statement once, and then execute() it multiple times.

The only problem I'm having, and it's still a problem with PHP 5.3 and MySQL 5.1, is that if the stored procedure contains a PREPAREd statement, then the fetch() function is returning corrupted data.

This sure seems to me to be an actual bug, and not just that I'm using the wrong function call.

Therefore, please mark this bug as open.

I will attempt to fix this myself. Can you point me to any documentation or IRC channel, that would help me to get started?

Thanks for any assistance,

Ross
 [2009-04-29 15:28 UTC] uw@php.net
Uuupps, accidently changed the Status?

I am quite sure this is not an API issue. Using Prepared Statements with CALL should be possible a late MySQL 5.0 beta. However, your bug report made me test some things using the C-API. I ended up filing two MySQL Server bugs:

http://bugs.mysql.com/bug.php?id=44521
http://bugs.mysql.com/bug.php?id=44495

I suggest we close this report. Most likely you have hit a server issue and not PHP problem.

Ulf
 [2009-04-29 16:54 UTC] phpbug at smithii dot com
Ulf, I concur. Thank you for taking the time to file the MySQL bug reports.

Best,

Ross
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Wed Jul 06 16:05:46 2022 UTC