Making the Most of my Commute

I recently started taking the train and I am enjoying my newly found two hours a day. As a long overdue followup to my previous post on Firefox Add-ons, I wanted to note that Firefox and some add-ons can leave some interesting forensic or reconnaissance information behind. By default, the Firefox SQLite databases are located in the C:\Documents and Settings\<Profile>\Application Data\Mozilla\Firefox\Profiles\<ProfileID&gt.default on Windows XP and in  C:\Users\<Profile>\AppData\Roaming\Mozilla\Firefox\Profiles\<ProfileID>.default on Windows Vista and 7.

The Mozilla team has documented the SQLite databases that Firefox uses here. These have been covered by others previously but are still worth noting. A few of these include but are not limited to;

•     download.sqlite: browser downloads (if not cleared)
•     formhistory.sqlite: search bar history, web forms, URL's
•     places.sqlite: bookmarks
•     signons.sqlite: covered in my previous post

While experimenting with the data stored by Firefox I noted one of my add-ons was also leveraging SQLite. Echofon, a twitter client formerly called Twitterfox, was writing data to the twitterfox_1.9.sqlite database located in the default location. Using the SQLite Manager add-on to browse the database structure gave me some interesting results.

The following select statement pulls my time line and all tweets referencing my Twitter ID.

SELECT statuses.id, statuses.in_reply_to_status_id, datetime(statuses.created_at/1000,'unixepoch','localtime'), users.name, users.screen_name, users.location, users.description, users.url, users.profile_image_url, statuses.text, statuses.source FROM statuses INNER JOIN users ON users.user_id=statuses.user_id WHERE statuses.user_id = '15707171' or statuses.in_reply_to_user_id = '15707171' ORDER BY statuses.created_at DESC;

Leveraging the datetime function converts the date and time into a more readable format. Note, the returned data includes all profile data. Moreover, each tweet has a unique ID and each record includes what tweet ID it was in reply to. This would make it very easy to reconstruct entire conversations while showing exactly what tweets the individuals were referring to (or at least clicked reply to anyway).  Similarly, to query direct messages;

SELECT direct_messages.id, datetime(direct_messages.created_at/1000,'unixepoch','localtime'), users.name, users.screen_name, users.location, users.description, users.url, users.profile_image_url, direct_messages.text FROM direct_messages INNER JOIN users ON users.user_id=direct_messages.sender_id ORDER BY direct_messages.created_at DESC

Relational data and commuting ftw! Out of respect for my followers who have private time lines, I am not including any  screen shots of the data returned, but reproducing my results should be straight forward.

With HTML 5 and Web 3.0 at our doorstep, I suspect the lines between data stored on the web and locally are going to blur significantly. While this will enable the end user to leverage web based technology more effectively, it will also provide forensic analysts and incident responders a plethora of forensics data during analysis.

What Firefox Add-ons do you use that are storing data? Happy Hunting!