moz_places is the main table for the browser history. It's columns are id, url, title, rev_host, visit_count, hidden, typed, favicon_id, and frecency[sic].
As we've mentioned before, id is a simple numerical index that connects the links in moz_places to their references in the other tables. The Mozilla Wiki has a handy graphic for reference.
After id is url which is the full URL of the visited page. As you can see from my test results above, it's the exact page visited, not just the domain. That's why we see my click train from http://www.phishtank.com/ to http://www.phishtank.com/login.php and then to http://www.phishtank.com/phish_archive.php. Next is Title which is the stored title of the page.
The Mozilla Wiki notes:
Bookmarks are basically pointers to that history entry, with a few properties to determine their placement in the bookmarks folder hierarchy. The design is such that most properties of a bookmark are derived from the moz_history entry, and not the bookmark.This is no longer the case. As we already discovered, the moz_bookmarks table has it's own title field where the bookmark title is stored. Changing the bookmark title no longer overwrites the title in moz_places.
This is problematic as soon as the same URI is bookmarked more than once. Eg: If you change a bookmark's title, then the title changes anywhere you've bookmarked that URI.
rev_host is the fully qualified domain name backwards, ending with a period. So, "http://www.phishtank.com/phish_detail.php" gets converted to "moc.knathsihp.www." Firefox uses this so that they can index this and quickly search for subdomains of a primary domain. An investigator may want to use this field for the same purpose. See the Firefox source code. Thanks to FirefoxForensics for this.
visit_count is another field very useful for an investigation. This is an integer that increments for every time the user visits the site. Since the places.sqlite is associated with the user logged into the computer, this only reflects the number of times that particular user account visited the site. Some additional notes from FirefoxForensics:
This counter is incremented when the associated places.sqlite::moz_historyvisits::visit_type is not 0, 4 (embedded), or 7 (download).
Research by the author shows that reloading a page by clicking the 'Reload current page' button, pressing CTRL-R or following a self-referring URL does not increment visit_count.
If the start-up option to 'Show my windows and tabs from last time' is selected, the visit_count of those pages will NOT be incremented when the browser is started and they are loaded.
If the start-up option to 'Show my home page' is selected, the visit_count of the home page WILL be incremented each time the browser is started and they are loaded.
According to the Mozilla devs, "Hidden uri's are ones that the user didn't specifically navigate to. Those tend to include embedded pages and images. It might include something else, but I'm not 100% sure." At first glance one might want to exclude this from analysis because the user didn't specifically navigate to them, but since it includes images that would be a mistake. If the user navigated to the page containing the image, the images entry here would still give you valuable info like how many times the image has been loaded, etc.
typed is different than the input field in moz_inputhistory If the user started typing and then clicked the quick result, then it'll be listed under moz_inputhistory.input. If the user typed the full url, then it'll be under moz_places.typed.
To give an example, the first time I went to phishtank.com, I typed "www.phishtank.com" into the address bar and it took me to the site. That generated this entry in the database:
The next time I visited, I just typed 'phishtank' before Firefox recognized the URL and I went to the site. That got stored in moz_inputhistory.
favicon_id is an id number that connects to which image in moz_favicons is the favicon (the little graphic in the title bar) for the page.
The last field in moz_places is frecency. That's not a typo for "frequency", it's a combination of "frequency" and "recency". As you'd expect from the name, it's a value generated by both how often the user has visited a place and how recently they visited it. The full algorithm is here. 0 is the default score, and the higher the number the higher it appears on autocomplete results. As the algorithm link shows, it draws on a number of elements of user behavior to determine the frecency. This means that you can use it as a shorthand for determining if a user was interested in the URL and then investigate the underlying behaviors (how often they visited, how often they typed the address vs. redirect vs. following links, if they bookmarked it, etc) to articulate what exactly they did that shows interest in the link.
Frecency turns out to be a pretty complicated topic, so I'll save it (and signons.sqlite) for part 3. Part 4 covers cookies, cache, downloads and more.