I was showing off a trick to export Firefox SQLite tables to a spread sheet, and while she is a forensics person, she had never ever heard of this trick. It is neat enough to know when working off an image to pull the entire history of a Firefox user by using the SQLite table manager Firefox plugin. You can also find this plugin for Chrome that makes things just as easy. This article though will focus on SQLite and Firefox.

With all things there are a number of assumptions being made here if working in a firefox forensics capacity, one that you are working on an image taken of the device in question, and that you understand the protocols involved in forensics, are working in a forensics capacity, and are duly authorized to do this kind of work. Or that you are simply interested in what is buried in your Firefox SQLite tables, and are working on your own system for research or just casual interest.

First download the plugin using Firefox. Install and reboot an instance of Firefox on the computer you are working on.

Open up SQLite Manager under Firefox >> Web developer >> SQL Lite Manager, it should open up in a new window if you did not change any of the defaults that come along with the program.

Click on Directory, it should default to the directory of the user of the Firefox application, if not you can tool around in the roaming profile for the user directory you are interested in observing.

There are a number of tables in the standard Firefox installation; each table performs a different function within the Firefox forensics program.

Addons – any browser Addons or otherwise are stored here, what they are, the version number, and other data as to who gets to use it in a multi-profile environment. Depending on what you are looking for, there might be multiple Addons that the user did not know about such as extra toolbars, or other items that might mean the browser was not theirs anymore.

Chromeappstore – this may or may not be present in all Firefox installs, for example where I work does not have this file, but my home installation does – this controls the search engine container in Firefox, usually this is set to Google right out of the box, but consumers can set the default search engine and it will be stored here.

Content-prefs – The purpose of this feature is to enable users to set preferences for browser and content settings (I.E. text zoom, page style, and character encoding) on a site-specific basis instead of just a tab- or page-specific basis, and to persist those preferences across page visits and browsing sessions, in order to improve the usefulness of those settings.

This is a good file to do forensics analysis on because it shows sites that are used on a regular basis, some of which the user might not know was being stored. Along with the history file, prefs allows a forensics investigator to know if the visit was casual/accidental or if it was a site they were always going to enough to have individual preferences set within the browser framework. This is very effective at showing intent and frequency along with the browser history.

Cookies – this is a repository of every cookie that is set by the system, this may or may not be fully cleaned out when a user deletes all cookies, or using a program like CCleaner to erase cookies. Non-erasure depends on cookie persistence, alternative cookie storage locations, and a number of other persistent cookie processes as to if this file gets cleaned out fully by Firefox or by outside programs.

Cookies are going to be contentious because of the way that advertising is linked together to help create a better profile of individual users. Some sites set many tracking cookies, as well as tracking cookies for advertising, other sites, audio or video streams, or even the random adult site. A cookie being set does not mean that a user went there; you have to understand how cookies, advertising, and user tracking works before you can make the assumption that a user visited a particular site. All times are in UNIX universal time, so you will want to check the last access time against the creation time against the day to see if it was a casual drive by cookie, or an intentional site visit.

Downloads – a repository of every file downloaded, this is what builds the download list within Firefox that you see popup when you are downloading something. This will erase fully if you clear the download queue using Firefox. A good Firefox Forensics tool.

The interesting part about downloads is that some users simply will never clear this list, which makes for an interesting pattern of behavior in relationship to downloads. You can tell a lot about a person by what they download and store. If someone is collecting, the download history could show that there is a repository of information somewhere on the network that needs to be found because it will be of interest in relationship to a forensics investigation.

Extensions – this is the repository for all extensions, like the SQLite manager

The cool part about extensions is that it can give an indication of user behavior, what did they care enough about to add an extension to their browser. Did they add a proxy system, or the TOR browser extension to their system? If they did it gives the investigator a bigger range of things to look at in terms of what the user was thinking about security, privacy, and avoiding corporate information security systems.

This file will normally popup as corrupted or not available when Firefox is running, so you want to view this file when Firefox is not running on the target system.

