Viewing Firefox cookie information with sqlite3 on Mac OS X

On a Macintosh, aka Mac, OS X/macOS system, the user profile directory where the Firefox web browser stores its data can be found at "/Users/account_name/Library/Application Support/Firefox/Profiles" where account_name is the relevant user name. The profile directory for the account will be a sequence of 8 characters followed by ".default". E.g.:

$ ls "/Users/jasmith1/Library/Application Support/Firefox/Profiles"

Within that directory will be a cookies.sqlite file within which Firefox stores browser cookies. You can view those cookies outside of Firefox using the SQLite relational database management system software found on OS X/macOS systems. You can examine the sqlite file with the sqlite3 command, which you can run from a command line interface (CLI), i.e., a Bash shell prompt, by opening a Terminal window; the Terminal application is found in the Applications/Utilities directory. You can examine the structure of the database using the SQLite .schema command. There is a moz_cookies table within the database.

$ sqlite3  ~/Library/Application\ Support/Firefox/Profiles/bgq13udo.default/cookies.sqlite
SQLite version 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE moz_cookies (id INTEGER PRIMARY KEY, baseDomain TEXT, originAttributes 
TEXT NOT NULL DEFAULT '', name TEXT, value TEXT, host TEXT, path TEXT, expiry INTEGE
R, lastAccessed INTEGER, creationTime INTEGER, isSecure INTEGER, isHttpOnly INTEGER,
 appId INTEGER DEFAULT 0, inBrowserElement INTEGER DEFAULT 0, CONSTRAINT moz_uniquei
d UNIQUE (name, host, path, originAttributes));
CREATE INDEX moz_basedomain ON moz_cookies (baseDomain, originAttributes);

If you want to see the domain names from which cookies have come, you can use the Structured Query Language (SQL) command select baseDomain from moz_cookies.

Udemy - April2516-25off-sitewide120x600
sqlite> select baseDomain from moz_cookies;
<text snipped>

Since you will likely have multiple cookies from some domains, as in the example above, if you don't want to see multiple instances, but only unique domain names, you can use the SQL command SELECT DISTINCT as shown below.

sqlite> select DISTINCT baseDomain from moz_cookies;
<text snipped>

The baseDomain column contains the domain name, but if you want the fully qualified domain nmae (FQDN), you should select the host column for records in the table, which you can do with SELECT DISTINCT baseDomain, host from moz_cookies or SELECT DISTINCT host from moz_cookies, if you just want the host name. E.g., for below, you can see the FQDNs for include and, wheareas in other instances, the baseDomain and host values are the same.

sqlite> select DISTINCT baseDomain, host from moz_cookies;||||||||||||||||||||||||||||
<text snipped>|||||||||||||

You can see the time the cookie was created on the system with select host, creationTime from moz_cookies.

 SQLite Tutorial for beginners
SQLite Tutorial for beginners
1x1 px

sqlite> select DISTINCT host, creationTime from moz_cookies;|1455832455354689|1455832457485737|1455832459859021|1455832461137879|1455832463173381|1455832495245290|1455832495798356|1455832575977197|1455832580463061

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in date and time functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

The 16 digits for time stamps in Firefox's cookies.sqlite file represent the date and time in Unix time, aka epoch time. You will need to convert those, which you can do using the datetime function in order to see them in a more human-readable format. You can do so, using select host, datetime((creationTime/1000000),'unixepoch') from moz_cookies as shown below. The division by 1,000,000 is for converting from microseconds to seconds - see nsICookie2, which notes for creationTime that it is "The creation time of the cookie, in microseconds since midnight (00:00:00), January 1, 1970 UTC."

sqlite> select host, datetime((creationTime/1000000),'unixepoch') from moz_cookies;|2016-02-18 21:54:15|2016-02-18 21:54:17|2016-02-18 21:54:19|2016-02-18 21:54:21|2016-02-18 21:54:23|2016-02-18 21:54:55|2016-02-18 21:54:55|2016-02-18 21:56:15
<text snipped>|2017-02-28 03:33:21|2017-02-28 14:35:16|2017-01-17 15:43:31|2016-04-09 15:29:13|2016-04-09 15:29:13|2017-02-28 15:06:36|2016-11-30 21:18:09

But that will show the value in Coordinated Universal Time (UTC), also referred to as Zulu time, or Greenwich Mean Time (GMT). But you might prefer to have the date and time converted to local time on the system on which you are working. If so, use select host, datetime((creationTime/1000000),'unixepoch','localtime') from moz_cookies, instead. E.g., since the time zone on the system on which I ran the above command is fiver hours behind UTC time, a time stamp of 15:05:36 represents 10:05:36 AM local time on the system. The output below shows the addition of 'localtime' as a parameter to the datetime function to view all times in the local time zone.

sqlite> select host, datetime((creationTime/1000000),'unixepoch','localtime') from moz_cookies;
select host, datetime((creationTime/1000000),'unixepoch','localtime') from moz_cookies;|2016-02-18 16:54:15|2016-02-18 16:54:17|2016-02-18 16:54:19|2016-02-18 16:54:21|2016-02-18 16:54:23|2016-02-18 16:54:55|2016-02-18 16:54:55|2016-02-18 16:56:15
<text snipped>|2017-02-27 22:33:21|2017-02-28 09:35:16|2017-01-17 10:43:31|2016-04-09 11:29:13|2016-04-09 11:29:13|2017-02-28 10:06:36|2017-02-22 12:38:10|2016-11-30 16:18:09

If you wish to restrict your query to just records for a certain host name, you can use the SQL WHERE clause as shown below.

