SQLite and Python in DFIR

SQLite databases are being used in more and more applications, and thus forensic examiners are increasingly running across them in investigations.  Python seems to be one of the languages of choice for the DFIR community, and so SQLite and Python often intersect.  I've developed two open source tools, Hindsight and SQUID, both of which use Python to access SQLite databases. I've run across a problem using Python with newer SQLite databases, and users of my tools may encounter the same problem if they are using the Python versions (the compiled versions avoid the issue). I thought I'd share a solution to hopefully save people some time and frustration getting things working.

New Features and New Problems

SQLite is under active development, with the newest release (3.9.2) happening just a few weeks ago. This is great, since bugs are being fixed and new features are being added to extend SQLite's capabilities.  However, one of these new features was the source of my problems. Actually, that's not true. The problem was that the version of SQLite my Python was using didn't support that new feature, while the database I was trying to access was using it.

The new feature was partial indexes, and I first encountered them while looking into a new Chrome version (surprising, right?).  A few versions ago, Chrome started using the partial indexing feature in the Cookies SQLite database.  Some of the SQLite tools I was using could access the Cookies file, while some (including Python) would throw an error. Trying to access the Cookies file resulted in the first error message below, and I've also ran across the second with other databases:

sqlite3.DatabaseError: malformed database schema (is_transient) - near "where": syntax error

sqlite3.DatabaseError: file is encrypted or is not a database

I eventually figured out that the version of SQLite that came with my (Windows) Python 2.7 installation was too old.  Downloading a newer Python 2.7 for Windows won't help; the latest installer (2.7.10) still ships with SQLite 3.6.21, which was released in 2009!  This version of SQLite is missing way more features that just partial indexes, and if you are running Python 2.7 on Windows, there's a good chance it's the version you're using.  You need SQLite version 3.8.0 (released in 2013) or later to use the partial index features.  (Quick note: the Python 3.5 installer for Windows ships with SQLite 3.8.11, but quite a few useful Python scripts are only usable with 2.x Python series.)

Upgrading Python's SQLite

How do we remedy this? For Windows, it's pretty simple.  First, check if you even need to do the upgrade.  Launch Python, import sqlite3, and check the version. But which version?

C:\Users\Ryan>c:\Python27\python.exePython 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.6.21'
>>>

sqlite3.version gets you the version of the Python 'sqlite3' database adapter module.  The version we care about - the actual version of SQLite - is sqlite3.sqlite_version.  That's not confusing at all!

To fix this on Windows, you can download the precompiled SQLite DLL.  Choose the appropriate version from the SQLite download page; I grabbed 'sqlite-dll-win32-x86-3090200.zip', because I'm running 32-bit Python on Windows, even though I have a 64-bit machine.  Extract 'sqlite3.dll' from the downloaded archive and place it in your Python installation's DLLs directory.  I have Python installed to C:\Python27, so my DLL folder is C:\Python27\DLLs.  There should already be a 'sqlite3.dll' in that folder; you can rename it or overwrite it, depending on how cautious you are.  Once that is done, launch Python again and check your SQLite version:

C:\Users\Ryan>c:\Python27\python.exe
Python 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] on win32Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.9.2'
>>>

All fixed! Python should now be able to access SQLite files that use the newer features.