Formhistory – this is a history of every form you ever filled out online, from e-mail to catalog, it is all in this file. The interesting thing about form history is that ties into the history file as well. If a user was often logging into something, or using the same form a lot (for example, the form data below shows that I do a lot of online shopping, and enter a lot of shopping data into systems with a fairly consistent pricing scheme) then the data is here. If you ever wondered why your system keeps on showing your e-mail address and zip code when filling out a form, this is where Firefox is pulling the information from. Another good firefox forensics technique.

Permissions – this is a history of what site has what permissions, like allowing or not allowing popups, or sites that have linked shopping credentials. Sometimes this file will be very full of data, other times it will not be.

If the permissions has an expire type and expire time in red, this is a default to Firefox process in which these require specific permission from the user, or popups are blocked. Permissions run in range from numbers 1 through 6, but in the Mozilla code base they do not state what each number represents in that numbering process.

Items are also categorized by what they can or cannot do, for example, PayPal will not cache the password for the account on the system. If it is a regular use site that requires permission (in this case Zenescope and shopping there), or permissions around the use of Google services, once logged in Google uses Xauth to log you in across the entire Google system. Facebook works similarly to this, and will put permissions data here if it is available.

Places – is a proto database of places visited, book marks, and attributes for those sites commonly visited by the browser. This is also where browser history comes into place, allowing for an investigator to pull the entire web history for a user. This includes bookmarks, book mark properties, favorite icons, and a host of other information about sites that are visited. Tying this file to cookies, form history and permissions provides a much more robust view of the user, and how they were using the browser.

Sometimes some (not all) investigators will only focus on the history file. This gives an incomplete view of user behavior, and really should be tied to all the other data that is stored in the SQLite files. This is an important process to follow because of the nature of the internet, marketing, advertising, tracking, and how some sites will deposit a lot of data that does not mean that a user went there. It might simply mean that on the GQ web site they went to have a link to Playboy. Or that the forum they went to had a link to Adult Friend Finder. It is very important to match out history, cookie, form, and other data to determine if the data is showing that the visit was intentional, versus a drive by cookie session.

Search – this is a set for available search engines, usually it just has Google, but can also have Bing and other search engines.

Almost any search engine can be put in here, some versions of Firefox have all the search engines, and others do not.

Signons – this is a repository of all places that the user has kept their username and login stored in the browser. All the passwords are hashed, but using rainbow tables they can be guessed depending on how the user manages password security.

Of all the tables signons is probably the most interesting, combined with the history and cookie table, if the user signed on to the web site you can state that it was an intentional visit. For police purposes, the password hash can be run through a rainbow table, so that they can have access to all the other sites that the user visited. This can be very important, and is often not cleaned out when removing cached data. Most users will want to keep their form and signon data when clearing cache. This is an alternative way to find out what sites the user thought was important, and work out a way to access them. These are also time stamped for last access, create time, and the number of times the user accessed the information.

Webappstore – This file stores all the tokens from XAuth (all localStorage data), the main idea of the XAuth is that the declared token will be accessible only from the XAuth.org domain that it was set for. You do not normally have the ability to see what exactly has been saved in your local storage. Some web sites store data here, and it may or may not be cleaned out when removing cookies, history, or form information. This can make for interesting information, but is generally minimal depending on the web sites being visited. This file can be used in conjunction with the cache directory to validate access times if this table is well populated.

All the data from the SQLite tables can be exported to a spreadsheet for easier use and sharing with law enforcement, Human Resources, or other entities within the organization that need this data to help set a case, fire an employee, or simply counsel and employee for improper internet usage.

The data is profile specific, in a shared computing environment you have to ensure that the files you are pulling are for the person who was logged in at the time. You also want to ensure that the data has not been manipulated, as using the SQLite manager; it is easy to add or subtract items from the files. The create time stamps are very important for this, but no data is assured to be non-manipulated. As with all investigations, you should try to form a picture of behavior based on more than one source. The data that is contained with the SQLite tables can go a long way to determining and validating user behavior based on the data that is stored in all these tables.