Even if you think the iPhone is an overpriced and buggy paperweight and the iPad just a larger version of the iPod, there is no denying the incredible popularity of both devices. Consumers have gone mad over both, with Apple selling 1.7 million new iPhone 4.0 units in the first three weeks of its release and the iPad selling three million in just eighty days.

That’s a heck of a lot of potential evidence containers circulating about. Now more than ever, forensic examiners need to understand and be able to acquire, exploit, and report on these devices.

Commercial applications for acquisition, analysis, and reporting on the iPhone, iPad, and iPod have come far, but as with any methodology or tool used for gathering and reporting of evidence in a court of law, they should be tested and validated.

This is why it is so important not only to understand the underpinnings of the device data structure, but also to use alternate means to examine what can be gleaned from it. The commercial tools can thus be vetted for use.

This article is concerned with understanding one of the structures used to store data on the iPhone and its siblings: the SQLite database.

Database Basics
Before we go too much farther, it would behoove us to take a momentary step back from the iPhone and SQlite and define what is meant by the term “database.” A database can be defined in terms of an analogy: simply put, it is a container composed of a file or set of files used to store organized data. Databases themselves comprise several elements.

Databases contain tables, which are structured lists that contain one specific type of data. A table cannot mix two different types of data, e.g. inventory and addresses. A table would need to be created for each different data type to be stored.

A table has a set of characteristics and properties that define the type of data that can be stored, how it is divided, etc. These property sets are known as the table’s schema. Schema can also be used to describe whole databases and the relationship from one table to another inside them.

Tables themselves comprise columns, so they can be imagined as a spreadsheet-like grid. Columns, or fields as they are sometimes known, are used to store individual pieces of data within a table. For example, an address table might have a column for street, one for city, one for zip code, and so forth.

Each column has an associated datatype, whether that is numeric, text, or some other type. Only the allowed datatype can go into its associated column for the table.

Tables store their data in rows. Each row contains a record, which itself is made up of columns. Again, this is very much like a spreadsheet.

Finally, each database should have at least one column that is a unique identifier. This column is called the primary key. Primary keys must be defined so that the data in the row can be updated, retrieved, or deleted. Any column can be designated as a primary key, but there are a few rules:

• No two rows can have the same primary key value.
• The primary key in the row must have a value.
• The primary key column cannot be modified.
• Primary keys cannot be reused in the same table for a different row once that key and its associated row have been deleted.

The SQLite Database Engine
Now that we have defined database basics, let’s turn our attention to the specific type of database engine that Apple uses in its mobile devices: SQLite, a software library that implements a self-contained, serverless, zero-configuration, transactional SQL (Structured Query Language) database engine.

Transactional databases cannot perform their transactions, i.e. updates to the database, if they encounter an OS fault, power failure, or some other reason. In these cases, the database is rolled back to its initial state before the update started. SQLite, unlike traditional Relational Database Management Systems (RDBMS) is not connected to a server and does not require configuration. It also does not require much support in the way of libraries from the host OS, therefore making it an ideal candidate for embedded systems.

Traditional RDBMS, unlike SQLite, often require multiprocessor computers with large amounts of memory and disk arrays for stable storage. They can be connected to other applications and rely on the operating system to provide networking and multitasking. A large corporation that requires a large number of processed transactions would perhaps have a number of networked servers running Oracle databases.

SQLite uses most of the same syntax that is present in SQL. A language that is specifically designed for interfacing with databases, SQL is easy to learn: it is composed of English words, non-proprietary, and supported by almost every database in the wild.

SQL Commands for Evidence Finding
We are now going to examine some basic SQL commands that will allow you to start interacting with the iPhone SQLite databases. I will be showing examples for sqlite3, a command line tool that is included with OSX.

Though many GUI programs exist for examining SQL Databases, I strongly recommend that you get to know SQL on the command line first as it will force you to learn the syntax and is applicable to any application you choose.

In order to interact with an iPhone database, say the SMS.db, you first need to load the database into the database management application. This is accomplished with sqlite3, with the simple command:

