Bug #70628 [Opn->Ver]: 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: Open +Status: Verified Type: Bug Package: SQLite related PHP Version: 5.6.14 -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: 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> Previous Comments: ------------------------------------------------------------------------ [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