Getting to know the Relatives: SQLite Databases

I was contacted by a colleague who needed some help analyzing a SQLite database.  It was the myspace.messaging.database#database located in the "\Users\\appdata\local\google\chrome\userdata\default\plugin data\google gears\messaging.myspace.com\http_80\" folder.  I didn't and still don't know a whole lot about this file, but it appears to contain myspace email messages.

The Challenges of SQLite

Let's face it: SQLite is everywhere.  Understanding it is essential to good examinations, and a big part of that understanding come from learning SQL statements. There are many good online sources for learning SQL, and one of my favorites is w3schools.com.

But, for digital forensics practitioners, there is another challenge beyond understanding SQL commands--understanding the construction and relationships of the tables.  SQLite is a relational database, and the tables are meant to be related to one another to produce a result no possible or impractical from a single table.  Knowing how the table was intended to be used can be very difficult... after all, a SQLite database is more akin to a file cabinet, not a secretary who uses the file cabinet.

For example, the secretary can place company bank records in a file called "Financial Records" or she can put them in a file called "Artichokes".  It really doesn't matter, because she knows what goes in the file.  Someone coming along behind her won't have much trouble finding the bank records in the Financial Records file, but might overlook them them entirely in the Artichokes file.  The point is, without the secretary, it might be very hard to understand the filing system.

SQLite databases can be a lot like that.  You can see the structure, or the schema as it is called, very easily.  But what is not so easily understood is how the structure is intended to be used.  That mystery is usually locked up in the application that utilizes the database, but it is not explained in the database itself.

Getting a Clue

To be sure, there can be hints about how tables in a database interrelate.  Table and field names often speak volumes.  A database called "AddressBook.db" with two tables called "Names" and "Addresses" that have a field in common called "SubjectID" isn't too hard to fathom.  If we are lucky enough to be able to run the application that uses the database and test our inferences based on the applications output, our confidence grows (and our understanding, if supported by the outcome, would now be considered reliable).

My favorite hints by far are SQL 'view' statements.  These are virtual tables that draw their data from other tables in the database (or an attached database).  By studying a view statement, you get insight from the database creator how the database was intended to be used... at least in one capacity.  Think of a view as a macro: it saves the database user the trouble of repeated typing a frequently used query.  And, if the query is frequently used, then you have a good sense of how the database was intended to be used.

What if There are No Clues?
What about circumstances in which there are no clues in the database to help us understand its use.  Well, if there are really no clues, then the only safe answer is we look at the data flat, that is to say, we look at the tables individually and we don't relate them in any way.  But, there are often less obvious clues than can reveal an underlying relationship... which brings me to the point of this article.

Latent Rows

Latent fingerprint examiners know the term "latent" means hidden or invisible.  Latent fingerprints must be revealed to be seen by some external method, such as fingerprint powder.  SQLite tables have a latent field, so to speak.  And, we can reveal it to help us form relations in a SQLite database.

Consider the myspace.messaging.database#database I mentioned in the open paragraph. It has the following schema:

CREATE VIRTUAL TABLE AuthorData USING fts2(AuthorDisplayName, AuthorUserName); 
CREATE TABLE AuthorData_content(c0AuthorDisplayName, c1AuthorUserName); 
CREATE TABLE AuthorData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE AuthorData_segments(block blob); 
CREATE TABLE AuthorMetaData (AuthorId INTEGER PRIMARY KEY, AuthorImageUrl TEXT); 
CREATE VIRTUAL TABLE MessageData USING fts2(Subject, Body); 
CREATE TABLE MessageData_content(c0Subject, c1Body); 
CREATE TABLE MessageData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE MessageData_segments(block blob); 
CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER); 
CREATE TABLE UserSettings (UserId INTEGER PRIMARY KEY, MachineId TEXT, Enabled INTEGER, TimeStamp INTEGER, LastSyncTimeStamp INTEGER, FirstRunIndexPass INTEGER, FirstRunIndexTargetCount INTEGER, OldestMessageId INTEGER, LastServerTotalCount INTEGER); 
CREATE INDEX AuthorIdIndex ON MessageMetaData (AuthorId, RecipientId); 
CREATE INDEX StatusIndex ON MessageMetaData (Status, CreatedDate);

Now look more closely at two tables of interest,MessageMetaData and MessageData_content:

CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER);

CREATE TABLE MessageData_content(c0Subject, c1Body)

It would seem from the table names that MessageMetaData contains information about the messages, and MessageData_content contains the messages themselves.  But, they don't share any fields that allow the two tables to be related. In other words, which rows of the metadata table correspond to which row of the content table?  Do they even correspond at all?

Let's look at our first hint or correspondence:

$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageMetaData;'

1358 
$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageData_content;'
1358

Both tables have the same number of records.  Hmm, a clue?  Quite likely, especially upon study of the table content and the remaining tables contents.  In fact conducting a similar study, we find another set of table correspondence: AuthorMetaData and AuthorData_content also have an equal number of records (172, to be exact) but no obvious, interrelated fields.

Unless you've studied SQLite construction in any depth, you probably don't know that it creates a 'rowid' field for every table to act as a primary key.  If a table is created with a defined primary key, that primary key is just a alias to the builtin rowid (with one exception outside the scope of this discussion).  But the rowid is not represented in the table or database schema, which is probably why you didn't know about it (at least, I didn't until I bought a SQLite book).

Knowing about the rowid, i can now check to see if the two tables have matching rowid fields:

$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageMetaData m, MessageData_content c where m.rowid = c.rowid'
1358 

We don't have to trust the count function, take a look for yourself:

