As I noted, frecency is a number generated by Firefox that combines different measures of user behavior with a URL, including bookmarking it, frequency and recency of visits. The result is a single number that tries to quantify how interested the user is in the URL. This means that with a simple SQL query (select * from moz_places order by frecency desc limit 20 for example), we can get a snapshot of the user's current sites of interest. This could be particularly useful for an "acceptable use policy" investigation, where the issue may turn around how much of a user's browsing history is personal vs. work-related. If the FarmVille app is among a user's top 20 sites sorted by frecency, a violation should be pretty easy to prove. Since frecency is based on other known factors, you could go through the user's full behavior to see what exactly they did to get there (type of visit, etc). That information is stored in moz_inputhistory (which we already covered) and moz_historyvisits.
As the names imply, moz_places records the urls that a user visits, and moz_historyvisits record the details of each visit to the url. Since it records each visit to each page in the domain, there will be a lot of records ... which makes a shorthand like frecency very useful. The columns are id (the index), from_visit (the referrer), place_id (the connection back to the URL entry in moz_places), visit_date (timestamp in PRTime again), visit_type, and session.
Let's step through what this means by actually analyzing my browsing behavior. First, I ran select * from moz_places order by frecency desc limit 20 to grab the top 20 websites from moz_places to look for something interesting. I'm arbitrarily picking http://www.wired.com as my URL of interest. moz_places.id for this url is 485, so I run select * from moz_historyvisits where place_id=485 which yields:
seven visits. Now, for these results we can ignore the first column (id). Next is from_visit, which gives the id of the page I visited before visiting the current page. For the last three visits, it's 0 indicating that I opened a new tab and went straight to Wired. For the others it refers to the immediately prior id number, which is what you would expect if I was only browsing in one tab at the moment. If the numbers were not consecutive, that would imply multiple-tabbed browsing. In the first entry, I came to Wired after visiting the entry in moz_historyvisits.id=941. Looking up that entry, I see that was a visit to place_id=484, which is http://wired.com. Incidentally, that's true for the first four entries. To explain why, see that the visit type for all four www.wired.com entries is 5, indicating a permanent redirect (HTTP 301). For the respective entries for http://wired.com, the visit type is 2, indicating I typed the URL. So, at those times I typed wired.com in the address bar. Firefox logged the visit and took me to http://wired.com, which redirected me to http://www.wired.com and logged another visit. For the last three visits to Wired, I typed www.wired.com directly. Since those were the more recent and since I logged 7 visits to www.wired.com and only 4 to wired.com, www.wired.com was the one that was on top in frecency (8800 vs. 4813) instead of the non-www version, despite the fact that a typed URL gets a frecency bonus over a redirect URL. Also note that just because I came to www.wired.com via a redirect, doesn't mean I didn't intend to go there. Seeing what site took me there is essential to determine what my intentions were.
Obviously this is a trivial example, but it should be pretty apparent how it could be applied to a more interesting investigation.
There are a set of tables called moz_annos, moz_anno_attributes and moz_items_annos, but as far as I can tell they are used by Firefox extensions and are unlikely to be useful for forensics.
That's everything of interest out of places.sqlite, but there are other tables that have useful information: tables like signons.sqlite. This is where the logins and passwords are stored. If the user hasn't specified a master password for his Firefox profile, then the usernames and passwords will be clearly available through Options > Security > Saved Passwords. If there is a master password set and you don't have it, then you won't be able to get to the list of sites and passwords directly. Nothing stops you from accessing signons.sqlite directly, though.
signons.sqlite contains two tables, moz_logins and moz_disabledHosts. moz_disabledHosts is a list of websites which the user doesn't want Firefox to ask to remember logins. moz_logins is where the sites, usernames and passwords are stored. The columns are id, hostname (the login page), httpRealm, formSubmitUrl (the actual page the username and password get sent to), usernameField (the identifier of the username field), passwordField (the identifier of the password field), encryptedUsername, encryptedPassword, guid (global ID, for login syncing), encType, timeCreated, timeLastUsed, timePasswordChanged, timesUsed.
Setting a master password in Firefox does not change any of the data here, it just blocks you from viewing the decrypted passwords IN Firefox. You can still get lots of useful browsing information from the file, everything except what their username and passwords are. Documentation on signons.sqlite is scanty, but from what I can tell all the timestamp fields are new in Firefox 4. Strangely, these timestamps aren't encoded in PRTime (microseconds since Unix epoch) like the rest of the timestamps. Instead it's miliseconds since epoch, so we get all the timestamps with:
sqlite> select hostname, datetime(timeCreated/1000,'unixepoch'),datetime(timeLastUsed/1000,'unixepoch'),datetime(timePasswordChanged/1000,'unixepoch') from moz_logins;
For my test system, the results are:
This system has credentials saved for two Facebook accounts. For the first, the login was saved on March 17 and last used on March 31. For the second, it was only used once, on April 4. Neither has changed passwords. This can be useful if you need to prove that a user actually logged into a site, rather than just visiting it. If a user clears their browsing history but not saved passwords (which is default behavior), this will still show up as well. It's also yet another way to prove that a user actually intended to go to a site repeatedly instead of it being an accidental click or redirect.
The usernames and passwords are encrypted, but the master password and decryption keys are stored in key3.db (more info here). Since you have the decryption keys, decrypting the passwords should be possible if needed. There are tools out to do that, so I'll leave that as an exercise to the reader. :)
In Part 4, I'll go into cookies and cached files.