Bug #70628 [Csd]: Clearing bindings on an SQLite3 statement doesn't work

Edit report at https://bugs.php.net/bug.php?id=70628&edit=1 ID: 70628 Updated by: cmb@php.net Reported by: symos at yahoo dot com Summary: Clearing bindings on an SQLite3 statement doesn't work Status: Closed Type: Bug Package: SQLite related PHP Version: 5.6.14 Assigned To: cmb Block user comment: N Private report: N New Comment: Thank you for the report, and for helping us make PHP better. Previous Comments: ------------------------------------------------------------------------ [2016-06-27 15:25:40] 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 14:37:30] 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> ------------------------------------------------------------------------ [2015-10-03 13:34:12] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=70628&edit=1