php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72267 SQLite PDOStatement iterator problems
Submitted: 2016-05-26 13:50 UTC Modified: 2016-05-26 21:47 UTC
From: baptiste dot gaillard at gomoob dot com Assigned:
Status: Not a bug Package: PDO SQLite
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
13 + 24 = ?
Subscribe to this entry?

 
 [2016-05-26 13:50 UTC] baptiste dot gaillard at gomoob dot com
Description:
------------
This has been reported on Stackoverflow here http://stackoverflow.com/questions/37431600/pdo-sqlite-extension-pdostatement-bug but nobody provided a satisfying response.

When we use an in memory SQLite and a PDOStatement inside a foreach loop the iterator state associated to the PDOStatement can be updated by other PDOStatement in the source code. 

Please test the provided test script provided with this case, this script shows that the PDOStatement used in a foreach loop can be modified by other PDOStatement objects. The output displays "1" and "3" but should display only "1" IMO. 

This behavior does not appear with MySQL so I think this behavior is clearly not expected and is very dangerous.

The official PHP documentation indicates a PDOStatement "Represents a prepared statement and, after the statement is executed, an associated result set.".

So we expect the PDOStatement to be something like an "in memory" object which embeds our results and which cannot be touched ("magically") by other peaces of code elsewhere. 

Test script:
---------------
// Insert 2 rows inside our table
$pdoStatementInsert = $pdo->prepare(
    'insert into scheduled_task(id, task_class, date_and_time, task_parameters) values(?,?,?,?)'
);
$pdoStatementInsert->execute([1, 'tk1', '2015-01-01', '{}']);
$pdoStatementInsert->execute([2, 'tk2', '2015-01-02', '{}']);

// Ensure the 2 rows were inserted
$pdoStatementCount->execute();
var_dump('Then table has size \'' . intval($pdoStatementCount->fetchColumn()) . '\'.');

// Now create a statement to select only the first row
$pdoStatementSelect1 = $pdo->query('select * from scheduled_task where date_and_time < \'2015-01-02\'');

// Here it seems their is a bug with the Iterator associated to the PDO Statement
foreach($pdoStatementSelect1 as $row) {
    var_dump($row['id']);

    // Inserts a new row inside our table
    $pdoStatementInsert = $pdo->prepare(
        'insert into scheduled_task(id, task_class, date_and_time, task_parameters) values(?,?,?,?)'
    );
    if($pdoStatementInsert->execute([3, 'tk3', '2015-01-01', '{}']) === false)  {
        var_dump('Fail inserting row in loop !');
        var_dump($pdoStatementInsert->errorCode());
        var_dump($pdoStatementInsert->errorInfo());
    }

    // Deletes the first row
    $pdo->query('delete from scheduled_task where id = ' . $row['id']);
}

// This fails with SQLite, we should have 2 rows inside our table here
$pdoStatementCount->execute();
var_dump('At the end table has size \'' . intval($pdoStatementCount->fetchColumn()) . '\'.');

Expected result:
----------------
string(32) "At beginning table has size '0'."
string(24) "Then table has size '2'."
string(1) "1"
string(30) "At the end table has size '2'."

Actual result:
--------------
string(32) "At beginning table has size '0'."
string(24) "Then table has size '2'."
string(1) "1"
string(1) "3"
string(30) "At the end table has size '1'."

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-05-26 21:47 UTC] requinix@php.net
-Status: Open +Status: Not a bug
 [2016-05-26 21:47 UTC] requinix@php.net
PHP and PDO are just interfaces to the database engine. They simply just pass along whatever information is returned to them through the engine's API.

So it's a SQLite problem. And they say this is undefined behavior:

https://www.sqlite.org/isolation.html
> No Isolation Between Operations On The Same Database Connection
> ...The application can also INSERT new rows into the table while the SELECT
> statement is running, but whether or not the new rows appear in subsequent
> sqlite3_step()s of the query is undefined.

And it's not just SELECT+INSERT too - concurrently DELETEing or UPDATEing can cause undefined behavior as well.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Mon Oct 18 21:03:39 2021 UTC