Bug #73530 [Com]: Unsetting result set may reset other result set

Edit report at https://bugs.php.net/bug.php?id=73530&edit=1 ID: 73530 Comment by: jcrews at gridlox dot net Reported by: cmb@php.net Summary: Unsetting result set may reset other result set Status: Re-Opened Type: Bug Package: SQLite related Operating System: * PHP Version: master-Git-2016-11-15 (Git) Assigned To: cmb Block user comment: N Private report: N New Comment: >Thanks for reporting! > >> It appears, via static analysis, the correct solution is to >> sqlite3_reset and sqlite3_clear prior to re-binding parameters >> in sqlite3stmt::execute (in addition to this change). > >That doesn't appear to solve the issue with your supplied test >script. I'm not yet sure why, but probably it hasn't been a good >idea to try to resolve this issue in a patch release anyway, so >I'm going to revert the fix for now. I will be a good OSS citizen and work on a solution for 7.1.x. Thank you for reverting in the interim. Previous Comments: ------------------------------------------------------------------------ [2016-12-29 13:03:01] cmb@php.net Automatic comment on behalf of cmbecker69@gmx.de Revision: http://git.php.net/?p=php-src.git;a=commit;h=2ba3b275948050ce600c5234b66e840b640ca5a5 Log: Revert &quot;Fix #73530: Unsetting result set may reset other result set&quot; ------------------------------------------------------------------------ [2016-12-29 11:56:25] cmb@php.net > The change referenced in this bug introduced a regression > compared to earlier releases. Thanks for reporting! > It appears, via static analysis, the correct solution is to > sqlite3_reset and sqlite3_clear prior to re-binding parameters > in sqlite3stmt::execute (in addition to this change). That doesn't appear to solve the issue with your supplied test script. I'm not yet sure why, but probably it hasn't been a good idea to try to resolve this issue in a patch release anyway, so I'm going to revert the fix for now. ------------------------------------------------------------------------ [2016-12-29 03:40:29] jcrews at gridlox dot net The change referenced in this bug introduced a regression compared to earlier releases. Working: php-7.0.13 Fails: php-7.0.14 Description ---------------- Using php-7.0.14 with phpoffice/phpexcel with sqlite3 caching causes documents to fail to load. This is ultimately caused by phpexcel not using SQLite3Result::finalize (or reset) internally, within limited-lifetime functions. This is a significant change in behavior that may break deployed applications during system maintenance. sqlite3_step has two effects: - On a reset (or new) statement, the prepared statement will be executed, and the first row will be available. - On a statement VM that previously had sqlite3_step called, the next result row will be available, until the end of data is reached. sqlite3_reset resets the statement for execution, not any bound parameters. This also explains the original test script behavior: A step is peformed after a reset, which was implicitly called through unset. The statement was thus re-run, returning the first result, again. When fetchArray hits SQLITE_DONE, the statement isn't reset, and it shouldn't be. Otherwise, any while(haveData) loops would run forever. It appears, via static analysis, the correct solution is to sqlite3_reset and sqlite3_clear prior to re-binding parameters in sqlite3stmt::execute (in addition to this change). This will guarantee that each call to execute provides consistent results. Unfortunately, I do not have sufficient resources to build and test php at this time. Test Script ---------------- <?php $db = new SQLite3(':memory:'); $db->exec("CREATE TABLE foo (num int)"); $db->exec("INSERT INTO foo VALUES (0)"); $db->exec("INSERT INTO foo VALUES (1)"); $stmt = $db->prepare("SELECT * FROM foo WHERE NUM = ?"); function pass1($db, $stmt) { $stmt->bindValue(1, 0, SQLITE3_INTEGER); $res1 = $stmt->execute(); while ($row = $res1->fetchArray(SQLITE3_ASSOC)) { var_dump($row); } echo "finished first pass\n"; } function pass2($db, $stmt) { $stmt->bindValue(1, 1, SQLITE3_INTEGER); $res2 = $stmt->execute(); /* Oops, we forgot to reset in fetchArray, no effect */ while ($row = $res2->fetchArray(SQLITE3_ASSOC)) { var_dump($row); } } pass1($db, $stmt); pass2($db, $stmt); ?> Expected Result (>>php-7.0.13): ---------------- sqlitetest.php:12: array(1) { 'num' => int(0) } finished first pass sqlitetest.php:21: array(1) { 'num' => int(1) } Observed Result: ---------------- sqlitetest.php:12: array(1) { 'num' => int(0) } finished first pass sqlitetest.php:21: array(1) { 'num' => int(0) } ------------------------------------------------------------------------ [2016-11-22 13:14:08] krakjoe@php.net Automatic comment on behalf of cmbecker69@gmx.de Revision: http://git.php.net/?p=php-src.git;a=commit;h=eb570294a289b45d0dd38efc71065d6b0d314c4b Log: Fix #73530: Unsetting result set may reset other result set ------------------------------------------------------------------------ [2016-11-16 11:14:36] cmb@php.net Automatic comment on behalf of cmbecker69@gmx.de Revision: http://git.php.net/?p=php-src.git;a=commit;h=eb570294a289b45d0dd38efc71065d6b0d314c4b Log: Fix #73530: Unsetting result set may reset other result set ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=73530 -- Edit this bug report at https://bugs.php.net/bug.php?id=73530&edit=1