Bug #64531 [Com]: SQLite3Result::fetchArray runs the query again.

Edit report at https://bugs.php.net/bug.php?id=64531&edit=1 ID: 64531 Comment by: antoni at friki dot cat Reported by: phplists at stanvassilev dot com Summary: SQLite3Result::fetchArray runs the query again. Status: Verified Type: Bug Package: SQLite related Operating System: Windows XP SP3 32bit PHP Version: 5.4.13 Block user comment: N Private report: N New Comment: Here an PHP example reproducing the weird issue. Find some commented out alternatives to avoid problems with current php-sqlite3 implementation: https://pastebin.com/kCvb65FH The key point using current SQLite3 version is not to call sqlite3_step() on a statement if previous sqlite3_step() over same statement returned !=SQLITE_ROW. The key point using current PHP version is not to call fetchArray() on query/execute $result. Using if numColumns()!==0 you can figure out if you want to run fetchArray() data after execute()/query(). Regards, Previous Comments: ------------------------------------------------------------------------ [2020-01-31 21:20:35] antoni at friki dot cat Hi, today I've faced that problem. Here my patch: --- php-7.4.2/ext/sqlite3/sqlite3.c 2020-01-21 12:35:21.000000000 +0100 +++ php-7.4.2-sqlite3fixed/ext/sqlite3/sqlite3.c 2020-01-31 22:12:56.599653191 +0100 @@ -612,8 +612,11 @@ return_code = sqlite3_step(result->stmt_obj->stmt); switch (return_code) { - case SQLITE_ROW: /* Valid Row */ case SQLITE_DONE: /* Valid but no results */ + { + result->complete = 1; + } + case SQLITE_ROW: /* Valid Row */ { php_sqlite3_free_list *free_item; free_item = emalloc(sizeof(php_sqlite3_free_list)); @@ -624,6 +627,7 @@ break; } default: + result->complete = 1; if (!EG(exception)) { php_sqlite3_error(db_obj, "Unable to execute statement: %s", sqlite3_errmsg(db_obj->db)); } @@ -713,6 +717,9 @@ return_code = sqlite3_step(stmt); switch (return_code) { + php_sqlite3_result *result_obj; + zval *object = ZEND_THIS; + result_obj = Z_SQLITE3_RESULT_P(object); case SQLITE_ROW: /* Valid Row */ { if (!entire_row) { @@ -730,6 +737,8 @@ } case SQLITE_DONE: /* Valid but no results */ { + result_obj->complete = 1; + if (!entire_row) { RETVAL_NULL(); } else { @@ -738,10 +747,12 @@ break; } default: - if (!EG(exception)) { - php_sqlite3_error(db_obj, "Unable to execute statement: %s", sqlite3_errmsg(db_obj->db)); - } - RETVAL_FALSE; + result_obj->complete = 1; + + if (!EG(exception)) { + php_sqlite3_error(db_obj, "Unable to execute statement: %s", sqlite3_errmsg(db_obj->db)); + } + RETVAL_FALSE; } sqlite3_finalize(stmt); } @@ -1844,14 +1855,16 @@ return_code = sqlite3_step(stmt_obj->stmt); + sqlite3_reset(stmt_obj->stmt); + object_init_ex(return_value, php_sqlite3_result_entry); + result = Z_SQLITE3_RESULT_P(return_value); switch (return_code) { - case SQLITE_ROW: /* Valid Row */ case SQLITE_DONE: /* Valid but no results */ + { + result->complete = 1; + } + case SQLITE_ROW: /* Valid Row */ { - sqlite3_reset(stmt_obj->stmt); - object_init_ex(return_value, php_sqlite3_result_entry); - result = Z_SQLITE3_RESULT_P(return_value); - result->is_prepared_statement = 1; result->db_obj = stmt_obj->db_obj; result->stmt_obj = stmt_obj; @@ -1861,9 +1874,11 @@ break; } case SQLITE_ERROR: + result->complete = 1; sqlite3_reset(stmt_obj->stmt); default: + result->complete = 1; if (!EG(exception)) { php_sqlite3_error(stmt_obj->db_obj, "Unable to execute statement: %s", sqlite3_errmsg(sqlite3_db_handle(stmt_obj->stmt))); } @@ -1939,6 +1954,10 @@ return; } + if (result_obj->complete) { + RETURN_FALSE; + } + RETURN_LONG(sqlite3_column_count(result_obj->stmt_obj->stmt)); } /* }}} */ @@ -1958,6 +1977,11 @@ if (zend_parse_parameters(ZEND_NUM_ARGS(), "l", &column) == FAILURE) { return; } + + if (result_obj->complete) { + RETURN_FALSE; + } + column_name = (char*) sqlite3_column_name(result_obj->stmt_obj->stmt, column); if (column_name == NULL) { @@ -2007,6 +2031,10 @@ return; } + if (result_obj->complete==1) { + return; + } + ret = sqlite3_step(result_obj->stmt_obj->stmt); switch (ret) { case SQLITE_ROW: @@ -2043,6 +2071,7 @@ break; default: + result_obj->complete = 1; php_sqlite3_error(result_obj->db_obj, "Unable to execute statement: %s", sqlite3_errmsg(sqlite3_db_handle(result_obj->stmt_obj->stmt))); } } ------------------------------------------------------------------------ [2019-11-11 13:17:01] cmb@php.net Related To: Bug #77977 ------------------------------------------------------------------------ [2019-11-08 21:37:35] svnpenn at gmail dot com I am new to PHP SQLite so at first I was disappointed to find that "procedural style" functions were dropped with PHP SQLite3. Compare: - https://php.net/function.sqlite-open - https://php.net/sqlite3.construct but I was going to give the Object oriented style a chance. Then I came across this bug here: https://php.net/sqlite3stmt.execute#119404 and the example here still fails today: https://3v4l.org/MMGio I am surpised that its still open 6 years later. After realizing the poor PHP support for SQLite, I will simply call the SQLite binary going forward. ------------------------------------------------------------------------ [2019-05-08 09:24:48] cmb@php.net Related To: Bug #77977 ------------------------------------------------------------------------ [2018-09-24 15:49:07] cmb@php.net While the fix for SQLite3::query() is trivial, a general fix for SQLite3Stmt::execute() is impossible, since it is allowed to ::execute() the same statement multiple times, which might trigger sqlite3_reset()s at unexpected times (see, for instance, bug #73530). ------------------------------------------------------------------------ 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=64531 -- Edit this bug report at https://bugs.php.net/bug.php?id=64531&edit=1