$ sqlite3 myspace.messaging.database#database.db 'select m.rowid, c.rowid from MessageMetaData m, MessageData_content c where m.rowid = c.rowid'
...
81407357|81407357
81416917|81416917
81504605|81504605
81505714|81505714
81530947|81530947
81569294|81569294

Well, now this is even more interesting.  We not only have two tables with the same number of rows, but we have two tables with fields in relation, i.e., rowid!  

Understand that rowid is simply an autoincrementing, unique, 64-bit integer unless specifically declared otherwise by insert and update commands.  But is this just a coincidence?  Let's consider: we have non-sequential rowids throughout both tables.  That might be explained by dropped rows from the tables.  But two tables, each with 1358 rows, and each row having a matching rowid in the other table?  That is more than coincidence--it's programatic.  The application populating the tables is assigning the rowids.

The Proof is in the Pudding

My assertion is that the myspace.messaging.database#database.db is assigning the rowids as it populates the related tables and links the rows by matching rowid.  Let me demonstrate how rowid can be assigned:

sqlite> create table numbers(digit integer);
sqlite> insert into numbers (digit) values(1);
sqlite> insert into numbers (digit) values(2);
sqlite> insert into numbers (digit) values(3);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
sqlite> insert into numbers (rowid, digit) values (1000, 4);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
1000|4

I created at table called "numbers" with one field called "digit."  I then inserted three rows in the table with the values 1, 2, and 3 respectively.    If you've been following along, you now know that every SQLite table also has a rowid field, even if not expressly created in the table by the user.  The first select statemnt shows the autogenerated rowid and along with the digits I inserted.

The final insert statement is different.  Here I assign the rowid, rather than let it be automatically populated by the SQLite engine.  And, as you an see in the final select statement, I succeed in setting an non-sequential rowid.

Putting it All Together

I've demonstrated a "hidden" way that tables in SQLite databases can be related.  It takes some knowledge in SQLite structure and the SQL query language to unveil this data, however.  If you are in the habit of relying on SQLite browsers and looking at tables without relating them, then you are really missing out on a wealth of data.

Again, let me illustrate using the myspace.messaging.database#database.  Lets look at one row in each of the tables I mentioned previously:

$ sqlite3 -header myspace.messaging.database#database.db 'select * from MessageMetaData limit 1;'
MessageId|RecipientId|AuthorId|Folder|Status|CreatedDate
1289081|544962655|41265701|0|2|1280870820000 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from MessageData_content limit 1;'
c0Subject|c1Body
Hi|Hey, what's up? 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from AuthorMetaData limit 1;'
AuthorId|AuthorImageUrl
-1930729470|http://some_url/img/some_image.png 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from AuthorData_content limit 1;'
c0AuthorDisplayName|c1AuthorUserName
A User|auser

The only hint of relationship, besides table names, is the AuthorID field in MessageMetaData and AuthorMetaData.  But there is still no obvious way to tie the metadata to the content we are most interested in.  Your favorite GUI browser maybe make the display prettier, but its just as impotent.

But, now that you have knowledge of the rowid, and have a link to a tutorial on SQLite statements, you're not too far from being able to do this:

sqlite3 -header myspace.messaging.database#database.db 'select messageid, datetime(createddate/1000, "unixepoch", "localtime") as Date, mm.AuthorID, c0AuthorDisplayName as "Author Display Name", c1AuthorUserName as "Author Username", c0subject as Subject, c1Body as Body from  MessageMetaData mm, MessageData_content mc, AuthorData_Content ac, AuthorMetaData am where mm.AuthorID = am.AuthorID and am.rowid = ac.rowid and mm.rowid = mc.rowid limit 2;'
MessageId|Date|AuthorId|Author Display Name|Author Username|Subject|Body1289081|2010-08-03 14:27:00|41265701|A User|auser|Hi|Hey, what's up?

I ask you, on which output would you rather examine and report?

Addendum

That last query is really not so scary.  It's just long because we're grabbing seven fields from four tables, and converting a date stamp.  But, in reality, it's very straight forward.

Let's take a look:

select
     messageid,
     datetime(createddate/1000, "unixepoch", "localtime") as Date,
     mm.AuthorID,
     c0AuthorDisplayName as "Author Display Name",
     c1AuthorUserName as "Author Username",
     c0subject as Subject,
     c1Body as Body 
from 
     MessageMetaData mm,
     MessageData_content mc,
     AuthorMetaData am,
    AuthorData_Content ac 
where
     mm.AuthorID = am.AuthorID
     and am.rowid = ac.rowid
     and mm.rowid = mc.rowid;

The select clause simply picks the fields we want to display.  The datetime function converts the unixepoch time, which is recorded in milliseconds, to local time.  The 'as' statements are naming the columns something more user friendly and are not required.

The from statement simply declares what tables to query for the fields we are trying to display.  Each table is followed by an alias I chose to make easier reference to field names common to more than one table.  For example, AuthorID is found in both the MessageMetaData and AuthorMetaData tables.  By giving MessageMetaData the alias of mm, I can now reference the MessageMetaData.AuthorID field as mm.AuthorID.

The where statement is a filter.  It keeps the tables 'aligned,' so to speak.  It ensures that only the correct author content and message content is returned for each row.  This post is a lot long in the tooth, so I won't go into detail describing how it works.  But, very succinctly, the MessageMetaData record is matched to a AuthorMetaData record by AuthorID.  The the AuthorMetaData record is matched to its corresponding AuthorData_Content record by rowid.  Finally, the MessageMetaData record is matched to its corresponding MessageData_content, also by rowid.

Who's Texting? The iOS6 sms.db

