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

Edit report at https://bugs.php.net/bug.php?id=64638&edit=1 ID: 64638 Updated by: andrey@php.net Reported by: DimonSoft at sa-sec dot org Summary: Fetching resultsets from stored procedure with cursor fails Status: Assigned Type: Documentation Problem Package: MySQLi related Operating System: Irrelevant PHP Version: 5.3, 5.4, 5.5, 5.6, 7 Assigned To: mysql Block user comment: N Private report: N New Comment: 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());' Previous Comments: ------------------------------------------------------------------------ [2015-07-28 13:04:09] andrey@php.net Crash reproduced with mysqli ------------------------------------------------------------------------ [2015-07-28 13:03:30] 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. ------------------------------------------------------------------------ [2013-09-23 06:41:50] 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. ------------------------------------------------------------------------ [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() ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=64638 -- Edit this bug report at https://bugs.php.net/bug.php?id=64638&edit=1