Python Forensics – SQLite Investigations Part One

SQLite has grown in popularity over the past several years, especially for use in embedded applications that require local/client storage such as web browsers, dropbox and Skype. In addition, SQLite is embedded in iPhone, iPod touch and the iTunes applications, and Android, Microsoft and other popular operating system platforms also use SQLite in a variety of applications. This versatile database has some limitations, but for lightweight embedded applications it has become the ‘go to’ database.

For these reasons I’m often asked, “Can evidence be easily extracted from SQLite databases using a Python script? If so how can I build one?” The answer of course is yes, and there are quite a few examples of Python code snippets that demonstrate the basics. However, in many cases these examples lack detailed explanations and are not directly targeted at forensic interrogation of the databases. This makes it difficult to apply the snippets within a forensic context.

So…. I’ve decided to dedicate a blog series to Python SQLite Forensics. The series will take a deep dive into examining SQLite databases using Python, and will be presented in my normal style of providing you with every detail of how to do this. Following, of course, the same style as my book “Python Forensics, A workbench for inventing and sharing digital forensic technology”.

Databases contain a set of tables and associations defined as a schema. Here’s a nice example of a SQLite schema, provided by Mozilla showing the relationship of information stored by a web-browser.

Part One – The Basics

In Part One, I’m going to start simple and create the basics necessary to dump the SQLite database to a set of .csv files each representing a table in the database with the column headings and contents.


1. Allow the user to specify the SQLite database file to examine
2. Extract all the table names associated with the database
3. Extract the field / column headings of each table
4. Extract the contents of each table and create an associated .csv file
5. Provide complete exception handling
6. Provide fully documented source code with detailed comments

Special Note:  Make sure that you are using the latest SQLite.dll.  You can download the latest under Windows binaries from and then replace the existing file in your Python27 folder.  This will ensure compatibility with the latest implementations.

Executing the SQLite Script

Running the program from the command line using the –h or help option shows the basic operation.





Running the application against an actual SQLite database, (in this case the main.db from Skype) delivers the following results. As you can see the Skype database has many tables that could provide valuable information regarding user activity.








Examining the results directory, you can see that .csv files were created for each database table.






Examining the Python Script

The Python script below contains detailed comments and information that should get you started interrogating SQLite database files with Python.

In Part Two, I will be examining the relationships between tables and providing basic search and information identification code, so stay tuned.

Note… can also follow this blog feed to stay tuned in.

# Python Forensics
# SQLite Part I: Basic SQL Database Dump
# Dumps the table names and contents of each table
# by creating a Comma Separated Value (CSV) with the contents
# of each table
# Sample code with detailed comments.  
# usage: python -v -i .\main.db .\result
# Python Version 2.7.x
# Version 1.1  June 30, 2014

Copyright (c) 2014 Chet Hosmer, Python Forensics, Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software
and associated documentation files (the "Software"), to deal in the Software without restriction, 
including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, 
and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, 
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial 
portions of the Software.


# Import the standard library module sqlite3
# This type of import allows you to abbreviate the interface
# to sql methods.   i.e. sql.connect  vs sqlite3.connect
import sqlite3 as sql

# import the system module from the standard library
import sys

# import the standard library csv to handle comma separated value file I/O
import csv

# import the Operating System Module this handles file system I/O operations and definitions
import os

# import the Standard Library Module of handling program arguments
import argparse

# Local Classes and Method Definitions

# Class: CSVWriter 
# Desc: Handles all methods related to comma separated value operations
# Methods  constructor:    Initializes the CSV File and writes the header row supplied (as a List)
#          writeCVSRow:    Writes a single row to the csv file
#          destructor:     Closes the CSV File

class CSVWriter:

    def __init__(self, csvFile, heading):
            # create a writer object and then write the header row
            self.csvFile = open(csvFile, 'w')
            self.writer = csv.writer(self.csvFile, delimiter=',',quoting=csv.QUOTE_ALL)
            print "CSV File: Initialization Failed"

    def writeCSVRow(self, row):
            rowList = []
            for item in row:

                if type(item) == unicode or type(item) == str:
                    item = item.encode('ascii','ignore')



            print "CSV File Write: Failed" 

    def __del__(self):
        # Close the CSV File
            print "Failed to close CSV File Object"

# End CSV Writer Class ====================================