It didn't take long from the release of iOS6 on 09/14/12 until I got my first iOS6 device to process: 18 days.  In this case, it was an iPhone4 with iOS6.  In so short a time, there are not too many commercial or open source tools out there prepared to analyze the new OS or its new data formats.

Fortunately for me, the phone was not locked.  To process the phone, in which the prime interest was text messages and contacts, I used the latest iTunes to create a backup of the phone.  Fortune was again on my side: the phone was not set for encrypted backups.

iTunes Backup

If you are not familiar with iTunes backups, then allow me to introduce them generally.  iTunes copies user data (media, databases, settings, applications, etc.) to a directory in the operating system.  The location of the directory varies by operating system and version.  Since this is primarily a discussion about the new iOS6 sms.db, I'll let you find the location yourself.  What's most important to know is this:

1. the backups are flat:  all the files are dumped into one directory and are not preserved in their original tree structure.
2. the files are renamed: the file names are sha1 values calculated from the domain, path, and filename of the original file.

The nature of hash digest is that they produce unique, fixed length values based on the data present in the data structure being analyzed.  This means that a 1 byte file and a 200gb data stream both produce a 40-byte sha1 hash value.  This also means that the data on which the hash value was calculated cannot be reverse engineered from the value alone.  Why did I just state all this?  Certainly not to ignite another hash controversy by anything I might just misstated, but instead to point out that we can't really know the original file name, path, and domain from the sha1 value file names found in the backup.  Sure, you'll find postings that tell you the sms.db is renamed "3d0d7e5fb2ce288813306e4d4636395e047a3d28", but you won't find a file name for every file in your backup.  And you won't know any metadata about any of the backup files from the hash value, either.

Now, Linux helps us here.  On the command line, the file command will tell us the file type of each of the backup files, and most Linux file managers will render thumbnails for known file types.  This means its quite easy to view media files and identify databases just by opening a file browser pointed at the backup directory.  Its good for low hanging fruit, but how do you differentiate between the databases, for example?  Keyword search on a table name, maybe?  Sure, that might narrow the field, but it's a less than perfect solution.

Identifying Files in the Backup

Keeping the discussion general, know one more thing about iTunes backups before we proceed with the sms.db discussion: there are database files included in the backup that identify the files by domain, path, and file name.  In fact, the file metadata such a MAC times, ownership, permissions, and file size are included in the databases.

Now, the databases have changed with each iOS evolution--at least since iOS3 when I started examining iTunes backups.  Well, more accurately, I should they changed with each evolution except iOS6.  iOS6 retains the format introduced in iOS5.  That was again fortunate, because it just so happens that I coded a tool, based on a python script posted on stackoverflow posted by user galloglass, to 'unback' the iTunes backup based on the information in the backup databases.  The iOS5-unback.py tool works just fine on the iOS6 backup.

The sms.db

Where the backup format did not change in iOS6, the sms.db changed markedly.  Compare, if you will, the database schema side by side with an iOS5 sms.db.

	iOS6	iOS5
Number of tables:	10	8
Table Names:	SqliteDatabaseProperties, message, sqlite_sequence, chat, attachment, handle, message_attachment_join, chat_handle_join, chat_message_join, sqlite_stat1	_SqliteDatabaseProperties, message, sqlite_sequence, msg_group, group_member, msg_pieces, madrid_attachment, madrid_chat
Number of triggers:	9	7
Trigger Names:	set_message_has_attachments, update_message_roomname_cache_insert, delete_attachment_files, clean_orphaned_attachments, clean_orphaned_handles, clear_message_has_attachments, clean_orphaned_messages, update_message_roomname_cache_delete, clean_orphaned_handles2	insert_unread_message, mark_message_unread, mark_message_read, delete_message, insert_newest_message, delete_newest_message, delete_pieces
Number of indexes:	16	18
Index Names:	sqlite_autoindex__SqliteDatabaseProperties_1, sqlite_autoindex_message_1, sqlite_autoindex_chat_1, sqlite_autoindex_attachment_1, sqlite_autoindex_handle_1, sqlite_autoindex_handle_2, sqlite_autoindex_message_attachment_join_1, sqlite_autoindex_chat_handle_join_1, sqlite_autoindex_chat_message_join_1, message_idx_is_read, message_idx_failed, message_idx_handle, chat_idx_identifier, chat_idx_room_name, message_idx_was_downgraded, chat_message_join_idx_message_id	sqlite_autoindex__SqliteDatabaseProperties_1, madrid_attachment_message_index, madrid_attachment_guid_index, madrid_attachment_filename_index, madrid_chat_style_index, madrid_chat_state_index, madrid_chat_account_id_index, madrid_chat_chat_identifier_index, madrid_chat_service_name_index, madrid_chat_guid_index, madrid_chat_room_name_index, madrid_chat_account_login_index, message_group_index, message_flags_index, pieces_message_index, madrid_guid_index, madrid_roomname_service_index, madrid_handle_service_index

For anyone familiar with the iOS5 sms.db database structure, the most apparent change in the iOS6 sms.db is the lack of "madrid" tables.  In iOS5, the iMessage app was introduced, and iMessage texting and SMS texting were unified in the sms.db.  This is still true in iOS6, but it is handled quite differently, and by "differently," I mean "better."

Time Stamps

When parsing the iOS5 database, it was necessary to convert two different date formats: Unix epoch time and Mac Absolute Time.  The dates of SMS messages sent through the wireless carrier were recorded in unix epoch time in the date field of the message table.  iMessages, on the other hand, were recorded in Mac Absolute Time in the same table and field as the SMS message date!  

