SQLite: the good, the bad, the embedded database

SQLite is an embedded, open-source, lightweight SQL database engine. The C based library is transactional, self-contained, and highly compact. It’s also fairly easy to implement. It doesn’t require any sort of installation or configuration, and all data is stored locally. This is very differently from a standard Oracle or MySQL database, so don’t make the mistake of thinking they are interchangeable.

The compact and efficient nature of SQLite has led it to become very common in mobile development. The library understands most standard SQL commands, with a few exceptions (such as missing right/full outer joins, limited alter commands, and the inability to write to views).  It also has bindings for a large array of popular programming languages and is included by default on most smartphones. SQLite has even expanded into on-disk file formats for desktop apps and low traffic web sites.

With a small footprint, server-less databases, readable source code, and cross-platform capability, there are plenty of advantages. But… we are tech people; we know the other side of that coin. There’s always a price to pay.

So what are the disadvantages of SQLite? Well, to begin with, the database is stored as a single file (which can be located anywhere in the directory hierarchy). While this may seem convenient, any rogue process can open the database file and overwrite it. SQLite has no way to defend against this, so security must be performed at the file level. Set permissions carefully, keep files out of the web root, and/or use an .htaccess rule to prevent unauthorized viewing. You’ll also want to make sure to sanitize user input using sqlite_escape_string(), since SQL Injection is still an issue.

Another security concern is a feature called journaling. When changes are made, the SQLite database maintains separate “journal” or “WAL” files to facilitate roll backs. These files are generally temporary and get deleted when the transaction commits or rolls back. However, there are 3 conditions that can interfere with journal deletion.

1.    If a crash occurs mid-transaction, the -journal or -wal file is stored to disk until the next use.
2.    Developers have the option to set journaling mode to PERSIST (which prevents the journal from being deleted).
3.    Developers also have the option to put SQLite into exclusive locking mode (often done for performance). With this option, the rollback journal might be truncated or have it’s header zeroed (depending on which version you’re using) but the journal itself is not deleted until SQLite exits the exclusive lock mode.

These conditions could present serious security concerns for a database handling sensitive data (even if it is only being stored temporarily). So how do we protect it? Well, theoretically, it is possible to turn off journaling at the source level. However, this is not recommended. If journal files are missing when the application crashes, your database will likely be corrupted.

In the end, it seems like the best solution for storing sensitive data in SQLite is to encrypt it before storage. If you prefer not to encrypt the data yourself, SQLite has an extension called SQLCipher that will perform encryption. The commercial version does have a fee, but the community edition is open source.

Donna Fracarossi is a Security Analyst at Secure Ideas. If you are in need of a penetration test or other security consulting services you can contact us at info@secureideas.com or visit the Secure Ideas – Professionally Evil site for services provided.