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
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 DESCRelational 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!