# Display Class
# Replaces basic print function with two advantages
# 1. It will only print to the console if verbose was selected by the user
# 2. It will work with both Python 2.x and 3.x printing 

class Display():

    def __init__(self, verbose):
        self.verbose = verbose
        self.ver = sys.version_info

    def Print(self, msg):
        if self.verbose:

            if self.ver >= (3,0):
                print msg        

# Display CLASS

# Name: ParseCommand() Function
# Desc: Process and Validate the command line arguments
#           use Python Standard Library module argparse
# Input: none
# Actions: 
#              Uses the standard library argparse to process the command line
# For this program we expect 3 potential arguments
# -v which asks the program to provide verbose output
# -i which defines the full path and file name of the sqlite database to dump
# -d which defines the directory where the resulting table dumps should be stored
def ParseCommandLine():

    parser = argparse.ArgumentParser('SQL DB Dump')

    parser.add_argument('-v', '--verbose', help="enables printing of additional program messages", action='store_true')
    parser.add_argument('-i', '--sqlDB',   type= ValidateFileRead,  required=True, help="input filename of the sqlite database")
    parser.add_argument('-o', '--outPath', type= ValidateDirectory, required=True, help="output path for extracted tables")    

    theArgs = parser.parse_args()           

    return theArgs

# End ParseCommandLine()

# Name: ValidateFileRead Function
# Desc: Function that will validate that a file exists and is readable
# Input: A file name with full path
# Actions: 
#              if valid will return path
#              if invalid it will raise an ArgumentTypeError within argparse
#              which will inturn be reported by argparse to the user

def ValidateFileRead(theFile):

    # Validate the path is a valid
    if not os.path.exists(theFile):
        raise argparse.ArgumentTypeError('File does not exist')

    # Validate the path is readable
    if os.access(theFile, os.R_OK):
        return theFile
        raise argparse.ArgumentTypeError('File is not readable')

# End ValidateFileRead()

# Name: ValidateDirectory Function
# Desc: Function that will validate that the directory exists and is writable
# Input: Path to a Directory 
# Actions: 
#              if valid will return path
#              if invalid it will raise an ArgumentTypeError within argparse
#              which will inturn be reported by argparse to the user

def ValidateDirectory(theDirectory):

    # Validate the path is a valid directory
    if not os.path.exists(theDirectory):
        raise argparse.ArgumentTypeError('Directory does not exist')

    # Validate the path is writable
    if os.access(theDirectory, os.W_OK):
        return theDirectory
        raise argparse.ArgumentTypeError('Directory is not writable')

# End ValidateDirectory()

# Main Function Starts Here

# Main program for SQL Dump
# Input: 
#       verboseFlag: used to be loud or silent in processing
#       theDB:       full path and filename of the input sqlite database file
#       outPath:     the path of the designated results directory

def main(verboseFlag, theDB, outPath):

    p = Display(verboseFlag)
    p.Print("Python Forensics: SQLite Investigation Part One - Simple Database Dump")

        # attempt to connect to a database file
        # this example uses the skype main.db that I have copied into
        # my local directory for easy access

        db = None
        db = sql.connect(theDB)

        # sql requires a cursor 
        # A database cursor is a  structure that enables you traverse over the records in a database
        # Cursors facilitate operations such as retrieval, addition and deletion of records contained
        # in a database

        dbCursor = db.cursor()    

        # Now let's utilize the cursor to execute a simple SQL command
        # that extracts the table names from the database

        dbCursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

        # The next statement fetches all the results from the table query

        tableTuple = dbCursor.fetchall()

        # For good measure let's print the list of tables
        # associated with this datbase

        p.Print("Tables Found")

        for table in tableTuple:

        # Now we have all the table names in the object tableTuple
        # We can interate through each tuple entry

        for item in tableTuple:

            # For this particular tuple we are only interested in the first
            # entry which is the name of the table

            tableName = item[0]
            p.Print("Processing Table: "+tableName+"\n")

            # Now we can use the table name to extract data
            # contained in the table

            tableQuery = "SELECT * FROM "+tableName 

            # We will use the cursor to execute the query
            # and then collect the row data using the fetchall() method


            # Obtain the table description
            tableDescription = dbCursor.description      

            # Create a heading for each table
            tableHeading = []
            for item in tableDescription:

            oCSV = CSVWriter(outPath+os.sep+tableName+'.csv', tableHeading)

            rowData = dbCursor.fetchall()

            # Now we can interate through the row data
            # and write the results to the associated CSV file

            for row in rowData:


        p.Print ("SQL Error")


        if db:

    p.Print("End Program")

