How to open and read SQLite files

I am trying to recover something from Mozilla Firefox history, and grep returns lots of binary files, I think they come from sqlite content. How can I view them in human-readable form, as text? At the top of the file I see something like SQLite format 3

2 Answers

I don't think there's any general way to turn an arbitrary binary file into human-readable form - you would need to know the byte-by-byte format in order to unpack and convert it. (There is the strings utility, but that will only extract ASCII sequences that happen to be embedded in the file).

In the specific case of Mozilla Firefox, it appears to use SQLite 3 - this is likely what you are seeing at the top of the file - this is one time where strings is useful:

$ strings ~/.mozilla/firefox/
SQLite format 3

although you could also use the file command to identify the content type:

$ file -b ~/.mozilla/firefox/
SQLite 3.x database, user version 65536, last written using SQLite version 3013000

Probably the best way to display / search these specific files is to use the sqlite3 command line client (from package sqlite3) to .dump them e.g.

$ sqlite3 ~/.mozilla/firefox/ .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

SQLite is a type of relational database, so that file will contain tables, columns and indexes.

sqlitebrowser is a GUI based application that lets you browse inside SQLite files.

Of course you can simply install sqlite3 itself and use the sqlite3 command-line tool to open the database.