php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40852 prepared statement in a stored procedure mess up bind_result() parameters
Submitted: 2007-03-19 00:04 UTC Modified: 2007-04-04 19:59 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:1 (50.0%)
From: phpbugs at mise dot x25 dot se Assigned: andrei (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5CVS-2007-03-19 (snap) OS: winXP
Private report: No CVE-ID: None
 [2007-03-19 00:04 UTC] phpbugs at mise dot x25 dot se
Description:
------------
Using a prepared statement in a stored procedure in MySQL 5 mess up bind_result() parameters. The prepared statement can be anything, it seems.

Reproduce code:
---------------
- create a stored procedure in the mysql test database
DELIMITER $$
DROP PROCEDURE IF EXISTS test.spTest$$
CREATE PROCEDURE test.spTest()
BEGIN
  PREPARE statement FROM "SET @x = 123";
  EXECUTE statement;
  DEALLOCATE PREPARE statement;
  SELECT 5, 8;
END$$


- then call it from PHP like this
<?php
  $mysqli = new mysqli("localhost", "user", "pwd", "test");
  $statement = $mysqli->prepare("CALL spTest()");
  $statement->execute();
  $statement->bind_result($a, $b);
  $statement->fetch();
  print "a = $a,  b = $b";
?>

Expected result:
----------------
Expected result 
a = 5,  b = 8

we can confirm this by doing:
mysql> call spTest();
+---+---+
| 5 | 8 |
+---+---+
| 5 | 8 |
+---+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

(moving "SELECT 5, 8" above EXECUTE in the stored procedure yield the expected result)

Actual result:
--------------
Actual result (in this case):
a = ,  b = 15763698207438849

but depending on a variety of unknown factors the bound result can be complete garble as well.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-03-19 09:32 UTC] tony2001@php.net
Andrei, is this ext/mysqli or MySQL issue?
 [2007-04-04 19:59 UTC] georg@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html:

The following statements can be used as prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not supported in MySQL 5.0.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 07:01:28 2024 UTC