What's the difference between unix epoch and Mac Absolute Time?  Exactly 978307200 seconds.  Unix epoch starts on 01/01/1970, while Mac Absolute time starts on 01/01/2001.  SQLite queries that integrated the SMS and iMessage texts had to account for these differences.  Some examiners with automated tools for parsing the sms.db were likely blissfully ignorant of this issue, but it exited none-the-less.  But it presented challenges to those of use extracting data from the database with the SQLite command line program or a SQLite GUI browser.

The iOS6 sms.db simplifies the date issue.  All text message dates, whether SMS or iMessage are recorded in Mac Absolute time.  The datetime function in SQLite can be used to convert that time by adding 978307200 seconds to the date data.  This converts the time to unix epoch which is one of the 'modifiers' the datetime function was coded to handle (Mac Absolute Time is not a valid datetime modifier):

sqlite> select datetime(370884516 + 978307200, 'unixepoch');
datetime(370884516 + 978307200, 'unixepoch')
2012-10-02 22:28:36

While on the topic of time stamps, two additional time stamps are possible in each record: date_read and date_delivered.  The date_read field defaults to 0 until the message is opened in the iMessage application.  The date_sent is populated with a date when the message is sent through the iMessage service but not SMS.  This meas it is possible to differentiate between the time an iMessage was initiated by the device user and when it was actually sent.

Addresses 

Another significant change in the database is the address field.  In the iOS5 version, the "address" was the phone number of the remote contact in the text conversation.  The address field has been replaced with the handle_id in iOS6.  The handle_id corresponds to the ROWID in the handle table, which contains the phone number of the the contact in the id field.

Flags

I'll quickly mention a couple of other changes.  In the previous versions of the sms.db, fields such as flags and read were used to mark the type (sent, received, etc) and status (read, unread, etc) of the message.  New fields exist for these attributes including is_from_me, is_empty, is_delayed, is_auto_reply, is_prepared, is_read, is_system_message, is_sent.  The values 0 and 1 are used as "no" and "yes" respectively in interpreting these fields.

I mentioned earlier that the service used to transmit the text, SMS or iMessage, resulted in different time stamp type.  It also resulted in different fields being populated in the message table.  In iOS6, the messages share the same flags regardless of the service used to send them.  The service utilized is recorded in the the new service field of the message table.

Putting it All Together

I did not come anywhere close to describing all the data in the new sms.db table, nor how to related all the tables.  I only intended to alert investigators that there are differences in the new database that must be considered.

That said, I'd be remiss if I did not provide a sample query to produce a basic chat list.  The following query produces a list with several important fields (RowID, Date, Phone Number,Service|Type|Date Read/Sent|Text):

SELECT m.rowid as RowID, DATETIME(date + 978307200, 'unixepoch', 'localtime') as Date, h.id as "Phone Number", m.service as Service, CASE is_from_me WHEN 0 THEN "Received" WHEN 1 THEN "Sent" ELSE "Unknown" END as Type, CASE WHEN date_read > 0 THEN DATETIME(date_read + 978307200, 'unixepoch') WHEN date_delivered > 0 THEN DATETIME(date_delivered + 978307200, 'unixepoch') ELSE NULL END as "Date Read/Sent", text as Text FROM message m, handle h WHERE h.rowid = m.handle_id ORDER BY m.rowid ASC;


I'll make that a little easier to read:

SELECT 
  m.rowid as RowID, 
  DATETIME(date + 978307200, 'unixepoch', 'localtime') as Date, 
  h.id as "Phone Number", m.service as Service, 
  CASE is_from_me 
    WHEN 0 THEN "Received" 
    WHEN 1 THEN "Sent" 
    ELSE "Unknown" 
  END as Type, 
  CASE 
    WHEN date_read > 0 then DATETIME(date_read + 978307200, 'unixepoch')
    WHEN date_delivered > 0 THEN DATETIME(date_delivered + 978307200, 'unixepoch') 
    ELSE NULL END as "Date Read/Sent", 
  text as Text 
FROM message m, handle h 
WHERE h.rowid = m.handle_id 
ORDER BY m.rowid ASC;

Your results, when imported to a spreadsheet, should look like this:

ROWID	Date	Phone Number	Service	Type	Date Read/Sent	Text
2484	2012-10-02 08:28:36	11231234567	iMessage	Sent	2012-10-02 08:28:39	Hey
2485	2012-10-02 08:45:17	11231234567	iMessage	Sent	2012-10-02 08:46:11	Call me when you get this
2486	2012-10-02 08:46:06	13217654321	SMS	Received	2012-10-02 08:47:21	Can I borrow some bucks?
2487	2012-10-02 08:47:10	1321765321	SMS	Sent		No! I don't have any doe.

Rubbing Sticks: Lighting The Kindle Fire

I recently obtained a Kindle Fire on which to experiment.  It was booted to the operating system and unlocked.  Naturally, I thought, "Enable USB Debugging and have a go at it with adb."  I looked at the settings and to my surprise: no USB debugging option!  A little research revealed that USB debugging is enabled by default on the Kindle Fire and it runs on a custom Android 2.3.  "Fantastic!" I thought, "This is going to be a piece of cake."

Dashed Hopes

Boy was I wrong.  It's not that the Kindle Fire is particularly hard to deal with, it's just very different from my normal Android experience.  Normally, for the Android Debug Bridge (adb) to connect to an Android device in Linux, the vendor ID needs to be defined in a udev rule.  A quick look at the vendor IDs listed on the Android Developer site reveals the definite lack of a definition for the Kindle.  So, how do we find one?

