Chrome Forensics

Some times we need to get information from Google Chrome like History, Bookmarks and Downloads.

Using Google Chrome we can have this information typing "about:history", "about:bookmarks" and "about:downloads" in browser. For a list of Chrome URLs just type "about:about".

Another way is to look into Google Chrome database. Chrome stores the browser information in a SQLite database named "History". Take a look at:

    LINUX - /home/<USERNAME>/.config/<google-chrome or chromium>
    WINDOWS 7 - C:\Users\<USERNAME>\AppData\Local\Google\Chrome\Default\

To open the database file, we can use any SQLite Browser, like SQLiteStudio for Windows or sqlite3 for Linux. The database has the following tables: downloads, presentation, urls, keyword_search_terms, segment_usage, visits, meta and segments.

The more interesting table is the urls table. It has the columns: id, url, title, visit_count, typed_count, last_visit_time, hidden and favicon_id.

Other tables that we can be interested in are the download and keyword_search_terms tables.

The download table has the columns: id, full_path, url, start_time, received_bytes, total_bytes, state, end_time and opened.

And the keyword_search_terms has the following columns: keyword_id, url_id, lower_term, term.

The timestamps stored in the database are in Webkit Format and its base time is 01/01/1601 00:00:00. For more information you can go here.

So, to obtain the history from Google Chrome browser we can create a select query like this (Atila, thanks for the improvement!):

    SELECT
    urls.url AS URL,
    urls.title AS "Page Title",
    urls.visit_count AS "Number of Visits",
    urls.typed_count AS "Typed Count",
    datetime(( urls.last_visit_time /1000000)-11644473600, 'unixepoch') AS "Last Visit (UTC)",
    urls.hidden AS Hidden,
    datetime(( visits.visit_time /1000000)-11644473600, 'unixepoch') AS "First Visit (UTC)",
    visits.from_visit AS "From Visit",
    visitsold.url AS "From Visit URL",
    CASE (visits.transition & 255)
    WHEN 0 THEN 'User clicked a link'
    WHEN 1 THEN 'User typed the URL in the URL bar'
    WHEN 2 THEN 'Got through a suggestion in the UI'
    WHEN 3 THEN 'Content automatically loaded in a non-toplevel frame - user may not realize'
    WHEN 4 THEN 'Subframe explicitly requested by the user'
    WHEN 5 THEN 'User typed in the URL bar and selected an entry from the list - such as a search bar'
    WHEN 6 THEN 'The start page of the browser'
    WHEN 7 THEN 'A form the user has submitted values to'
    WHEN 8 THEN 'The user reloaded the page, eg by hitting the reload button or restored a session'
    WHEN 9 THEN 'URL what was generated from a replacable keyword other than the default search     provider'
    WHEN 10 THEN 'Corresponds to a visit generated from a KEYWORD'
    END AS Comment
    FROM urls, visits
    LEFT OUTER JOIN (SELECT visits.id, urls.url FROM visits, urls WHERE urls.id=visits.url) AS     visitsold
    ON visits.from_visit = visitsold.id
    WHERE urls.id = visits.url
    ORDER BY 5

The transition column describes how the URL was loaded in the browser. We need to apply a 0xFF mask to the value found inside the database to correspond to one of the 10 types of transitions.

And to obtain the Downloads from Chrome we can:

    SELECT
    url AS URL,
    full_path AS "Full Path",
    received_bytes AS "Receveid Bytes",
    total_bytes AS "Total Bytes",
    datetime(( start_time /1000000)-11644473600, 'unixepoch') AS "Start Time (UTC)",
    datetime(( end_time /1000000)-11644473600, 'unixepoch') AS "End Time (UTC)"
    FROM downloads

The same procedure we can use to the keyword_search_terms table.

The Bookmarks are stored in the file Bookmarks in the same directory we found the History database.

The Bookmark file is plain text and you will find a structure like this:

    {
       "checksum": "7f7d7ff1d022fca5cad656c796845993",
       "roots": {
          "bookmark_bar": {
             "children": [ {
                "children": [ {
                   "date_added": "12975699777642745",
                   "id": "5",
                   "name": "Forensics & Incident Response",
                   "type": "url",
                   "url": "http://forensir.blogspot.com/"
                }, {
                   "date_added": "12975699777777745",
                   "id": "6",
                   "name": "Windows Incident Response",
                   "type": "url",
                   "url": "http://windowsir.blogspot.com/"
                } ],

             ...

          }
       },
       "version": 1
    }

The timestamps of the Bookmark file are also in Webkit Format and to translate to a readable format (UTC) we can use the SQLite query:

    SELECT datetime(( "TIME" /1000000)-11644473600, 'unixepoch')

where TIME is the integer number in the Bookmark file timestamps.

Good Luck with your queries!!!