php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33859 Failed SQLite assertion when using SQL 'AS'
Submitted: 2005-07-26 05:36 UTC Modified: 2005-07-27 06:08 UTC
From: leon at lost dot co dot nz Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5CVS-2005-07-26 (dev) OS: Linux (Debian Sarge)
Private report: No CVE-ID: None
 [2005-07-26 05:36 UTC] leon at lost dot co dot nz
Description:
------------
Attached snippet triggers an assertion everytime:

$ php -v
PHP 5.1.0-dev (cli) (built: Jul 26 2005 15:26:09) (DEBUG)
Copyright (c) 1997-2005 The PHP Group
Zend Engine v2.1.0-dev, Copyright (c) 1998-2004 Zend Technologies

$ php bug3.php
php: /tmp/php5-200507260230/ext/pdo_sqlite/sqlite/src/auth.c:117: sqlite3AuthRead: Assertion `pExpr->op==7' failed.
Aborted

Reproduce code:
---------------
<?php

// Setup sample database
$conn = new PDO('sqlite::memory:');
$conn->exec('CREATE TABLE barrel (docid INTEGER, wordid INTEGER, position INTEGER)');
$conn->exec('CREATE TABLE documents (id INTEGER PRIMARY KEY, key TEXT UNIQUE)');

// Run problem query
$sql = "SELECT count(*) AS count, key FROM ".
	"barrel, documents WHERE id == docid AND " .
	"wordid == 1 GROUP BY docid ORDER BY count DESC;";
$stmt = $conn->query($sql);
$result = $stmt->fetch();
print_r($result);

?>

Expected result:
----------------
Array
(
    [count] => 0
    [0] => 0
    [key] =>
    [1] =>
)


Actual result:
--------------
php: /tmp/php5-200507260230/ext/pdo_sqlite/sqlite/src/auth.c:117: sqlite3AuthRead: Assertion `pExpr->op==7' failed.
Aborted


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-07-26 09:28 UTC] helly@php.net
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions.  Due to the volume
of reports we can not explain in detail here why your report is not
a bug.  The support channels will be able to provide an explanation
for you.

Thank you for your interest in PHP.

Ask for SQLite support here: http://sqlite.org
 [2005-07-27 00:51 UTC] leon at lost dot co dot nz
You cannot mean to say that an SQL query should be allowed to CRASH a scripting language like PHP, surely!  Even if the SQL were incorrect (and mine wasn't) crashing PHP is not an option...

Prehaps I should have made myself more clear:

Triggering the assertion caused PHP to abort.  No page view, no HTML error messages, nothing but a frustrated user and an error in Apache's errorlog...

This is not a bogus bug report.
 [2005-07-27 01:13 UTC] iliaa@php.net
The only way to fix it would be for PHP to implement it's own SQL query parser, pre-scan user queries and determine if any disallowed keywords are being used. This is not only highly impractical, but would also make database communication code very slow.


 [2005-07-27 01:33 UTC] leon at lost dot co dot nz
With all due respect, that's complete and utter nonsense.

However on the postitive side, at least now I can see where  you were confused.  

Obviously you have assumed that the error was because my choice of alias is also a function name (prehaps you should have ran the code to actually test your assumption).  This turns out not to be the case.

The error still occurs if I use another alias (I've also  simplified the SQL to the bare minimum for you):

<?php
// Setup sample database
$conn = new PDO('sqlite::memory:');
$conn->exec('CREATE TABLE barrel (docid INTEGER)');

// Run problem query
$sql = "SELECT count(*) AS cnt FROM barrel ORDER BY cnt";
$stmt = $conn->query($sql);
$result = $stmt->fetch();
print_r($result);
?>

Also, the original example SQL was perfecly valid, as demonstrated by giving it to native sqlite3 command line program:

$ echo "SELECT count(*) AS count,key FROM barrel, \
  documents WHERE id == docid AND wordid == 3   \
  GROUP BY docid ORDER BY count DESC;" | 
  sqlite3 search.sqlite3
3|/main/library/index.html
2|/main/docs/page/printable.html
1|/main/apps/index.html
... and so on...
$
 [2005-07-27 03:47 UTC] wez@php.net
The bottom line is that the assertion is happening inside the sqlite library; it is therefore a libsqlite bug (because it is responsible for that particular condition never arising).

Now, it is possible that the way that PDO uses libsqlite is leading to that, so we can look into it more deeply.

Please also note that abusing us about our reasonable first impression isn't going inspire anyone to come running to your aid; why don't we keep it professional (even though we are volunteers and don't get paid for this)?

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 10 07:01:28 2024 UTC