# End Main program

# Main Program Entry Point

# Processes the user supplied arguments
# and if successful calls the main function
# with the appropriate arguments

if __name__ == "__main__":

    args = ParseCommandLine()

    # Call main passing the user defined arguments

    main(args.verbose, args.sqlDB, args.outPath)
Posted in Example, Source Code | 2 Comments

First Python Forensic Script Challenge Winner Selected


Congrats to John Carney from for submitting the best new Python Forensic Script Idea at the 2014 TechnoSecurity Conference held in Myrtle Beach, SC June 1-4.

Watch for solutions to the script challenge in coming weeks.


Posted in Announcement | Leave a comment

Python Forensics Book Launch

TechnoSecurity 2014

Python Forensic Book launched at TechnoSecurity 2014. Stop by booth 616 for a chance to win a free book.


Posted in Announcement | 4 Comments

PFIC 2013 Python Labs

Thanks to everyone that attended the Python Labs at PFIC 2013.

As promised, I have included the lecture and Labs with full source code.


Total Downloads: 7,803


Posted in Example, Source Code | 1 Comment

HTCIA International Conference 2013

Thanks to everyone who attended the Python-Forensics Lab at HTCIA International.

The presentation and lab are available for download. Enjoy.
Download Lab and Presentation
Download Count 1,641

Posted in Example, Source Code | Leave a comment

Ubuntu and Python a nice couple

I’m often asked: What is the best environment for developing Python applications?

The answer of course is that depends, mostly on your preferences. The great thing about Python is whether you are most comfortable on a Mac, Windows 8 or Linux you can enjoy the same integrated development environment.

However, with the advent of Ubuntu 12.x LTS (Long Term Support version) it certainly rises to the top for Linux. This version is guaranteed to be supported with updates and security patches until April 2017.

In addition, Python 2.7.3 comes installed as part of the base installation. Also, the Ubuntu Software Center is available once installed and by searching for Python a plethora of additional resources and downloads are available to enhance your Python experience.


Posted in Announcement | Leave a comment

Using Hex and Binary Numbers in Python

One of the first questions forensic investigators ask about when writing python programs or scripts is how do I handle Hex and Binary numbers and perform simple operations?

Python has built in intuitive capabilities to handle such numbers. Remember Python is designed to be as easy to read as English.

Opening the Python shell we can see how easy this really is.

> python
Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit (Intel)]
Type “help”, “copyright”, “credits” or “license” for more information.

# First set the variable named value = to the decimal number 127
>>> value = 127
# displaying the number in hex as you would suspect is as easy and saying
# show me the hex representation of the variable “value”. using the proper syntax of course
>>> hex(value)
# I like to see my hex numbers in all caps, I know old school
# so I add on the upper() function as shown below
>>> hex(value).upper()

#displaying the number in binary works the same way
>>> bin(value)
# what if we want to “Exclusive Or” two hex values together?
# we first set variable A = to a hex 20 and variable B = to a hex 40
>>> A = 0x20
>>> B = 0x40
# then we use the carrot operator to create the new variable C
# (this operator represents “Exclusive Or” in most languages)
>>> C = A ^ B

# then we use the hex function once again to display the result
>>> hex(C).upper()

# and of course we then would like to display the variable C in binary
>>> bin(C)

As the saying goes “as easy as pie”
One of the earliest uses of this idiom was in a comic story found in the The Newport Mercury (a Rhode Island Newspaper) back in 1887.

Posted in Example | Leave a comment

Python-Forensics @ Techno Security

A Python-Forensics lecture, demonstration along with a mini training session was held at the 15th annual Techno Security Conference in Myrtle Beach, SC.

Over 50 attendees participated and we had a great interchange of ideas.

Thanks to all that participated.

Posted in Announcement | 3 Comments

Quick Hash Python Script Added

Check out the Quick Hash Python Script and submit ideas for improvement

Posted in Announcement | 1 Comment


Welcome to

Our mission is to bring together researchers, developers, investigators and anyone else that is passionate about investigating cyber-crime, to build python based forensic tools that are freely shared with the world.

Posted in General | Leave a comment