Parsing the stock Android Browser for Searched Items

 in Physical Analyzer

While digging through some Android extractions in Physical Analyzer, I noticed that, although the Google Search items were found by PA, the searches made from within the browser were not found. Now, I'm only talking about the stock Android browser here, not Chrome, Firefox, or any other browser.

The stock Android browser databases I'm interested in are found in the following path in the user data partition:

/data/data/com.android.browser/databases/browser.db or
/data/data/com.android.browser/databases/browser2.db

This is, of course, a SQLite database that typically also contains the web history for the browser in another table.

The table I care about here is the "searches" table. Here's a look at it using Physical Analyzer's database view tool. It should be noted that you COULD just export this as a .csv file and manually convert the Unix dates yourself, but... what I want to do is add these searches to the Searched Items under the Project Tree with their own category "Android Browser."

View of browser.db from within Physical Analyzer

So, you can see that this is a very simple table, with the search terms under the "search" column and the date (in Unix epoch time with milliseconds) under the "date" column.

What I was doing with these before was just exporting the entire table out to a .csv as I mentioned and using Evigator's Clocksmith to manually convert the dates and then pasting them back into the table in a new column. Something like this:

Using Excel to add the converted timestamps to the browser searches

By the way, Clocksmith is an excellent add-on tool which just happens to be free.

While the export to .csv trick works just fine, it's far from quick. Each manual conversion takes 15-20 seconds, so if you have dozens of search terms, it may take a while. I have seen some people do it with an Excel formula that converts the Unix date to a human-readable format, but that's too hard for me.

Anyway, I'll get to the point, which is a script that will do this for us and put the results right back into Physical Analyzer where we can use them just like anything else found there in the Project Tree.

SQLiteParser

Physical Analyzer includes a SQLite parsing module called, appropriately, SQLiteParser. It is quite easy to use (yes, even easier than importing sqlite3). All you need are the full path of the database, the table name of interest, and the column names from within the table. In this case, the database full path is /data/data/com.android.browser/databases/browser.db, the table name of interest is searches, and the column names are search and date.


We need to do the following:

- Find the file
- Set the file as our "database" for the parser, this tells PA to treat the file as a SQLite db
- set the table name that we want to read
- have PA pull the columns of interest from that table and assign them to values in a "searched item"
- add those searched items to the DataStore, which makes them show up in the Project Tree

So, here is one way to do it:

#script for finding the /data/data/com.android.browser/browser.db or browser2.db and pulling the searches
#table from that SQLite database, adding them to the Physical Analyzer datastore and displaying them
#in the Analyzed Data portion of the Project Tree under Searched Items - Android Browser
#Written for Physical Analyzer
#This script is not published by Cellebrite and Cellebrite makes no guarantees, either implied
#or express as to its suitability or performance or indeed its utility for any particular
#purpose.
#Greg Thomson
#H-11 Digital Forensics

from physical import *
import SQLiteParser
import clr
clr.AddReference ('System.Windows.Forms')
from System.Windows.Forms import MessageBox

#We need to iterate through the file systems and find the android browser database
#(this will vary depending on the make and model of Android phone and whether is a 
#physical or file system extraction. Some have multiple partitions and/or file
#systems, some just one.

#find the file and set the db to that file
count_db = 0
for fs in ds.FileSystems:
    for f in fs.Search('com\.android\.browser/databases/browser(2)*\.db$'):
        if f.AbsolutePath.endswith('browser.db') or f.AbsolutePath.endswith('browser2.db'):
            count_db += 1
            db = SQLiteParser.Database.FromNode(f)

#show a message if it didn't find anything
if count_db == 0:
    MessageBox.Show('Sorry, the Android Browser search history wasn\'t found.\
    \r\n\r\nEither this phone does\'t use the stock Android Browser or it\'s not an Android phone.',
    'Database not found.')
    quit()


#setting up a new Searched Item and assigning it values from the database    

tableName = "searches"                               #setting the table to the searches table

count_items = 0

for record in db.ReadTableRecords(tableName):         #read the records one by one in the
                                                      #'searches table
    srch = SearchedItem ()                            #setting a variable 'srch' to hold
                                                      #each new Searched Item
    srch.Value.Value = record ['search'].Value        #setting the "Value" value to our
                                                        #new Searched Item
    #here we convert the Unix timestamp to a PA timestamp (without milliseconds)
    srch.TimeStamp.Value = TimeStamp.FromUnixTime(int(record['date'].Value)/1000)
    srch.Source.Value = "Android Browser"              #setting the "Source" value to our
                                                       #new Searched Item
    ds.Models.Add(srch)                                #adding the new Searched Item to the
                                                       #Datastore
    count_items += 1
    
content = "Successfully added " + str(count_items) + " Android Browser search history items to the Project Tree."
title = "Success"
MessageBox.Show(content, title)

BTW, if anyone knows a good way to format source code on blogger, let me know. I'm new at this.

After running this on an Android phone that does have searches in the Android browser, you should see this, as well as a popup box telling you how many items were added:

Results of  android_browser_search.py script run on an Android phone with searches from the stock browser

If you run it on a non-Android phone or an Android phone that doesn't contain that file, you will see this:

Results of running the script on a phone that doesn't have the database

After you have one script written that parses SQLite records, then it's going to be only a few minutes to modify it for other apps that you may run into. Note: always double check to make sure you have the tables and columns names correct and that you're pulling the appropriate data to the appropriate places.

In any case, you should always know where the data came from, what its significance is, how it's formatted, etc. Even when your forensic tool does it all for you, you should always be able to go to the raw data and verify it for yourself.

Good luck. Remember.