Tuesday, February 18, 2014

Using SQLite to View Google Chrome History

I recently needed to view a user’s browser history from Google Chrome, without affecting that user’s actual history – in order words, without opening Google. In order to accomplish that, I:

  1. Logged onto the desktop as an administrator
  2. Installed SQLite3
  3. Copied the Chrome history file from the user’s profile into the SQLite3 folder
  4. Open the history file in SQLite
  5. Executed a simple command to pipe a date/time stamp and the URL to a text file

It took some poking around the Internet to find how to do this – I’ll assemble the steps here, for “one-stop shopping” for people who need to do this.

Oh – this post assumes you’re logged on as an administrator.

Install SQLite3

This is pretty simple – go to the SQLite download page and get the version you need. http://sqlite.org/download.html I recommend the SQLite Shell precompiled binary for Windows.

I like to keep all my tools in one place, so I extracted SQLite3 into the \tools\SQLite\ folder. (BTW: I recommend keeping your tools folder somewhere where there are no spaces in the path—older apps can’t handle spaces).

Copy Chrome History

The user’s Chrome history file is NOT where the web says it is… Just sayin’. It’s here: C:\Users\<user>\AppData\Local\Google\Chrome\User Data\Default\

The file is simply titled “history”. Copy that file and paste it into the SQLite folder you created when you downloaded that tool.

Open History in SQLite

Open a command prompt and navigate to the SQLite folder you created. Then type this and hit enter: sqlite3.exe history

Execute Query

Almost there – really, it’s that easy…

sqlite> .mode column
sqlite> .width 25 255
sqlite> .output urls.txt
sqlite> SELECT datetime(((visits.visit_time/1000000)-11644473600), "unixepoch"),
urls.url FROM urls, visits where urls.id = visits.url;
sqlite> .output stdout
sqlite> .quit

Yes, the period is needed before any command.

And that’s all there is to it – you’ll have a fixed-width file with the visit date in the first column and the URL in the second column.

Resources

http://sqlite.awardspace.info/syntax/localindex.htm

http://www.forensicswiki.org/wiki/Google_Chrome

No comments:

Post a Comment