Why is sqlite so cool and why should I care
by Kevin Waterson
Contents
With the inovation of PHP5 comes many new and needed features and functionality.
Not least among these is the inclusion of a bundled database, sqlite.
Some will argue that there is no need for this as PHP is well served with databases
such as MySQL, Firebird, PostgreSQL, Oracle and many others. This is true, however,...
SQLite is in no way intended to be a replacement for your existing database server and
is more suited to those who have no access to a local or remote SQL server.
This can be useful for many applications in the way data is accessed.
SQLite is a lightweight, public domain database engine written in C, that was developed by D. Richard Hipp of Hipp, Wyrick & Company, Inc., and is freely available from http://www.hwaci.com/sw/sqlite/. The inclusion of SQLite as a standard SQL driver for flat files will ensure many applications have access to better management of information as opposed to the existing methods of flat file management. Many applications developers choose flat file management to ensure the application will run on as many shared servers as possible. The problem then arises of the code base becoming very messy.
Chained Queries
Like many other databases, SQLite allows chained queries. These are queries of the
type
SELECT * FROM table1; SELECT * FROM table2; SELECT * FROM table3;
This has been seen by some to be a security hole as it could allow an unfriendly user
to do something nasty from within a PHP script like
SELECT * FROM table1; drop table1;
PHP5 will do many new things, but it will not save you from sloppy coding practices.
Here the golden rule is to always verify your data, in particular, verify user input.
All input obtained from the client should be considered unsafe and should be checked
for correctness. Every article ever written on the most basic security problems will
tell you this. It is a fundamental cause of security issues within many applications,
not just PHP. You could of course write a little function to stop chained queries like this:
<?php
if ($db = sqlite_open('sqlite.db', 0666, $sqliteerror)) {
if(noChains($sql)==TRUE){
echo 'Sorry, Chained Queries are not allowed!';
} else {
sqlite_query($db, $sql):
}
}else{
die($sqliteerror);
}
// this function checks if a query string contains
// a chained Query
function noChains($sql){
function ($string, $match = ";", $delim = "'")
{
$ignore = false;
for ($i = 0; $i < strlen($string); $i++)
{
if ($s{$i} == $delim)
{
$ignore = !$ignore; continue;
}
if ($s{$i} == $match && $ignore) return true; } return false; }
}
?>
Similarly, you may wish to simply not stop a script with an error message and allow just the first query to be executed and any further queries to be simply ignored
<?php
if ($db = sqlite_open('sqlite.db', 0666, $sqliteerror))
{
if(noChains($sql)==TRUE)
{
$sql=singleQuery($sql);
}
else
{
sqlite_query($db, $sql):
}
}
else
{
die($sqliteerror);
}
// this function checks if a query string contains
// a chained Query
function noChains($sql){
// regex goes here and returns TRUE if a chained query is found
}
function singleQuery($sql){
// some string replace code here
$return $sql;
}
?>
Casting and quoting your field values are just some of the good practices when working with a database. However you go about it, it's imperative to understand your underlying data, datasource, and exactly how the data gets sent to and from your datasource.
This is a simplistic example and much more can be done to secure your applications such as simple bounds and type checking, but that is beyond the scope of this article. Here we point out only that this is not a security hole any more than simple security measures that should be used when using any database, not just SQLite. If you are unwilling to verify your safety of your user input you should use a database that supports params and bindings.
Session Management
There has been much debate over the use of SQLite as a tool of session management, and
it would seem that the outcome has some bonuses and some failings. Some of the those
opposed to session management have failed to realise that some of the alleged disadvantages
of using SQLite are its greatest assets.
Lets look at the two biggest hurdles that have aggrieved some users.
Single file for entire database:
No practical advantage
It has been put by some that SQLite is not intended for a write intensive environment as it uses a single file for the database. This could pose problems when updating sessions. Every time a session is updated the database file is locked and so that multiple sessions must be updated one at a time rather than together. Another problem could be corruption. Should a single SQLite based session be corrupted, all sessions will be lost. Having a single point of access implicitly decrees a single point of failure and a single point of performance degredation.
We see the single file database a major feature of SQLite. It provides the same benifits as myiam datafiles in this respect, while at the same time offering a clean and manageable code by allowing us to identify and destroy a session out of band. While the single point of failure arguement does hold water it has little meaning in this context as the mm session handler is a single point of failure. Single file management also has benifits with regard to maintainence, it is much easier to delete or a single file rather than many session files in /tmp, also, finding and manipulation of data outside normal sessions framework becomes much easier, as does moving sessions across servers.
Benchmarks I have seen regarding session management do show a decrease in performance under high loads, but under low loads this seems of little consequence. As a SQLite based session write is only a small part of a script, the performance penalty does not propogate linearly but remains a constant, thus longer running scripts will no incur an exponetial slowdown. It is correct that enterprise type sites would not benifit from the use of SQLite based sessions but, enterprise sites would most likely not be using SQLite.
Binary Storage
In an article published in PHP Magazine, I described database storage as an alternative
to file based storage. Good News. SQLite performs extremely well with binary data and PHP5 already
has in CVS functions to encode and decode binary data. This is required as SQLite is 8-bit clean and
data needs to be converted to ASCII. The functions sqlite_udf_decode_binary() and sqlite_udf_encode_binary()
take care of this for us, much in the same way as addslashes() might in other implementations.
Summary
SQLite is fully self contained within PHP5 and as such, needs no external libs like MySQL and PostgreSQL. SQLite has no external dependancies and will work right out of the box. No users are required with SQLite so admins
can rest easy and not need to fuss about with user management. SQLite supports most of SQL92
and database sizes up to 2 terabytes (2^41 bytes) in size. Built and tested under Linux and Windows, SQLite
sources are in the public domain and can be used for any purpose. The speed and feature rich environment
will solve and improve many applications that currently use flat files in an effort to work on as
many platforms as possible. The high performance bindings for PHP, and their ease of use and installation, make SQLite well worth a look. A PHP5 powered Admin application is currently under development at sourceforge.net to make the use of SQLite as simple as MySQL an others.