php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #78813 SQLite ALTER TABLE query is returning columns
Submitted: 2019-11-14 11:34 UTC Modified: 2020-02-24 12:50 UTC
From: deus dot kane at claromentis dot com Assigned: cmb (profile)
Status: Not a bug Package: SQLite related
PHP Version: 7.2.24 OS: Windows
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: deus dot kane at claromentis dot com
New email:
PHP Version: OS:

 

 [2019-11-14 11:34 UTC] deus dot kane at claromentis dot com
Description:
------------
Calling SQLite3::query with a resultless query returns a mostly useless result object.

Prior to PHP 7.2.24 calling SQLite3Result::numColumns on this result object would return zero, indicating that this was a query with no data to step through.

In PHP 7.2.24, calling SQLite3Result::numColumns on this result object returns 1, making it indistingishable from a result object that contains data. In addition, when calling SQLite3Result::columnName with the value 0 returns "1" (as a string).

The previous behaviour was relied on by my company when implementing an SQLite driver for our database layer. As we are issuing arbitrary queries, we always called ::query, and then used the fact that a query with no result had zero columns to avoid re-executing the query by stepping through it.


Test script:
---------------
<?php

$db = new SQLite3(":memory:");

$result = $db->query("CREATE TABLE users(id INTEGER PRIMARY KEY)");

$result = $db->query("ALTER TABLE users RENAME TO user_temp");

print($result->numColumns());




Expected result:
----------------
0 is output to the screen

Actual result:
--------------
1 is output to the screen

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-11-14 13:04 UTC] cmb@php.net
This behavioral change is caused by upgrading our bundled
libsqlite to 3.28.0, and is an upstream issue.  Not sure when
exactly the behavior changed (must have been after 3.25.1), and
whether this would be regarded as bug (the docs are not
particularly clear on this).  Anyway, pure C reproducer:

#include <string.h>
#include <stdio.h>
#include <assert.h>
#include <sqlite3.h>

#define SQL_CREATE "CREATE TABLE users(id INTEGER PRIMARY KEY)"
#define SQL_ALTER "ALTER TABLE users RENAME TO user_temp"

int main()
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;

    rc = sqlite3_open_v2(":memory:", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    assert(rc == SQLITE_OK);

    rc = sqlite3_prepare_v2(db, SQL_CREATE, strlen(SQL_CREATE), &stmt, NULL);
    assert(rc == SQLITE_OK);
    rc = sqlite3_step(stmt);
    assert(rc == SQLITE_DONE);
    printf("%d\n", sqlite3_column_count(stmt));
    rc = sqlite3_finalize(stmt);
    assert(rc == SQLITE_OK);

    rc = sqlite3_prepare_v2(db, SQL_ALTER, strlen(SQL_ALTER), &stmt, NULL);
    assert(rc == SQLITE_OK);
    rc = sqlite3_step(stmt);
    assert(rc == SQLITE_DONE);
    printf("%d\n", sqlite3_column_count(stmt));
    rc = sqlite3_finalize(stmt);
    assert(rc == SQLITE_OK);

    rc = sqlite3_close_v2(db);
    assert(rc == SQLITE_OK);

    return 0;
}
 [2020-02-19 15:29 UTC] cmb@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: cmb
 [2020-02-19 15:29 UTC] cmb@php.net
> (the docs are not particularly clear on this)

While the sqlite3_column_count() docs[1] are indeed not as clear
as would be desireable, the closely related sqlite3_column_*()
docs[2] are:

| These routines may only be called when the most recent call to
| sqlite3_step() has returned SQLITE_ROW and neither
| sqlite3_reset() nor sqlite3_finalize() have been called
| subsequently.

However, that is exactly what is happening in the sqlite3
extension, so this is not an upstream bug.

Sorry for not having realized this earlier, but now it's too late
to fix it for PHP 7.2, because that branch receives security
fixes only[3].

[1] <https://www.sqlite.org/c3ref/column_count.html>
[2] <https://www.sqlite.org/c3ref/column_blob.html>
[3] <https://www.php.net/supported-versions.php>
 [2020-02-24 12:48 UTC] cmb@php.net
Automatic comment from SVN on behalf of cmb
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=349251
Log: Clarify SQLite3Result::columnName()

Cf. bug #78813.
 [2020-02-24 12:50 UTC] cmb@php.net
-Status: Verified +Status: Not a bug
 [2020-02-24 12:50 UTC] cmb@php.net
> However, that is exactly what is happening in the sqlite3
> extension, so this is not an upstream bug.

Well, even if this (the additional call to sqlite3_reset()) would
not happen, the result would still be 1, as can be seen in the
pure C reproducer above.  So this is an upstream issue (but not
necessarily a bug).

Regarding SQLite3Result::columnName(): this is implemented as thin
wrapper of sqlite3_column_name(), about which the documentation[1]
states:

| The name of a result column is the value of the "AS" clause for
| that column, if there is an AS clause. If there is no AS clause
| then the name of the column is unspecified and may change from one
| release of SQLite to the next.

I have just added that to our docs[2].

Anyhow, if you need generic support for arbitrary queries, your
best bet is likely to use SQLite3::prepare(), and to check whether
the query is read only with SQLite3Stmt::readOnly()[3].  And of
course, bug #64531 should be fixed.

[1] <https://www.sqlite.org/c3ref/column_name.html>
[2] <http://svn.php.net/viewvc?view=revision&revision=349251>
[3] <https://www.php.net/manual/en/sqlite3stmt.readonly.php>
 [2020-02-25 13:53 UTC] mumumu@php.net
Automatic comment from SVN on behalf of mumumu
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=349252
Log: Clarify SQLite3Result::columnName()

Cf. bug #78813.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Nov 03 13:01:28 2024 UTC