sqlite> select host, datetime((creationTime/1000000),'unixepoch','localtime') from moz_cookies where host = '';|2017-02-28 10:06:36

You can do the same type of conversion for other datetime fields, e.g. lastAccess and expiry. The lastAccess time is the last time the cookie was accessed, i.e. created, modified, or read by the server, in microseconds since midnight (00:00:00), January 1, 1970 UTC - see nsICookie2.idl - DXR. E.g.:

sqlite> select host, datetime((creationTime/1000000),'unixepoch','localtime'), datetime((lastAccessed/1000000),'unixepoch','localtime') from moz_cookies;|2016-02-18 16:54:15|2017-02-27 15:11:59|2016-02-18 16:54:17|2017-02-27 15:11:59|2016-02-18 16:54:19|2017-02-28 10:05:11|2016-02-18 16:54:21|2017-02-27 21:10:01|2016-02-18 16:54:23|2017-02-28 09:47:53|2016-02-18 16:54:55|2017-02-27 21:57:50|2016-02-18 16:54:55|2017-02-27 22:33:45|2016-02-18 16:56:15|2017-02-28 08:16:48
<text snipped>|2017-02-27 22:33:21|2017-02-28 10:06:22|2017-01-17 10:43:31|2017-02-28 10:06:25|2016-04-09 11:29:13|2017-02-28 10:06:25|2016-04-09 11:29:13|2017-02-28 10:06:25|2017-02-28 10:06:36|2017-02-28 10:38:24|2017-02-28 10:29:04|2017-02-28 10:31:13|2016-12-01 16:09:32|2017-02-28 10:29:05|2017-02-28 10:38:25|2017-02-28 10:38:25|2017-02-22 12:38:10|2017-02-28 10:41:09|2016-11-30 16:18:09|2017-02-28 10:41:26

If you use this method to query the expiry value, you would find that many entries have an expiration date in the past in the year 1969.

sqlite> select host, datetime((expiry/1000000),'unixepoch','localtime') from moz_cookies;|1969-12-31 19:29:31|1969-12-31 19:25:18|1969-12-31 19:25:18|1969-12-31 19:25:18|1969-12-31 19:24:50|1969-12-31 19:24:49|1969-12-31 19:25:18|1969-12-31 19:25:03|1969-12-31 19:27:20|1969-12-31 19:29:31|1969-12-31 19:29:31|1969-12-31 19:25:18|1969-12-31 19:25:17|1969-12-31 19:25:17|1969-12-31 19:25:18|1969-12-31 19:25:18
<text snipped>|1969-12-31 19:24:48|1969-12-31 19:24:49|1969-12-31 19:25:06|1969-12-31 19:25:06|1969-12-31 19:25:19|1969-12-31 19:25:51|1969-12-31 19:25:04|1969-12-31 19:24:48|1969-12-31 19:24:48|1969-12-31 19:25:19|1969-12-31 19:24:48|1969-12-31 19:24:50|1969-12-31 19:25:03

That is because the expiry timestamp is stored as seconds since midnight (00:00:00), January 1, 1970 UTC whereas the creationTime and lastAccessed values are stored as microseconds since that time - see nsICookie2. So to view the expiry field for records in the table, don't divide the value by 1,000,000. I.e., use a command like the one shown below.

sqlite> select host, datetime((expiry),'unixepoch','localtime') from moz_cookies;|2026-02-15 16:54:15|2018-02-17 16:54:18|2018-02-17 16:54:19|2018-02-17 16:54:21|2017-03-20 17:54:23|2017-03-17 17:54:55|2018-02-17 16:54:55|2017-08-18 17:56:15
<text snipped>|2017-09-30 20:00:00|2017-02-28 12:03:52|2019-02-28 11:53:54|2017-02-28 12:03:54|2018-02-28 11:53:54|2017-03-30 13:01:13|2017-08-27 13:03:12

The nsICookie2 attributes are listed below.

Attribute Type Description
creationTime PRInt64 The creation time of the cookie, in microseconds since midnight (00:00:00), January 1, 1970 UTC. Read only.
expiry PRInt64 The actual expiry time of the cookie, in seconds since midnight (00:00:00), January 1, 1970 UTC. (where 0 does not represent a session cookie). Read only.
isHttpOnly boolean true if the cookie is an http only cookie. Read only.
isSession boolean

true if the cookie is a session cookie.

Note:  That expiry time will also be honored for session cookies; thus, whichever is the more restrictive of the two will take effect.
Read only.
lastAccessed PRInt64

The last time the cookie was accessed, in microseconds since midnight (00:00:00) on January 1, 1970 UTC. "Accessed" means creation, modification, or reading by the server.

Note: This time may be approximate.
Read only.
rawHost AUTF8String The host (possibly fully qualified) of the cookie, without a leading dot to represent if it is a domain cookie. Read only.

Related Articles:

  1. Using SQLite
  2. Running SQL commands from a text file using sqlite3
  3. Count Records with SQL
  4. DB Browser for SQLite on OS X
  5. Remove a site's cookies from Firefox


  1. SQLite Query Language: Date And Time Functions
  2. Firefox Forensics
    Posted by infosecbryce
    Date: July 17, 2012
    Forensic Focus - For Digital Forensics and Ediscovery Professionals
  3. HTTP Cookies: What's the difference between Max-age and Expires?
    By: Peter Coles
    Date: October 24, 2009
    MrColes - Peter Cole's Blog
  4. HTTP cookies
    Mozilla Developer Network (MDN)
  5. nsICookie2.idl - DXR
    mozilla-central - DXR
  6. nsICookie2
    Mozilla Developer Network (MDN)