PHP PDO error in multi-statement query

Question

I ran in to this problem in one of my live web apps. It seems that if you issue a multi-statement query to MySQL via PHP PDO, and the first statement is an insert statement, and the second statement is an update statement, the PDO::nextRowset() function doesn't return the right number of result sets. (Note that PDO supposedly supports multiple statements per MySQL query since PHP 5.3.)

Here's an example:

SQL:

create database `test`character set utf8 collate utf8_general_ci;
create table `test`.`testtable`( `id` int );

PHP:

<?php
$link = new \PDO('mysql:host=localhost;dbname=test', 'username', 'password');

//Run one of the 4 $handle assignments at a time (comment out all but one). 
//Run #4 on an empty table to compare the results of #1 and #4.

//WORKS: INSERT, followed by SELECT, followed UPDATE
//Output: 
//Rowset 1
//Rowset 2
//Results detected
$handle = $link->prepare(' insert into testtable(id) values(1);
                           select * from testtable where id = ?;
                           update testtable set id = 2 where id = ?;');


//WORKS: SELECT, followed by UPDATE
//Output: 
//Rowset 1
//Results detected
$handle = $link->prepare('select * from testtable where id = ?; 
                          update testtable set id = 2 where id = ?;');

//WORKS: UPDATE, followed by SELECT
//Output: 
//Rowset 1
//Rowset 2
//Results detected
$handle = $link->prepare('select * from testtable where id = ?; 
                         update testtable set id = 2 where id = ?;');


//DOESN'T WORK: INSERT, followed by UPDATE, followed by SELECT
//Output: 
//Rowset 1
//Expected output: same as examples 1 and 3
$handle = $link->prepare('insert into testtable(id) values(1);
                          update testtable set id = 2 where id = ?;
                          select * from testtable where id = ?;');

$handle->bindValue('1', '1');
$handle->bindValue('2', '2');

$handle->execute();

$i = 1;
do{
    print('Rowset ' . $i++ . "\n");
    if($handle->columnCount() > 0)
     print("Results detected\n");
}while($handle->nextRowset());
?>

Does anyone have any idea as to what I'm doing wrong? Why can't I put my select statement at the end?

PHP 5.3.5

MySQL 5.1.54

You shouldn't be doing multiple queries in a single query call to begin with. The mysql client libraries for PHP, which PDO uses, do not support this as an sql injection prevention defense. — Marc B
– Marc B, Commented Feb 22, 2012 at 4:07
@LawrenceCherone One should, if one is developing within a namespace — Phil
– Phil, Commented Feb 22, 2012 at 4:09
PDO supports multiple statements per query as of PHP 5.3, so the question isn't whether or not one should do that, but why the above example is giving strange results. — Alex Cabal
– Alex Cabal, Commented Feb 22, 2012 at 4:10
@MarcB this is interesting. apparently pdo switched mysql drivers in php 5.3: stackoverflow.com/questions/6346674/… — dqhendricks
– dqhendricks, Commented Feb 22, 2012 at 5:11

Alex Cabal · Accepted Answer · 2012-02-29 14:44:45Z

4

I submitted a PHP bug report about this problem and a possible patch has been submitted. So it looks like this was a PHP bug.

answered Feb 29, 2012 at 14:44

Alex Cabal

917 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shengfengli · Accepted Answer · 2012-02-26 09:51:06Z

0

First , you have to figure out what will nextRowset() returns regarding of a non-select operation . I have tried to var_dump it after an update ,and I got bool(false), which can explain why you only get one output with :
```
$handle = $link->prepare('insert into testtable(id) values(1);
                  update testtable set id = 2 where id = ?;
                  select * from testtable where id = ?;');
```
Second, I replaced the following codes:
```
var_dump($h->nextRowset());
var_dump($h->nextRowset());
```
with your do-while segment,I got bool(false) bool(true), witch means that it just goes once in the loop.

I don't know whether it is right to explain your question as above, may it will help you.

edited Feb 26, 2012 at 9:51

answered Feb 26, 2012 at 9:42

shengfengli

1261 silver badge8 bronze badges

Collectives™ on Stack Overflow

PHP PDO error in multi-statement query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related