Difference between pages "Mozilla Firefox 3 History File Format" and "SQLite database format"

From ForensicsWiki
(Difference between pages)
Jump to: navigation, search
(Gathering browser history: Fixed a typo in the SQL Query)
 
 
Line 1: Line 1:
Starting in Firefox 3, a new file format was used to record browser history information.  Rather than storing this information in a flat file using the mork file format (as was done in previous versions of Firefox), the information is kept in sqlite tables within a file.
+
{{expand}}
  
==File Locations==
+
SQLite databases are used by many programs including several forensics tools, e.g. [[Autopsy]] 3.
On linux systems, the history file is located in the users home folder in the .mozilla/firefox/<profile folder>/ folder and is named places.sqlite.
+
SQLite 3 is current and older SQLite packages cannot use sqlite3 databases so use sqlite3 tools.
On Windows systems, the history file is located in C:\Documents and Settings\<username>\Application Data\Mozilla\firefox\Profiles\<profile folder> and is named places.sqlite.
+
  
==File Header==
+
== SQLite3 ==
Firefox 3 history files start with <pre> 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33</pre> which represents the ascii string SQLite format 3.  This is normal for any Sqlite database file, so it may be more appropriate to verify that the file is a Firefox 3 history file by looking for the database tables within the file.  For example, at offset 120701 (0x1D77D) the hex value <pre>43 52 45 41 54 45 20 54 41 42 4C 45 20 6D 6F 7A 5F 62 6F 6F 6B 6D 61 72 6B 73</pre> can be found.  This represents the ascii string CREATE TABLE moz_bookmarks.  At offset 120973 (0x1D88D) the hex value <pre>43 52 45 41 54 45 20 49 4E 44 45 58 20 6D 6F 7A 5F 62 6F 6F 6B 6D 61 72 6B 73 5F 69 74 65 6D 69 6E 64 65 78</pre> can be found.  This represents the ascii string CREATE TABLE moz_bookmarks_itemindex.
+
  
==Database Tables==
+
SQLite version 3 uses a page-based storage where the pages are used for various types of data e.g. there are:
The places.sqlite file is essentially a database with multiple tables:
+
* lock-byte pages
<pre>  moz_bookmarks
+
* freelist pages
  moz_bookmarks_itemindex
+
** freelist trunk pages
  moz_bookmarks_parentindex
+
** freelist leaf pages
  moz_bookmarks_roots
+
* B-tree pages
  moz_keywords
+
** table B-tree interior pages
  moz_favicons (actually stored the favicons in a BLOB within the table)
+
** table B-tree leaf pages
  moz_annos
+
** index B-tree interior pages
  moz_annos_placeattributeindex
+
** index B-tree leaf pages
  moz_attributes
+
* payload overflow pages
  moz_items_annos
+
* pointer map pages
  moz_items_annos_itemattributeindex
+
  moz_places (See Below)
+
  moz_places_url_uniqueindex
+
  moz_places_faviconindex
+
  moz_places_hostindex
+
  moz_places_visitcount
+
  moz_places_frequencyindex
+
  moz_historyvists (See Below)
+
  moz_historyvists_placedateindex
+
  moz_historyvists_fromindex
+
  moz_historyvists_dateindex
+
  moz_inputhistory
+
  sqlite_autoindex_moz_bookmarks_roots_1
+
  sqlite_autoindex_moz_keywords_1
+
  sqlite_sequence
+
  sqlite_autoindex_moz_favicons_1
+
  sqlite_autoindex_moz_anno_attributes_1
+
  sqlite_autoindex_moz_inputhistory_1</pre>
+
  
==moz_places==
+
=== Write-Ahead Log (WAL) ===
The moz_places table holds some of the information necessary to reconstruct the browser history.
+
The default method by which SQLite implements atomic commit and rollback is a rollback journal. In version 3.7.0 a "Write-Ahead Log" option was added.
<pre>  id INTEGER PRIMARY KEY
+
  url LONGVARCHAR (The whole URL string)
+
  title LONGVARCHAR (The title presented from the TITLE tags on the page)
+
  rev_host LONGVARCHAR (this is the host name from the URL in reverse)
+
  visit_count INTEGER
+
  hidden INTEGER
+
  typed INTEGER
+
  favicon_id INTEGER
+
  frequency INTEGER</pre>
+
  
==moz_historyvisits==
+
== Use Cases ==
The moz_historyvisits table holds the other information that you need to link up with moz_places to reconstruct the browser history.
+
=== Web Browser Data ===
<pre>  id INTEGER
+
[[Mozilla Firefox]] and [[Google Chrome]] both use SQLite version 3 databases for user data such as history, downloaded files.
  from_visit INTEGER
+
  place_id INTEGER
+
  visit_date INTEGER
+
  visit_time INTEGER
+
  session INTEGER</pre>
+
The place_id column of the moz_historyvisits table corresponds to the id column of the moz_places table.
+
The visit_date column keeps time in Unix time format (number of seconds since January 1, 1970).  However, there are additional digits appended to the end of the number.  Therefore, only the first 11 digits of the number should be used to calculate the actual visit date.
+
  
==Gathering browser history==
+
== External Links ==
Browser history for Firefox 3 can be gathered by connecting to the Sqlite database and performing simple queries. For example, the URLs visited and the date and time of the visit can be gathered with this query:<pre>SELECT moz_places.url, moz_historyvisits.visit_date
+
* [http://sqlite.org/fileformat2.html The SQLite Database File Format], by the [[SQLite|SQLite project]]
FROM moz_places, moz_historyvisits
+
* [http://sqlite.org/wal.html Write-Ahead Logging], by the [[SQLite|SQLite project]]
WHERE moz_places.id = moz_historyvisits.place_id</pre>
+
* [http://linuxsleuthing.blogspot.ch/2013/09/recovering-data-from-deleted-sqlite.html Recovering Data from Deleted SQLite Records: Redux], by [[John Lehr]], September 13, 2013
 +
 
 +
== Tools ==
 +
* [[SQLite]]
 +
* [[SQLite Forensic Reporter]]
  
 
[[Category:File Formats]]
 
[[Category:File Formats]]

Revision as of 01:49, 30 October 2013

Information icon.png

Please help to improve this article by expanding it.
Further information might be found on the discussion page.

SQLite databases are used by many programs including several forensics tools, e.g. Autopsy 3. SQLite 3 is current and older SQLite packages cannot use sqlite3 databases so use sqlite3 tools.

SQLite3

SQLite version 3 uses a page-based storage where the pages are used for various types of data e.g. there are:

  • lock-byte pages
  • freelist pages
    • freelist trunk pages
    • freelist leaf pages
  • B-tree pages
    • table B-tree interior pages
    • table B-tree leaf pages
    • index B-tree interior pages
    • index B-tree leaf pages
  • payload overflow pages
  • pointer map pages

Write-Ahead Log (WAL)

The default method by which SQLite implements atomic commit and rollback is a rollback journal. In version 3.7.0 a "Write-Ahead Log" option was added.

Use Cases

Web Browser Data

Mozilla Firefox and Google Chrome both use SQLite version 3 databases for user data such as history, downloaded files.

External Links

Tools