Sunday, November 1, 2015

FireFox 41.0.1 Browsing session analysis - places.sqlite


Similar to "formhistory.sqlite" post, let's open the "places.sqlite" file and list the tables.

According to "support.mozilla.org", the "places.sqlite" file is used to store your bookmarks and list of files you have downloaded along with websites previously visited.

When I tried to open "places.sqlite" directly from the "tmp/" mountpoint, no tables or anything was shown. As a result I copied it to another location on the system. Once I copied the  the file I was now able to gain access.


To get a better understanding of the relationship among these tables, please visit "http://people.mozilla.org/~dietrich/places-erd.png"

Let's ensure we turn the headers on with ".header on". Let's take a look at some of these tables.
Using information we learned in the "formhistory.sqlite" post, let's build our SQL statement to query the "places.sqlite" file

First I ran "SELECT * FROM moz_annos;" This returned more data than I wanted to see so I got more targeted with the query.

According to developer.mozilla.org "moz_annos: Contains the values of page annotations. It maps a page (reference to moz_places) and an annotation name (reference to moz_anno_attributes) to the value of the annotation."

We first set the mode to column by using ".mode column". Below we see the output.

While this view shows data, it would be better if we can see what is actually the contents of the "content" column. Let's change the way sqlite presents this data by changing the mode to csv. To change the mode we use ".mode csv"
Now we see below that this view is much clearer. Of interest to me from this table are the references to "dnsSpoof.py" and "map of canada.png" It seems these two files were both stored to the "C:/tmp/" folder on 2015-10-03 at 21:54:26 and 22:00:34 respectively.



Looking at the "moz_hosts" table shows the sites I visited. We see the column labeled "typed". This represents the entries which I typed specifically and which I was not otherwise referred to.



Taking a quick look at the "moz_places" table while also changing the mode to csv ".mode csv" so that we have a clearer picture of what's going on. Let's try to extract the unique URLs which were visited.
sqlite> SELECT DISTINCT url,dateTime(last_visit_date/1000000, 'unixepoch', 'localtime'),typed as last_visited FROM moz_places WHERE url LIKE '%http%' ORDER BY typed DESC;


Above we see a snapshot of this output
Obviously, since the data is available there are a number of things which you can do with it. I just chose things I think are relevant for what I'm doing at this point.



References:
https://support.mozilla.org/en-US/kb/recovering-important-data-from-an-old-profile
https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Database
https://www.sqlite.org/cli.html
http://www.tutorialspoint.com/sqlite/sqlite_date_time.htm
http://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm

No comments:

Post a Comment