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
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:
29 - 18 = ?
Subscribe to this entry?

 
 [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: Wed Dec 11 01:01:27 2024 UTC