Chrome SQLite database analysis

By chrome, Discovery, Forensics, python, SQLite, sqlite troubleshooting, SQLiteDiver

Today I’m posting some research I did early last year related to querying Chrome Web Browser SQLite databases which is how Chrome stores most of the useful information that makes for a great browsing experience. A byproduct of course is useful information for an examiner. In this post I’m going to talk about two databases Chrome uses the Cookies and the Favicons database.

If you want to run the queries I provided try using the Firefox SQLiteManager Add-On or SQLiteman otherwise you can just view a database in SQLiteDiver very easily.

The “Cookies” database

Cookies is pretty self explanitory in this case. It stores the first and last time a cookie for a website was accessed. This is good for knowing where someone went, how often and the last time they may have visited the site. I’m only going to use a few columns for this example.

Tables: cookies

Basic Query:

select distinct creation_utc, host_key, last_access_utc, from cookies;

Output (using SQLiteDiver)

chrome-cookies_sqlite

The “Favicons” database

I find the favicons db very interesting. There are three tables of interest, the favicons_bitmaps, favions and icon_mapping. The icon_mapping table keeps the page_url (this makes finding which ones are still valid).

Tables: favicon_bitmaps, favicons and icon_mapping

Basic Query

       select  favicons.url,
                icon_mapping.page_url,
                favicon_bitmaps.last_updated
        from favicons
                inner join icon_mapping on favicons.id = icon_mapping.icon_id
                inner join favicon_bitmaps on icon_mapping.icon_id = favicon_bitmaps.icon_id

Advanced Query

        select  favicons.url,
                icon_mapping.page_url,
                datetime(((favicon_bitmaps.last_updated -11644473600000000)/1000000),
                        ‘unixepoch’,’localtime’) [last_updated]
        from favicons
                inner join icon_mapping
                  on favicons.id = icon_mapping.icon_id
                inner join favicon_bitmaps
                  on icon_mapping.icon_id = favicon_bitmaps.icon_id

This is a query from one of my Python scripts. It does some additional stuff like convert the last_updated date to a readable format.

Output

Here’s the output you should expect. As you can see the date has converted nicely.

‘http://static01.nyt.com/favicon.ico’ ‘http://www.nytimes.com/2014/01/31/technology/amazons-shares-fall-as-revenue-disappoints.html?nl=todaysheadlines&emc=edit_th_20140131’ 2014-01-31 08:31:39
‘https://ssl.gstatic.com/s2/oz/images/faviconr3.ico’ ‘http://ow.ly/t9y7h       ‘ 2014-03-12 21:40:37

 Wrap-Up

I’m sure you can see the value in reviewing these SQLite databases. If you would like to it done in python take a look at the python script i demonstrated at CEIC last year.  Here’s where you can the script sqlite3_reader-favicons.py.

I meant to get this post done about a year ago but time got away from me!

Enjoy!