php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #70628 Clearing bindings on an SQLite3 statement doesn't work
Submitted: 2015-10-03 13:34 UTC Modified: 2016-06-27 15:26 UTC
From: symos at yahoo dot com Assigned: cmb (profile)
Status: Closed Package: SQLite related
PHP Version: 5.6.14 OS:
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: symos at yahoo dot com
New email:
PHP Version: OS:

 

 [2015-10-03 13:34 UTC] symos at yahoo dot com
Description:
------------
When inserting multiple rows to SQLite3 using a prepared statement, if you don't bind a parameter for a row then the value from the previous row will be inserted, even if you "clear" and "reset" the statement between lines.

The equivalent code in C seems to work as expected as demonstrated here:
http://stackoverflow.com/questions/32917795/clearing-bindings-on-an-sqlite3-statement-doesnt-seem-to-work-php/32918127

Therefore this leads me to believe this is a PHP bug.

Test script:
---------------
$db = new SQLite3('dogsDb.sqlite');

$db->exec("CREATE TABLE Dogs (Id INTEGER PRIMARY KEY, Breed TEXT, Name TEXT, Age INTEGER)");    

$sth = $db->prepare("INSERT INTO Dogs (Breed, Name, Age)  VALUES (:breed,:name,:age)");

$sth->bindValue(':breed', 'canis', SQLITE3_TEXT);
$sth->bindValue(':name', 'jack', SQLITE3_TEXT);
$sth->bindValue(':age', 7, SQLITE3_INTEGER);
$sth->execute();

$sth->clear(); //this is supposed to clear bindings!
$sth->reset();

$sth->bindValue(':breed', 'russel', SQLITE3_TEXT);         
$sth->bindValue(':age', 3, SQLITE3_INTEGER);
$sth->execute();

Expected result:
----------------
Second database row should have a null value for the 'name' column.

Actual result:
--------------
Second database row has 'jack' value for the 'name' column.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-06-27 14:37 UTC] cmb@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: cmb
 [2016-06-27 14:37 UTC] cmb@php.net
Confirmed: <https://3v4l.org/nLZAP>.

The culprit appears to be that SQLite3Stmt::clear() indeed calls
sqlite3_clear_bindings()[1], but doesn't reset the internally
stored bound_params[2], so they'll be reused when the statement is
executed again[3].

[1] <https://github.com/php/php-src/blob/PHP-7.0.8/ext/sqlite3/sqlite3.c#L1331>
[2] <https://github.com/php/php-src/blob/PHP-7.0.8/ext/sqlite3/sqlite3.c#L1364>
[3] <https://github.com/php/php-src/blob/PHP-7.0.8/ext/sqlite3/sqlite3.c#L1527>
 [2016-06-27 15:25 UTC] cmb@php.net
Automatic comment on behalf of cmb
Revision: http://git.php.net/?p=php-src.git;a=commit;h=57940605da718174cdcf5f6bf19b7ed7df27ffa6
Log: Fix #70628: Clearing bindings on an SQLite3 statement doesn't work
 [2016-06-27 15:25 UTC] cmb@php.net
-Status: Verified +Status: Closed
 [2016-06-27 15:26 UTC] cmb@php.net
Thank you for the report, and for helping us make PHP better.
 [2016-07-06 05:47 UTC] davey@php.net
Automatic comment on behalf of cmb
Revision: http://git.php.net/?p=php-src.git;a=commit;h=57940605da718174cdcf5f6bf19b7ed7df27ffa6
Log: Fix #70628: Clearing bindings on an SQLite3 statement doesn't work
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Nov 21 15:01:30 2024 UTC