php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #64638 Fetching resultsets from stored procedure with cursor fails
Submitted: 2013-04-11 23:37 UTC Modified: 2020-12-18 09:26 UTC
Votes:23
Avg. Score:4.6 ± 0.6
Reproduced:23 of 23 (100.0%)
Same Version:14 (60.9%)
Same OS:15 (65.2%)
From: DimonSoft at sa-sec dot org Assigned: nikic (profile)
Status: Closed Package: MySQLi related
PHP Version: 5.3, 5.4, 5.5, 5.6, 7 OS: Irrelevant
Private report: No CVE-ID: None
 [2013-04-11 23:37 UTC] DimonSoft at sa-sec dot org
Description:
------------
It seems like there's no enough info in the documentation on how to retrieve result sets when calling a stored procedure with mysqli_stmt (prepared statement).

Although it is quite easy to find non-official recomendations on the Internet (like using mysqli_stmt_more_results() and mysqli_stmt_next_result()) it is still not enough.

Right now I'm having a related problem. There're 2 stored procedures: Proc1 and Proc2. Proc1 internally calls Proc2(), which, in its turn, produces a resultset. See pseudocode.

Test script:
---------------
Pseudocode:

$DB = new mysqli(…);
$Stmt = $DB->prepare('CALL `Proc1`(?)');
$Stmt->bind_param(…);
$Stmt->execute();
$Stmt->store_result();
$Stmt->bind_result(…);
while ($Stmt->fetch())
{
  …
}
while ($Stmt->next_result())
  $Stmt->store_result();
$Stmt->free_result
…

Expected result:
----------------
Resultset is expected to get fetched.

Actual result:
--------------
A call to $Stmt->fetch() fails with "Packets out of order" message.

I guess, I'm doing something wrong, but there're no hints anywhere in the documentation.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-04-12 23:22 UTC] DimonSoft at sa-sec dot org
Description:
------------
Attempt to retrieve resultsets from stored procedures containing cursors fails with "Packets out of order" message. Retrieving data from SPs without cursors works fine.

See "Test script" section for a minimal test that reproduces the problem.

Test script:
---------------
PHP:
<?php
  $DB = new mysqli('SomeHost', 'SomeLogin', 'SomePassword', 'SomeDB');
  $Stmt = $DB->prepare('CALL `Proc1`()');
  $Stmt->execute();
  $Stmt->store_result();
  $Stmt->bind_result($Res);
  $Stmt->fetch();
echo 'OK';
  $Stmt->close();
  $DB->close();
var_dump($Res);
?>

SQL:
DROP PROCEDURE IF EXISTS `Proc1`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc1`()
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  DECLARE Temp CURSOR FOR
    SELECT 15 AS Result;

  OPEN Temp;
  CLOSE Temp;
  SELECT 20 AS Result;
END//
DELIMITER ;

Expected result:
----------------
"OKint(20)" is expected to be output without any error messages.

Actual result:
--------------
A call to $Stmt->fetch() fails with "Packets out of order" message.
 [2013-04-12 23:22 UTC] DimonSoft at sa-sec dot org
-Summary: No info about combining prepared statements and stored procedures +Summary: Fetching resultsets from stored procedure with cursor fails -PHP Version: Irrelevant +PHP Version: 5.3.13
 [2013-09-22 11:18 UTC] flannell at gmail dot com
I am also experiencing exactly the same issue.  PHP v5.3.8 on
ApacheFriends XAMPP version 1.7.7

Using mysqli directly works fine, just the PDO statement bringing back the error 
upon fetch() or fetchall()
 [2013-09-22 11:26 UTC] flannell at gmail dot com
In addition, it doesn't matter if the cursor is embedded from a second called 
stored procedure.  As soon as OPEN cursor is called it throws the error.  Am 
wondering if PDO Statement is finding trouble deducing what columns are going to 
be returned and the cursor confuses it?
 [2013-09-23 06:41 UTC] flannell at gmail dot com
Found the problem in my scenario.  You can't use cursors in stored procedures 
using PDO and having this in your connection params:

$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

If removed, or set to true (the default), it starts to work.

This does raise a possible SQL injection issue as the SQL statement and params 
are no longer sent to the server independently.

Hope this helps someone.
 [2015-07-28 13:03 UTC] andrey@php.net
-Status: Open +Status: Assigned -PHP Version: 5.3.13 +PHP Version: 5.3, 5.4, 5.5, 5.6, 7 -Assigned To: +Assigned To: mysql
 [2015-07-28 13:03 UTC] andrey@php.net
Crash reproduced with 5.4, 5.5, 5.6 and 7 . Probably has something to do with the cursor opened in the SP. A SP which also generates a result set, like BEGIN SELECT 1; END doesn't crash.
 [2015-07-28 13:04 UTC] andrey@php.net
Crash reproduced with mysqli
 [2015-07-28 13:05 UTC] andrey@php.net
delimiter //
CREATE PROCEDURE `test`()
BEGIN
declare test_var varchar(100) default "ciao";
declare bNoMoreRows bool default false;
declare test_cursor cursor for
    select id from tmp_folder;
declare continue handler for not found set bNoMoreRows := true;
create temporary table tmp_folder select "test" as id;
open test_cursor;
fetch test_cursor into test_var;
close test_cursor;
select test_var;
drop temporary table if exists tmp_folder;
END//


./php -r '$c=mysqli_connect("127.0.0.1", "root","", "test");$s=$c->prepare("CALL test()");var_dump($s->execute());var_dump($s->get_result());'
 [2015-07-28 13:06 UTC] andrey@php.net
-Type: Documentation Problem +Type: Bug
 [2019-03-25 02:48 UTC] maxosky at gmail dot com
Same problem in PHP 7.1.26, always not fixed.
 [2020-12-18 09:26 UTC] nikic@php.net
-Status: Assigned +Status: Closed -Assigned To: mysql +Assigned To: nikic
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 11:01:28 2024 UTC