How to Manage Firefox SQLite Databases

Managing Firefox Data with SQLite

Firefox, one of the most popular web browsers, stores much of its internal data, such as bookmarks, browsing history, cookies, and extensions, in SQLite databases. This guide explains the structure and use of Firefox's SQLite databases, including how to locate them, query them, and interpret the stored data.

Why Firefox Uses SQLite

1. Lightweight: Ideal for managing small to medium-sized data efficiently.

2. Standalone: No separate server setup required.

3. Cross-Platform Compatibility: Works seamlessly across all platforms Firefox supports.

4. Flexibility: Easily handles relational and structured data.

Locating Firefox SQLite Files

Firefox SQLite databases are stored in the user profile directory.

Default Locations:

• Windows:

C:\Users\<YourUsername>\AppData\Roaming\Mozilla\Firefox\Profiles\<ProfileName>

• macOS:

~/Library/Application Support/Firefox/Profiles/<ProfileName>

• Linux:

~/.mozilla/firefox/<ProfileName>

The <ProfileName> is a unique identifier for the Firefox user profile.

Common SQLite Files:

1. places.sqlite: Stores bookmarks, browsing history, and metadata.

2. cookies.sqlite: Contains all browser cookies.

3. formhistory.sqlite: Saves autocomplete form data.

4. webappsstore.sqlite: Stores site-specific data.

Querying Firefox SQLite Databases

1. Install SQLite Command-Line Tool

Install SQLite to query Firefox’s SQLite files:

sudo apt install sqlite3   # For Linux
brew install sqlite3       # For macOS
choco install sqlite       # For Windows

2. Open a Firefox SQLite Database

sqlite3 places.sqlite

Explanation

• Opens the places.sqlite file for querying.

3. Query Browsing History

Code:

-- Query for browsing history
SELECT url, title, visit_count, last_visit_date
FROM moz_places
ORDER BY last_visit_date DESC
LIMIT 10;

Explanation

• moz_places: Table storing URLs and visit details.
• visit_count: Number of times the URL was visited.
• last_visit_date: Timestamp of the last visit.

4. Query Bookmarks

Code:

-- Query for bookmarks
SELECT moz_bookmarks.title, moz_places.url
FROM moz_bookmarks
JOIN moz_places ON moz_bookmarks.fk = moz_places.id;

Explanation

• moz_bookmarks: Table storing bookmarks metadata.
• moz_places: Linked table for URLs.
• JOIN: Combines data from both tables.

Working with Cookies

Query Stored Cookies

Code:

-- List cookies stored in Firefox
SELECT host, name, value, expiry
FROM moz_cookies
WHERE host LIKE '%example.com%';

Explanation

• moz_cookies: Table storing cookie data.
• host: Domain for which the cookie is valid.
• expiry: Expiration time of the cookie.

Exporting Data from Firefox SQLite Files

Export Query Results to CSV

Code:

sqlite3 places.sqlite -header -csv "SELECT url, visit_count FROM moz_places;" > history.csv

Explanation

• -header: Adds column headers to the output.
• -csv: Outputs the result in CSV format.
• > history.csv: Redirects output to a file.

Manipulating Firefox SQLite Files

Update Visit Count

Code:

-- Update the visit count of a specific URL
UPDATE moz_places
SET visit_count = visit_count + 1
WHERE url = 'https://example.com';

Explanation

• Updates the visit_count column for the specified URL.

Delete Old History

Code:

-- Delete browsing history older than a specific date
DELETE FROM moz_historyvisits
WHERE visit_date < strftime('%s', '2023-01-01') * 1000000;

Explanation

• Deletes history records before January 1, 2023

Important Notes

• Backup Before Editing: Always back up your Firefox profile directory before modifying any SQLite files.
• Read-Only Mode for Safety: Open SQLite files in read-only mode unless editing is necessary:

Code:

sqlite3 -readonly places.sqlite

• Firefox Must Be Closed: Ensure Firefox is closed before accessing its SQLite files to avoid database lock errors.

Advantages of Using Firefox SQLite

1. Transparency: Data is stored in a standard, accessible format.

2. Custom Analysis: Allows users to analyze browsing habits or export data.

3. Interoperability: Easy to use with external tools like Python or Excel.

Practical Guides to SQLite Snippets and Examples.