$ lsusb
Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 002 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 001 Device 002: ID 8087:0020 Intel Corp. Integrated Rate Matching Hub
Bus 002 Device 002: ID 8087:0020 Intel Corp. Integrated Rate Matching Hub
Bus 001 Device 004: ID 1949:0005 Lab126

The lsusb command displays information about USB buses on the system.  From the output, we see the vendor ID for the Kindle Fire is 0x1949.  Yes, that "Lab126" is the Kindle and yes, the ID is hexadecimal.  And in the event you were wondering, the second hex value after the colon, 0x0005, is the Prodoct ID.

False Hopes

OK, now we're in business, right?  I append the Kindle Fire to my android.rules file in /etc/udev/rules.d/ with the following lines:

#Amazon Kindle
SUBSYSTEM=="usb", ATTR{idVendor}=="1949", MODE="0666"

Now I start the adb server and read the device with:

$ adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached

Nothing!  How can that be?  I've added every new device in this way!  I should see the serial number of the device or at least a series of question marks (indicating I need to restart the server as root because I lack permission to read the device).

Discovering Fire

It took some poking around and some experimentation, but it turns out the only way I could get adb to see the Kindle fire was to add the Vendor ID to the adb_usb.ini file.  "An '.ini' file?  Isn't that for Windows?" you ask.  In a strict sense, an ini file is a text file used for application settings, and it is commonly found on Windows systems, but not exclusively so.

You may recall that when you installed the Android SDK, you launched the android application in the ./android-sdk-linux/tools/ directory.  When you selected and installed the "Android SDK Tools" and "Android SDK Platform Tools", a hidden .android directory was created in your user account, or in the root user account if you ran the tool as administrator (recommended).  In the directory is the adb_usb.ini file, which by default, or after an android update usb command, has the following content:

# cat /root/.android/adb_usb.ini
# ANDROID 3RD PARTY USB VENDOR ID LIST -- DO NOT EDIT.
# USE 'android update adb' TO GENERATE.
# 1 USB VENDOR ID PER LINE.

To connect to the Kindle Fire with adb, we need to append the Vendor ID, as a hexadecimal expression, to the adb_usb.ini file:

# echo 0x1949 >> $HOME/.android/adb_usb.ini

You can see the effect of your command with 'cat':

# cat adb_usb.ini
# ANDROID 3RD PARTY USB VENDOR ID LIST -- DO NOT EDIT.
# USE 'android update adb' TO GENERATE.
# 1 USB VENDOR ID PER LINE.
0x1949

Now, to connect the Fire, all that remains is to restart the adb server:

# adb kill-server
# adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached
0123456789ABCDEF device

Success!  

To recap, the udev rule does not help us in the case of the Kindle Fire.  The presence of the Vendor ID in the rule or lack there of has no effect on connecting to the device with the Android Debug Bridge in Linux.  The only effective method for connecting to the Fire is to place the Vendor ID in the adb_usb.ini file.

Facebook Search History

A digital forensics investigator is often asked to answer the question, "What did the user search for?"  Usually, the question revolves around Internet search engines, and producing a list depends on the browser in play as much as the search engine.  With the 250+ search engines and the 130+ Internet browsers referenced in Wikipedia, where you will find these histories and in what format can vary wildly.  Add to that the various tool bars that maintain data independent of the browser, and you're probably ready to surrender jump to another post on a topic less daunting!

But, you probably figured out from the title of this post that I'm not going to discuss the over 33,800 browser/search engine possibilities you need to consider when producing a "search term" history.  Instead, I'm going to discuss something I just discovered, Facebook search history.  And like so many web browsing sessions, I begin the discussion with a Google search....

Google Instant Predictions

I'll wager that most Internet users that have conducted an Internet search in the last two years have done so at least once using Google.  If so, they have likely experienced Google Instant Predictions. Launched around September, 2010, Instant Predictions produces "instant" search results as you type.  Search results begin populating based on Google's prediction of your search terms as you type them.

From a forensics point of view, this produces a lot more Internet history.  Every time Google populates results, its sending a webpage and elements.  And unless the connection is slow or the typist is particularly fast, Google refreshes the search results page for every letter typed by the user!  Consider a Google search using the Safari web browser for the term: "car wash" (obtained from the Safari Cache.db).

http://clients1.google.com/complete/search?client=safari&q=c
http://clients1.google.com/complete/search?client=safari&q=ca
http://clients1.google.com/complete/search?client=safari&q=car
http://clients1.google.com/complete/search?client=safari&q=car+
http://clients1.google.com/complete/search?client=safari&q=car+w
http://clients1.google.com/complete/search?client=safari&q=car+wa
http://clients1.google.com/complete/search?client=safari&q=car+was
http://clients1.google.com/complete/search?client=safari&q=car+wash

It is easy to see that an html page was cached for each of these URLs!  What can be interesting, and entertaining, is that you can even follow typing errors such as typos, spelling errors, and the resulting backspaces!

Facebook Type Ahead Search

I've recently discovered that Facebook produces a similar URL history for searches conducted through its website.  Observe the following:

http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=sl&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.sl&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.sleuth&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions

The response speed of Facebook does not appear to be that of Google, but again, it is easy to see that facebook sent several responses that were cached by Safari related to a single search by way of the "typeahead" mechanism.

Now, Facebook searches might be recorded in browser history databases, but consider that these databases may be destroyed through anti-forensics techniques or otherwise.  Searching for URLs might be the only method at your disposal for producing search term histories.  And now, like me, you know what to seek to reconstruct Facebook searches!

Mate: Your Forensics Desktop Buddy

