Doc #64638 [Com]: Fetching resultsets from stored procedure with cursor fails

Edit report at https://bugs.php.net/bug.php?id=64638&edit=1 ID: 64638 Comment by: flannell at gmail dot com Reported by: DimonSoft at sa-sec dot org Summary: Fetching resultsets from stored procedure with cursor fails Status: Open Type: Documentation Problem Package: MySQLi related Operating System: Irrelevant PHP Version: 5.3.13 Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2013-09-22 11:26:02] 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-22 11:18:41] 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-04-12 23:22:59] 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-11 23:37:54] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=64638&edit=1