|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Tue Oct 28 09:00:01 2025 UTC |
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)