There has been a casualty in the Linux Desktop over the past year or two: forensics.  Now, I'm not saying forensics can't be conducted from a modern Linux desktop environment like Gnome3 or Unity, but those environments do make it more difficult.  First, they are less configurable and second, they make it difficult to display more than one window at a time.  What might be a simple, clean interface to a desktop user is a hindrance to a forensics practitioner.

As a result, I switched to XFCE.  It's always been attractive to me because of its simplicity and size, and it does a lot of things right.  But it's a little rough around the edges.  The various settings windows are not well integrated, and a new user can easily become lost and frustrated.  More importantly, Thunar, the default file manager, is a little weak for exploring file systems from digital discovery point-of-view.  While

Meet Mate

Fortunately, forensics gurus are not the only people dissatisfied with the direction of the Linux desktop.  Gnome2 was a staple for many users, and it was common on Linux forensics boot disks, too.  The Mate project arose from the ashes of Gnome2 and serves as a drop in replacement for the popular but deprecated classic.

If you are a longtime or former Ubuntu user, Mate will look very familiar.  There is a little bit of translating to do, however, as some of the application names to which you've grown accustomed:

	MATE	GNOME 2	Type	Notes
	Atril	Evince	Document viewer	“lectern, reading desk”
	Caja	Nautilus	File manager	“box”
	Engrampa	File-Roller	File archive manager	“clip together”
	Eye of MATE (EOM)	Eye of GNOME (EOG)	Image viewer
	Marco	Metacity	Window manager	“framework, frame”
	MateCalc	gcalctool	Calculator
	MateConf	GConf	DE configuration system
	MateDialog	Zenity	GTK+ command-line dialog boxes
	MDM	GDM	Display manager (graphical login)
	Mozo	Alacarte	Menu editor
	Pluma	Gedit	Text editor	“pen”

The application gconf-editor was default GUI settings editor for Gnome2.  It was an important tool to know, because with it, the forensicator could configure his/her system to not automount devices.  The table above and a wiki entry at the Mate website indicate that gconf-editor will be replaced with, predictably, mateconf-editor, but as of Mate v1.40, no such application exists.

Configuring Mate

This brings me to the purpose of this post.  There is a command line tool that can be used to configure the Mate desktop environment called mateconftool-2, but its use is not intuitive.  I'll demonstrate its use to alter the media auto-mount setting.

First, its important to understand that the Mate settings expressed as key-value pairs and are stored in a series of XML files.  Editing the XML files directly is not recommended (settings are read and applied by the mateconfd daemon), and if you try, you'll see its quite difficult to chase down the correct file.  The mateconftool-2 tool makes navigating and editing the settings straight forward.

If you choose to think of the settings as being stored in a file system-like hierarchy, and I think you'll have little problem.  Observe, to view the settings directories at the root of the settings tree, issue the command:

$ mateconftool-2 --all-dirs /
 /system
 /desktop
 /schemas
 /apps

We see that there are settings in four categories: system, desktop, schemas, and apps.  We are seeking to change the behavior of the caja application, the file manager responsible for mounting attached devices.  We can see that the caja settings are not the only Mate desktop environment settings that can be configured:

$ mateconftool-2 --all-dirs /apps
 /apps/mate-dictionary
 /apps/procman
 /apps/mate-system-log
 /apps/timer-applet
 /apps/control-center
 /apps/pluma
 /apps/marco
 /apps/panel
 /apps/baobab
 /apps/engrampa
 /apps/caja
 /apps/mate-terminal
 /apps/mate-screenshot
 /apps/mate-screensaver
 /apps/notification-daemon
 /apps/eom
 /apps/mate-session
 /apps/mate-power-manager
 /apps/mate_settings_daemon
 /apps/mate-search-tool
 /apps/stickynotes_applet
 /apps/mate-volume-control

To review the caja settings, we examine the categories with the all-directories option and learn there is a preferences directory.  To view the preferences key-value pairs, we switch to the all-entries option:

$ mateconftool-2 --all-entries /apps/caja/preferences
 ...
 media_automount = true
 search_bar_type = search_by_text
 media_autorun_never = false
 media_autorun_x_content_start_app = [x-content/software]
 mouse_back_button = 8
 show_image_thumbnails = local_only
 desktop_is_home_dir = false
 media_autorun_x_content_ignore = []
 start_with_sidebar = true
 thumbnail_limit = 10485760
 directory_limit = -1

Now, this might seem cumbersome.  That's because it is.  If you know the path to the setting in which you are interested, you can address it directly:

$ mateconftool-2 --get /apps/caja/preferences/media_automount

true

To change a setting, you must specify its type: integer, boolean, float, or a string.  In our case, its pretty evident that we are dealing with a boolean value, and we want to set it to "false".  We must specify the action (set) and the type (false).  Silence means success, but we follow our change by reading the key for verification:

$ mateconftool-2 --set --type=bool /apps/caja/preferences/media_automount false

$ mateconftool-2 --get /apps/caja/preferences/media_automount

false

Now, when a device is plugged into the a Linux system running the Mate desktop, it will not be auto-mounted.  This is particularly important in Linux forensic boot discs designed to be inserted in the device to be examined/imaged or where a write-blocker is not an option or available.  None-the-less, we'll all  be happier when mateconf-editor GUI is added to the Mate line-up!

Safari History without History.plist

I came back from 7 days of training to a nightmare: impatient criminal investigators booted an Apple Macbook to "see" what the owner had been doing on the Internet in the days leading up to a crime.  The laptop had been in evidence for over a year but never examined (no request for examination was made), and trial was looming.  A change in the defendants plea less than two weeks before trial necessitated an examination, but no qualified examiners were immediately available.  So, someone thought it was a good idea just to boot the Macbook and take a look around .

