The ability to parse a user’s browser history is useful in lots of situations. I was recently in one of those situations, and I wanted to use PowerShell to pull the history for the Firefox. Searching the Internet didn’t yield too many results so I ended up writing some code to do this. It’s available on my Github, and I’ll likely be adding parsers for other browsers to the repository, as well as updating the code if I think of more fun things to do. What follows is an explanation of my process in writing the code, as well as a high-level overview how it works.
For reference, Firefox stores URLs and bookmarks in a SQLite database named places.sqlite. You can find it at:
For schema information the Mozilla Developer Network describes how the information is structured.
As mentioned, the first thing I did was search the Internet to see if a PowerShell script was already written that pulled the history from the sqlite file. There is a script here that did this with a regex, but I noticed it didn’t extract the URL querystring, and I figured there is probably a more elegant way of doing this (ideally by using a module that can parse the sqlite structure). As of this writing, Microsoft does not have native PowerShell support for SQLite (that I could find), but there are a few choices in the gallery for SQLite modules:
I settled on the PSSQLite module and installed it (Install-Module -Name PSSQLite). It should be noted the Microsoft doesn’t guarantee the security of anything in the PowerShell gallery, so take a look at the code and install at your own risk. The cmdlets included help data, and the PSSQL Github contained useful examples.
I ended writing up two functions, Get-FirefoxHistory and Get-FirefoxBookmarks, that use cmdlets from the PSSQLite module to query the places.sqlite file. Interacting with places.sqlite with the PSSQLite cmdlets are very straight forward. The following returns all URLs in the browser history:
Invoke-SqliteQuery -DataSource $SQLiteDbPath -Query “SELECT url FROM moz_places”
In the above example, the $SQLiteDbPath variable is set to the path of the places.sqlite file, and the Query parameter contains the SQL query to be used.
This cmdlet queries the moz_places table within the places.sqlite file. Running the cmdlet without any options will return a the contents of the database in the form of objects, which allow for parsing via normal PowerShell control statements. For example, to return all URLs that contain the string ‘espn’, you can run the commands:
However, I included several optional parameters in the cmdlet to handle a few use cases:
AllUrls: Returns all URLs in the browser history
Search: Searches all URLs/querystrings, for the specified word.
NumberOfDays: Specify the max number of days to return URLs
MostVisited: Sorts the URLs by visit count and return only the n most visited URLs along with their visit count.
Query: Used to specify the entire query.
ShowColumns: Returns the columns of the table. Used to help generate custom queries.
So, to return the same results in the previous screenshot, you can use the Search parameter:
To get the Firefox bookmarks you actually need to query two tables. Well, technically, all of the URLs, including the bookmarks, are in the moz_places table, so by getting the browser history you are also getting the bookmarks. But let’s say you just want the bookmarked URLs. The moz_bookmarks table has a column, ‘fk’, that corresponds to the ‘id’ column in moz_places table. Correlating these values was a little more difficult than I thought it would be, and I’ll talk about that in a bit, but eventually I got everything to work. This function doesn’t have any built-in options. It just returns the bookmarks.
Some Gotcha’s in the code:
- SQLite subqueries:
I thought the following query would grab the bookmarks, but this didn’t work:
SELECT url FROM moz_places WHERE id = (SELECT fk FROM moz_bookmarks WHERE fk NOT NULL)
I think it was an issue of objects not expanding into the correct datatype for the subquery. Either way, here was my workaround:
The important part of the workaround is creating a query in a variable, $VarQuery, where I use a string ‘var’ as a placeholder for a variable. This allows me to call the replace function and substitute the value of my variable with the placeholder.
- Converting to UNIX Epoch time:
The places.sqlite file stores a last visit timestamp in a column named ‘last_visit_date’. The timestamp is in the UNIX Epoch format. Here is how I handled the conversion and use:
If the $NumberOfDays parameter is used, that number is added (subtracted) from the current date. Epoch time is calculated from 01/01/1970, so this code snippet calculates the total seconds between $date1 (01/01/1970) and $date2 (n days ago from the current date). Note, that n days ago is calculated as (Get-Date).AddDays(-n).
There is definitely room for improvement in the code, and hopefully I’ll continue to improve the features and functionality of the code.
Feedback is welcome.