~bohwaz/blog/

The SQLite3 module of PHP has some undocumented features that you may discover when you are reading the source code (yes that's what I do when I get bored, nothing is more fascinating than reading source code, really, you should try it).

Sqlite3Stmt::readOnly(void)

Available from PHP 5.3.5.

This method returns true if a statement doesn't change the database. This is useful sometimes, like to make sure that a template engine is only reading data and not altering the databse. Another use would be providing a way for the user to make queries on the database to select any data he wants (yes you wouldn't want this to be available to any user).

One example to check if there is no injection:

$_GET['id'] = 42;
$statement = $db->prepare('SELECT * FROM cooking_recipes WHERE id = '.$_GET['id'].';');
var_dump($statement->readOnly());
// bool(true)

And if there was an injection:

$_GET['id'] = 'NULL; DROP TABLE nuclear_warheads_locations;';
$statement = $db->prepare('SELECT * FROM cooking_recipes WHERE id = '.$_GET['id'].';');
var_dump($statement->readOnly());
// bool(false)

Of course you NEVER should insert any PHP variables straight in a SQL query. Instead you should use bindValue:

$statement = $db->prepare('SELECT * FROM cooking_recipes WHERE id = :id;');
$statement->bindValue(':id', $_GET['id']);

SQLite3::openBlob(string table, string column, int rowid [, string dbname])

Another undocumented feature of the PHP SQLite3 object, available since 2009, is openBlob. Basically it's a function that will return a stream pointer to a blob value in a table. Very very useful when you are dealing with files stored in a SQLite3 database.

Source code says:

proto resource SQLite3::openBlob(string table, string column, int rowid [, string dbname])
Open a blob as a stream which we can read / write to.

But despite that it's not possible to write to the blob, only reading is implemented. The blob are always opened as read-only by SQLite, so if you try to write to the stream, nothing will be saved to the database.

One example of use:

$db = new SQLite3('files.sqlite');
$db->exec('CREATE TABLE files (id INTEGER PRIMARY KEY, filename TEXT, content BLOB);');

$statement = $db->prepare('INSERT INTO files (filename, content) VALUES (?, ?);');
$statement->bindValue('filename', 'Archive.zip');
$statement->bindValue('content', file_get_contents('Archive.zip'));
$statement->execute();

$fp = $db->openBlob('files', 'content', $id);

while (!feof($fp))
{
    echo fgets($fp);
}

fclose($fp);

You can also seek in the stream. This is pretty useful for saving large files from the database too, this way you can use stream_copy_to_stream, it will be faster and more memory-efficient than dumping the file in memory before writing it to the disk.

Please note that openBlob() won't work on VIRTUAL FTS4 compressed tables (but no problem with non-compressed virtual tables).

SQLite3::enableExceptions([bool enableExceptions = false])

Available since 2009 (PHP version ??).

If set to true, then errors will be returned as exceptions instead of errors.