Issue:
URLs visible within the places.sqlite database file when viewing the file in hex view that are not visible when viewing the file in SQLite Manager or FTK’s viewer. The URLs seen in hex view are relevant to the investigation.
Test Information:
Path for Mozilla information (Windows XP): C:\Documents and Settings\%user%\Application Data\Mozilla\Firefox\Profiles\%uniquevalue%.default\
OS: Windows XP SP 3, 32 bit
Firefox version: 15.0.1
Within a virtual machine running Windows XP SP3 a clean installation of Mozilla Firefox 15.0.1 was installed. The places.sqlite created upon installation of Firefox was deleted, which forces Firefox to create a new database upon the next time the program is run.
The Firefox add-on SQLite Manager was downloaded and installed. Once it was installed it was launched by going to Tools>SQLite Manager:
To obtain a baseline, Firefox was launched and the places.sqlite database was rebuilt. SQLite Manager was launched to view the default entries in places.sqlite. By default Firefox installs five bookmarks, which can be seen below:
SQLite Manager shows the above bookmarks within the places.sqlite file:
As an overview, SQLite Manager is a great tool for viewing these database files. To search records, click on the “Browse & Search” tab. Although you can directly query the SQLite tables this way, unless you are familiar with SQL searches, I recommend exporting the data and using Excel.
To better search and review information, export the data to a CSV file. Once you click the “Export Wizard” tab, make sure to check the box “First row contains column names” and then select how you want to export the data. Once you have selected the appropriate settings, click “OK” and you should receive a dialog box stating that your records have been exported.
Navigate to your newly created CSV file and open it with Excel:
Above is the standard Excel view of a CSV file. When working with a large amount of data, there are a few tricks you can use to make data management easier. This includes highlighting the top row, center and bold the font on the first row, insert gridlines, and then freeze the top row and add filtering to the top row. Also, consider hiding any columns or rows that are not applicable to your investigation:
By using filtering (indicated by the dropdown arrow to the right of each heading in the top row), it is possible to quickly sort by the relevant information within each column. See below:
This file will now have to be saved as an Excel workbook since this file is no longer compatible the CSV format.
Below is a view of the places.sqlite file while viewing it in FTK. Notice the same information is seen below as what we have seen in the SQLite Manager. After reviewing the entire file, no other entries were located.
Note – the places.sqlite file is locked by the first application that accesses it. This is important to note during testing because it will alter the normal operation of Firefox. For example, if the places.sqlite file is open within FTK Imager and then Firefox is opened, Firefox will act normal, however no data is actually recorded in the places.sqlite file since FTK Imager has locked it.
In an attempt to replicate the initial problem of having URLs visible in the places.sqlite file but not within Firefox, SQLite Manager, or FTK’s parsed viewer, the following steps were taken:
1. Firefox was launched
2. The following URLs were visited:
a. Google.com
b. Cnn.com
c. Iacis.com
d. Whitehouse.gov
3. SQLite Manager was launched
4. Reviewed entries with this tool
The entries in my history match exactly what I navigated to. Now I opened SQLite Manager and reviewed that information:
SQLite Manager showed the exact same information as expected. When viewing the places.sqlite file in FTK Imager, the four entries were also seen. The entire places.sqlite file was viewed and no abnormal entries were located.
The IACIS.com URL begins at decimal offset 64308. This is important, keep note of this for later.
Next, Firefox was re-launched and all Internet history was cleared. This was accomplished by checking all available boxes and selecting “Everything” from the dropdown menu:
Within Firefox, all of the history entries are now gone:
SQLite Manager was opened next to see what entries it saw:
SQLite Manager also does not show any information for the URLs after the history has been deleted. Next, FTK Imager was launched and the places.sqlite file was added as an individual file:
With the exception of a few bytes of data, all areas that used to contain the URL’s I had visited had been overwritten with zeros. At offset 64308 where my cursor was (shown above in small red box), you can see that iacis.com is gone.
The next test was checking how Private Browsing mode in Firefox would affect the entries in the places.sqlite file.
The following was done for this test:
1. Deleted places.sqlite file to force Firefox to build a new one.
2. Launched Firefox.
3. Browsed in normal mode to the following websites:
a. computer-forensics.sans.org
b. facebook.com
c. youtube.com
d. yelp.com
4. Private Browsing mode was turned on and the following sites were navigated to:
a. yahoo.com
b. twitter.com
c. linkedin.com
d. amazon.com
5. Firefox was closed.
Firefox was re-launched and the places.sqlite file was viewed with the SQLite Manager add-on. See below:
As expected, all of the websites that were visited in normal browsing mode are shown and none of the websites visiting in Private Browsing mode are visible. Firefox was closed and the places.sqlite was viewed in FTK Imager.
In FTK Imager, the URLs visited in normal mode are visible as to be expected. It is also interesting that the new URLs overwrote the same location of the old URLs that were deleted when the history was cleared. You can see below at offset 64308 yelp.com now resides there (where IACIS.com once did):
The entire places.sqlite file was viewed in hex for any other remnants or evidence of the websites viewed in Private Browsing mode and nothing was located.
At this point it has been determined that the URLs found in the original investigation must not have been from a Private Browsing session and the history must not have been cleared from Firefox before the forensic examination was conducted. The only thing left to check was how bookmarks interacted with the places.sqlite file.
It was determined that when a bookmark is created in Firefox during normal browsing mode, it does make an entry into the places.sqlite database. The original four URLs were navigated back to and bookmarked.
See the native Firefox view below:
The SQLite Manager shows the following information:
FTK Imager shows the following:
The bookmarks start at decimal offset 58686.
To test how bookmarks interact with Private Browsing mode, the following was done:
1. Firefox was re-launched.
2. Navigated to the following websites and bookmarked them:
a. bing.com
b. wordpress.com
c. ebay.com
d. apple.com
3. Firefox was closed and re-launched.
4. SQLite Manager was launched.
SQLite Manager showed the following:
This shows that even in Private Browsing, if a URL is bookmarked, it will enter the URL into the places.sqlite file.
FTK Imager showed the following:
The bing.com bookmark entry was also shown but wouldn’t fit in the same screenshot. The bookmark for apple.com was located at decimal offset 65145.
Next, Firefox was re-launched and all history was cleared. The following bookmarks were visible:
Next the bookmarks were deleted that were created while in Private Browsing mode. The Firefox native view is shown below:
When SQLite Manager was opened, the following was seen:
In the bookmarks table, only the four remaining bookmarks are shown.
However, in the moz_places table, all of the bookmarks, including the deleted bookmarks can be found:
In looking at the places.sqlite in FTK Imager, all of the entries including the deleted bookmarks were present, although some had moved position:
Above shows remnants of the URL wordpress.com and bing.com. Offset 65145 that once had the apple.com URL now shows this:
You can see the URL for apple.com up above the original offset (highlighted in blue).
Next, Firefox was re-launched and all history was cleared again. This time it eliminated all of the deleted bookmarks from the places.sqlite database. See below:
The blue highlighted area is decimal offset 65145 again, showing that all of the old bookmark data is now overwritten.
The takeaways from this are:
- Bookmarking in Firefox, even in Private Browsing will create entries in the places.sqlite file.
- History is overwritten in the places.sqlite at the completion of a browsing session in Private Browsing mode, or anytime a user clicks Tools>Clear Recent History.
- If bookmarks are deleted, they are immediately removed from the moz_bookmarks table in the places.sqlite database.
- If bookmarks are deleted, they remain in the moz_places table in the places.sqlite database and are available to be recovered until they are overwritten.
- Deleted bookmark data will be overwritten if the user clicks Tools>Clear Recent History after deleting the bookmarks.
In this particular investigation it was my opinion that the user had at one time bookmarked the URLs that were located in the hex view of the places.sqlite file but not visible in SQLite Manager or Firefox’s native view. The user deleted the bookmarks of the websites in question prior to turning over the computer, however did not clear their recent history after deleting the bookmarks, allowing them to be recovered. This finding may show additional intent, not only that websites of interest were once bookmarked by the user, but also there was some attempt to “clean up” the computer before the examination (especially since many non-relevant bookmarks remained and only a select few were deleted).
In this particular investigation, the deleted bookmark entries correspond with thousands of deleted images recovered from unallocated space as well as orphan files located during the exam.
Awesome info! Thank you!
Thank you very much!