Safari, decided that the History.plist was tool old and created a new one with the websites that the investigators tried to visit--apparently from bookmarks.  So, I was faced with trying to determine a browsing history absent the relevant History.plist.

Assuming the old History.plist was not overwritten, carving 500gb for a plist was not a good option in the amount of time I had to do the work.  I was aware from past examinations that Safari keeps web page elements in a sqlite database called Cache.db.  It is found in "/Users/<username>/Library/Caches/com.apple.Safari".  You may be familiar with that location, because it is the home of a directory called "Webpage Previews" in which are stored jpeg images of visited webpages.

The Cache.db contains four tables: cfurl_cache_schema_version, cfurl_cache_response, sqlite_sequence, cfurl_cache_blob_data.  The relevant tables to this discussion are cfurl_cache_response and cfurl_cache_blob_data, which I will refer to as Response and Blob for ease of discussion.  I will not try to describe every element of these tables, just those I chose to implement my solution.

The Response Table

The Response table has the following schema:

CREATE TABLE cfurl_cache_response(
  entry_ID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
  version INTEGER, hash_value INTEGER,
  storage_policy INTEGER,
  request_key TEXT UNIQUE,
  time_stamp NOT NULL DEFAULT CURRENT_TIMESTAMP)

The request_key field contains the URL of the webpage element (html, images, flash, javascript, css, etc.).  The time_stamp field is in plain language (yyyy-mm-dd hh:mm:ss) and does not require conversion from an epoch time.  The entry_ID is a unique integer that relates directly to the data in the Blob table with a corresponding entry_ID.

The BLOB Table

The Blob table has the following schema:

CREATE TABLE cfurl_cache_blob_data(
  entry_ID INTEGER PRIMARY KEY,
  response_object BLOB, request_object BLOB,
  receiver_data BLOB,
  proto_props BLOB,
  user_info BLOB)

The receiver_data field contains the object downloaded from the URL in the Response table request_key. The response_object and receiver_data fields contain XML that provide metadata about the receiver_data content.  And, again, the entry_ID is a unique integer for this table that corresponds directly an entry_ID in the Response Table.

Making History

The Response table and the Blob table have a 1 to 1 correspondence, i.e., there are equal numbers of records in each table.  Response entry_ID 1 corresponds to Blob entry_ID 1, and so on.  However, not all the records a relevant to a history of visited webpages.  Most are elements of webpages, not the HTML that represents the webpage.  

The URLs in the Response table request_key field do not help us much here: many webpages do not end with ".html".  A quick look at Google search results will bear this out.  Therefore, we can't filter the URLs in any meaningful way.  We could try to exclude certain objects, like .jpg, .png, .js, .css, .swf, etc., but this is difficult and not reliable.

I settled on filtering the Blob table receiver_data content for the HTML tag that represents a webpage (or at least HTML content).  The query uses a "natural join" syntax that marries the tables on the entry_ID field and then only returns the records that have the leading "" tag:

sqlite3 -header Cache.db "select r.entry_ID, time_stamp, request_key from cfurl_cache_blob_data c , cfurl_cache_response r where c.entry_id=r.entry_id and receiver_data like '<html>%'" 

The query above reduced a database containing 6700 elements to 187 webpages.  I expanded this to any item with an HTML tag, in the event of malformed pages, by placing a leading wildcard before the tag:

# sqlite3 -header Cache.db "select r.entry_ID, time_stamp, request_key from cfurl_cache_blob_data c , cfurl_cache_response r where c.entry_id=r.entry_id and receiver_data like '%<html>%'"

By adding the leading wildcard, my history increased to 261 records.  I can redirect the output to a file for analysis, and I can modify the query to actually export the data in the  receiver_data field to view the actual content of any pages of interest.

EDIT:
Better still, because file headers can very in HTML documents (think "doctype" strings), is to search for the HTML footer tag "/html" (brackets excluded intentionally):

sqlite3 -header Cache.db "select r.entry_ID, time_stamp, request_key from cfurl_cache_blob_data c , cfurl_cache_response r where c.entry_id=r.entry_id and receiver_data like '%/html%'"

By allowing for a variable header string, my HTML history increased to 328 records.  I have since written a utility to export the files in the Cache.db and have checked the files by mime-type: there were 349 html files detected.  I hope to reconcile this in the near future.

Making Sense

I can't take the time to break down the queries right now, but I would like to highlight one expression that might not be familiar to casual sqlite users:

sqlite3 -header Cache.db "select r.entry_ID, time_stamp, request_key from cfurl_cache_blob_data c , cfurl_cache_response r where c.entry_id=r.entry_id and receiver_data like '<html>%'"

In the highlighted portion above, I list the two tables that are the subject of the query.  The trailing "c" and "r" behind the full table names are aliases for the full names.  The alias save a lot of typing, and you see them employed in the select clause, which tells sqlite which entry_ID I desire (since it exists in both tables) and in the where clause.

If anyone has another idea on how to accomplish this task or is aware of any shortcomings to this approach, please comment.  After all, I'm trying to make lemonade from from the lemons I've been handed...

File "Cliffs" Notes: Abbreviating File Reads

Sometimes a forensics task is pretty narrow.  Determining file type means reading the first few 10's of bytes in most cases.  Luckily there are tools that do just that.  But for other tasks we might face, our standard tools don't really give us a method for narrowing their scope.  You might want to search files for a string that you expect to find in the first 1000 bytes of the file.  Grep will allow you to stop searching a file once the string is found, but it won't stop searching until it finds the match.  This might not seem like a big deal until you consider large files like videos.  You really want to search 1k of data, but you end up searching 2 GB!

Head

