Generating HTML Reports from Dropbox Data in an iPhone Full Filesystem Dump

Klas Tveita

— 3 min read

Introduction

Working with data extracted from iPhones can be challenging, especially with large filesystem dumps. While forensic tools are convenient, they are often expensive and slow to support new applications. This article will teach you how to manually review Dropbox data from an iPhone’s filesystem dump and generate an HTML report using Python, providing a cost-effective and flexible alternative.

Understanding the Data

If you are planning to reverse engineer any iOS mobile application, the first step is to try and find where the app’s data resides. When analyzing an iPhone filesystem dump, identifying the data folder for a specific app can be crucial. Each app on an iPhone is assigned a unique identifier known as a GUID (Globally Unique Identifier). To resolve the app GUID and locate its data folder, you can navigate to the Application directory in the filesystem dump, which contains subdirectories named with these GUIDs. By referencing the Manifest.db or Manifest.plist file found in the Library or Containers directory, you can map these GUIDs to their respective app names. This process involves querying the Apps table in Manifest.db or examining the CFBundleIdentifier key in Manifest.plist, which lists the app’s human-readable name alongside its GUID. Once you have identified the correct GUID, you can navigate to the corresponding directory to examine the app’s data.

Tools and Libraries

To accomplish our task, we will use the following tools and libraries:

  • Python: A powerful programming language that makes it easy to manipulate data and generate reports.
  • SQLite3: A lightweight database engine that allows us to interact with SQLite databases.
  • Jinja2: A templating engine for Python, used to generate HTML content.

Steps to Generate the HTML Report

1. Set Up the Environment

First, ensure you have Python and the required libraries installed. You can install the libraries using pip:

pip install jinja2

2. Connect to the SQLite Databases

We'll create a Python class to manage connections to the SQLite databases and execute queries.

3. Extract Data from the Databases

Using SQL queries, we'll extract the necessary data from the Dropbox.sqlite and metadata.db databases.

4. Generate the HTML Report

We'll use Jinja2 to create a template for our HTML report and populate it with the extracted data.

Example Python Script

Below is the complete Python script that connects to the SQLite databases, extracts the data, and generates an HTML report.

import sqlite3
import os
from jinja2 import Template

class SQLiteManager:
    def __init__(self, primary_db_path, secondary_db_path):
        self.primary_db_path = primary_db_path
        self.secondary_db_path = secondary_db_path
        self.conn = None

    def connect(self):
        self.conn = sqlite3.connect(self.primary_db_path)
        self.conn.text_factory = str
        self.conn.row_factory = sqlite3.Row
        self.attach_secondary_database()

    def attach_secondary_database(self):
        attach_query = f"ATTACH DATABASE '{self.secondary_db_path}' AS metadata_db"
        self.conn.execute(attach_query)

    def execute_query(self, query, params=None):
        cursor = self.conn.cursor()
        cursor.execute(query, params or ())
        results = cursor.fetchall()
        cursor.close()
        return results

    def close(self):
        if self.conn:
            self.conn.close()

def generate_html_report(data, output_path):
    template = Template("""
    <html>
    <head>
        <style>
            table {
                width: 100%;
                border-collapse: collapse;
            }
            table, th, td {
                border: 1px solid black;
            }
            th, td {
                padding: 15px;
                text-align: left;
            }
            th {
                background-color: #f2f2f2;
            }
        </style>
    </head>
    <body>
        <h2>Dropbox Data Report</h2>
        <table>
            <tr>
                <th>ID</th>
                <th>Path</th>
                <th>Cache Size</th>
                <th>Size</th>
                <th>View Count</th>
                <th>Last Viewed Date</th>
                <th>Last Modified Date</th>
                <th>Client Modified Date</th>
            </tr>
            {% for row in data %}
            <tr>
                <td>{{ row['Z_PK'] }}</td>
                <td>{{ row['ZPATH'] }}</td>
                <td>{{ row['ZCACHESIZE'] }}</td>
                <td>{{ row['ZSIZE'] }}</td>
                <td>{{ row['ZVIEWCOUNT'] }}</td>
                <td>{{ row['ZLASTVIEWEDDATE'] }}</td>
                <td>{{ row['metadata_last_modified_date'] }}</td>
                <td>{{ row['metadata_client_mtime'] }}</td>
            </tr>
            {% endfor %}
        </table>
    </body>
    </html>
    """)

    html_content = template.render(data=data)

    with open(output_path, 'w') as f:
        f.write(html_content)

def main():
    primary_db_path = 'path/to/Dropbox.sqlite'
    secondary_db_path = 'path/to/metadata.db'
    output_path = 'path/to/output/report.html'
    
    db_manager = SQLiteManager(primary_db_path, secondary_db_path)
    db_manager.connect()

    try:
        query = """
        SELECT 
            Z_PK, 
            ZPATH, 
            ZCACHESIZE, 
            ZSIZE,
            ZVIEWCOUNT, 
            datetime(ZLASTVIEWEDDATE + 978307200, 'unixepoch', 'localtime') AS ZLASTVIEWEDDATE,
            datetime(metadata_last_modified_date, 'unixepoch', 'localtime') AS metadata_last_modified_date,
            datetime(metadata_client_mtime, 'unixepoch', 'localtime') AS metadata_client_mtime
        FROM 
            ZCACHEDFILE
        LEFT JOIN 
            metadata_db.metadata 
        ON 
            lower(metadata_db.metadata.metadata_path) = ZPATH
        """
        results = db_manager.execute_query(query)
        
        generate_html_report(results, output_path)
        print(f"Report generated successfully at {output_path}")
    finally:
        db_manager.close()

if __name__ == "__main__":
    main()

Conclusion

Generating an HTML report from Dropbox data in an iPhone full filesystem dump can be a powerful way to analyze and present your data. With Python, SQLite, and Jinja2, you can create a flexible and customizable solution tailored to your needs. This approach not only simplifies the process but also ensures that you can easily adapt the script for other types of data and reports.

Read more