macbook-pro-2:sqlite-article mah$ sqlite3 sms.db

The application should then display the following:

SQLite version 3.4.0
Enter ".help" for instructions
sqlite>

Now that we have the database attached, we need to decide what tables inside the database are of interest to us. So we now need to dump the table's schema to see all the CREATE commands which are used to make tables. This is accomplished with the following command:

sqlite> .schema

Since I’ve loaded the SMS database, I’m going to be interested in a table that contains messages. I find exactly that:

CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER);

Now that we know what table we want from the database, we need to know the command to retrieve data from a database table and its syntax. To retrieve data from a particular table you use the SQL SELECT command. The SELECT command requires a minimum of two pieces of information: what it is you want to select (column) and from where you want to select it (table).

sqlite> SELECT text FROM message;

The “FROM” part of the above statement is called a clause. All SQL statements contain one or more clauses some of which are required and some of which are optional. The FROM clause in the SELECT statement is required.

By the way, you might have noticed the semicolon at the end of the command. This is required on multiple commands, but not for single commands. Always including it makes for a good habit.

You can also select multiple columns by naming each one followed by a comma:

sqlite> SELECT address, date, text FROM message;

You can use wild cards in your SQL statements. For instance, to select all the columns from the message table, you could use the following statement:

sqlite> SELECT * FROM message;

This is all well and good to dump the contents. But what if we are interested in all the messages sorted by date? Or sorted by address and date? To accomplish this we have to learn an optional SQL clause to sort the data—the ORDER BY clause.

sqlite> SELECT address, date, text FROM message ORDER BY DATE;

This command grabs the telephone number, the date and the text of the SMS and sorts it by ascending order—the default order. It is good to note also that SQL will return the columns in the order you specify them after the SELECT keyword.

You can also sort multiple columns. This would be useful if you wanted to see multiple messages from an address sorted by date.

sqlite> SELECT address, date, text FROM message ORDER BY date, address;

The last thing we are going to look at is how to filter your results. For instance, what if you were looking for a particular telephone number within the SMS database? Basic filtering can be accomplished using the WHERE clause.

To look for a specific telephone number in the SMS database sorted by date, use the following:

sqlite> SELECT address, date, text FROM message WHERE address = '+15555555555' ORDER BY date;

The WHERE clause also takes operators such as =. <, >= as well as others. An example of how to use a WHERE operator in a statement can be seen in the following:

sqlite> SELECT address, date, text FROM message WHERE date = 1273153661;

The above selects the address, date and text from the message table on a particular date. Incidentally, the SMS database stores time in Unix or Epoch format. The date above translates to 6-5-10 at 9:47 am.

We’ve now explored, at least at a high level, SQLite databases and how to interact with them. The SQLite database is used throughout Apple’s products as a primary way of storing information and is used extensively in applications found on the App Store. Understanding how and why they work is important in the testing and validation of your methods and tools. They are a rich treasure trove of potential evidence.

References

1. Harrison, Natalie. “iPhone Sales Top 1.7 Million”, 28 June 2010, Apple, Inc, 20 July 2010. http://www.apple.com/pr/library/2010/06/28iphone.html
2. Harrison, Natalie. “Apple Sells Three Million iPads in 80 Days”, 22 June 2010, Apple, Inc, 20 July 2010. http://www.apple.com/pr/library/2010/06/22ipad.html
3. Hwaci, “SQLite home Page”, 20 July 2010, Hipp, Wyrick & Company, Inc, 20 July 2010. http://www.sqlite.org
4. Forta, Ben Teach Yourself SQL In Ten Minutes, Second Edition, Indianapolis, Sams Publishing, 2001
    

Michael Harrington is the Director of Training and Services for Teel Technologies (www.TeelTechTraining.com). He has taught mobile device forensics around the globe and will be conducting a series of iPhone Forensic classes throughout the Fall in Washington, D.C., Toronto, Ontario, Las Vegas, NV, and at customer-specific locations. For more information, please contact Teel Technologies at Mike.Harrington@TeelTech.com, or call: (203) 855-5387.