The head command is used to output the first part of a file.  By default, it outputs the first 10 lines of a files (best when applied to text files), but it can be used to output a user-defined amount of bytes.

$ head -c 1000 file # Export the first 1000 bytes of "file".  "1k" can be used as a shortcut for "1000".  Similarly, the tail command can be used to export data from the end of a file.  

You can stop reading here if you are only needing to ouput a fixed amount of bytes from one file.  But, usually the forensic examiner is concerned with searching through hundreds of thousands of files, if not millions!

A Real-World Example

Let's say we have a disk image containing hundreds of thousand graphics, but we only want to view photographs created with a digital camera.  Graphics viewing programs like Gthumb have the ability to recurse through a file system and find graphics images, and even provide for some search limits like file size, modified date, etc.  But these filters don't really help us here.

We need a way to limit the graphics we view to digital photographs.  The best way for us to do this is to limit our search for images based on the presence of Exif data.  The term Exif stands for Exchangeable Image File Format.  Exif data contains camera and setting information and was developed to encourage interoperability between digital devices (see exif.org).  It is found before the image data in a jpeg file.  Most digital cameras use this standard.

Exif data is preceed by the header 'Exif' found 25 bytes into a jpeg file.  So, it seems logical to search graphics files for the Exif header.  But how do we search for graphics files, and then search only the first 28 bytes for the term 'Exif'?

Find and File

The find command can be used to search recursively through a file system.  It's a very robust tool, but I'll only focus on one of the the simplest invocations here: searching a path recursively for any file:

$ find path/to/search/ -type f  # The "f" argument to the "-type" option returns only files to standard output.  The "d" argument would limit output to directories, etc.

We can further limit our output based on file name, if we wish:

$ find path/to/search -type f -iname ".jp*g" # The -iname option matches the following argument with case insensitivity.  Note that standard wildcards are accepted.  The -name option would produce case sensitive results.

We might be looking for graphics that have been renamed, however, and limiting our search by name is too narrow.  This is where the file command comes in.  File reads a files header and returns its type.  We can pipe files located with find and read their type thusly:

$ find path/to/search -type f | file -i -  # The -i option returns the files MIME type, which is more standardized than the "plain language" descriptions otherwise provided.  The file command requires a file argument, and the '-' after the -i option is a shortcut to stdin.  In otherwords, the - is replaced by the file path returned by the find command.

We now have a list of files with their types being returned to the standard output.  What we want to do is filter those files for images, and then get a subset of images with exif data.  To do so,  insert a while loop and add a test:

$ find path/to/search -type f | while read i; do file -ib "$i" | grep "image"; [ $? = 0 ] && echo "$i"; done # Here, each file located with find is assigned to variable $i in the while loop.  Inside the loop, the file type of $i is determined and if the type contains the string "image", the file path is printed.  The "-b" option was added to the file command to produce "brief" output, that is output that includes the file type absent the file name (this avoids false hits where the term "image" is in the file path).

If you're not familiar with bash tests, please review this post, or on the BASH command line, enter "help test".  In brief, we are testing the grep exit code, and if it is '0', which means success, we execute the echo command to print the file path.

We've now managed a list of files that are classified as images.  We need to determine which have Exif data to complete the our quest.  We do this by integrating one more test:

$ find path/to/search -type f | while read i; do file -ib "$i" | grep "image"; [ $? = 0 ] && head -c 28 "$i" | grep "Exif"; [ $? = 0 ] && echo "$i"; done  # Here, if the file is determined to be an image, the first 28 bytes are grepped for the string "Exif".  If that results in a match, the filename is printed.

Okay, you caught me: I only produced a list of files with Exif headers and I can't see the content of the images by reading a list.  This is easily rectified with the ln or 'link' command.  We can replace the last echo with a command to create link files to the images we located allowing for all the files to be viewed from one location:

$ find path/to/search -type f | while read i; do file -ib "$i" | grep "image"; [ $? = 0 ] && head -c 28 "$i" | grep "Exif"; [ $? = 0 ] && ln -s "$i" "${i##*/}"-$(stat -c %i "$i"); done # The -s option creates a symbolic link to file $i in the current directory.  The link will be named for the file's basename and have the inode number appended to prevent errors the occur if the files have the same name.  

Don't worry about the "${i##*/}"-$(stat -c %i "$i") razzle-dazzle for now.  I'll explain that in another post.  For now, just mimic that part of the command if you are concerned about files having the same name.

A Little More Detail for Those Still Conscious

It turns out that Exif is really not the best string on which to filter.  More effective was filtering on an exif creation date.  Consider a recent case where I carved jpegs from unallocated clusters.  Here, I could filter on file name over file type because the carver automatically appended the .jpg file extension.

Total number of files carved:

$ find recovered/ -type f | wc -l # The wc or "word count" command can be used to count lines of output
231331

My total number of jpeg images:

$ find recovered/ -name "*.jpg" | wc
61432

Total number of jpeg images with Exif data:

$ find recovered/ -name "*.jpg" | while read i; do head -c 28 $i | grep Exif ; done | wc -l
3815

Total number of jpeg imags with Exif creation dates:

$ find recovered/ -name "*.jpg" | while read i; do head -c 256 $i | grep -E '[0-9]{4}:[0-9]{2}:[0-9]{2}\ [0-9]{2}:[0-9]{2}:[0-9]{2}'; done | wc -l # The regular expression matches output like "2012:08:09 17:11:56"
841

As you can see, filtering on a date code was much more effective at finding files with meta data associated with cameras (file creation dates) the on the Exif header alone.

I know that there is a lot of information here, but I hope by presenting it completely you are able to understand and adapt the concepts to your own uses.