Web Browser Forensics with Python

Below is an example of how web browsing history can be retrieved from a Google Chrome profile and exported to CSV file using Python. The browsing history is stored in an SQLite database called 'History', with no file extension.

Firstly, the export file path and name are set, and a check is made to see if the path exists. Similarly, the path to the 'History' database is set and a check is made to see if this is correct. A connection to the database is then established and a query is specified and executed. If data has been returned, the export file is either cleared of contents or created if it does not exist. The header row is then constructed and added to the file, followed by the data rows. A try-except-finally block is used to catch any errors that may arise, as well as close the database connection, regardless of whether the data was successfully retrieved or not.

The browsing history is taken from two tables in the database, 'urls' and 'visits'. As the name suggests, the 'urls' table contains the URL and associated title, along with other information, whilst the 'visits' table contains the details of each visit, including the date, time and duration. This information needs formatting as they are stored as timestamps.

from datetime import datetime
from pathlib import Path
import os.path
import re
import sqlite3

# Export file path and name.
exportPath = 'c:\\demo\\'
fileHistory = 'chrome-history.csv'

# Check if the export path exists.
if not os.path.isdir(exportPath):

    # Message stating export path does not exist.
    print("Export path does not exist.")

    # Stop program execution.
    quit()

# Browsing history database and database connection.
dbHistory = 'C:\\path\\to\\profile\\\\History'
connectHistory = None

# Check if the browsing history database file exists.
if not os.path.isfile(dbHistory):

    # Message confirming incorrect database location.
    print("Error locating browsing history database.")

    # Stop program execution.
    quit()

try:

    # Connect to database.
    connectHistory = sqlite3.connect(dbHistory)

    # Cursor to execute query.
    cursorHistory = connectHistory.cursor()

    # SQL to select browsing history information.
    sqlSelectHistory = \
        "SELECT DISTINCT \
             u.url, u.title, \
             datetime(v.visit_time/1000000 + (strftime('%s', '1601-01-01')), \
                 'unixepoch', 'localtime') visit_date_time, \
             (v.visit_duration / 3600 / 1000000) || ' hours ' || \
                 strftime('%M minutes %S seconds', v.visit_duration / 1000000 / 86400.0) AS duration \
         FROM urls u \
         INNER JOIN visits v ON u.id = v.url \
         ORDER BY v.visit_time"

    # Execute query.
    cursorHistory.execute(sqlSelectHistory)

    # Fetch the data returned.
    resultsHistory = cursorHistory.fetchall()

    # Export browsing history information if there is any.
    if len(resultsHistory) > 0:

        # If export file exists, clear its contents.
        if os.path.isfile(f"{exportPath}{fileHistory}"):

            with open(f"{exportPath}{fileHistory}", 'w') as file:
                pass

        # Create the file.
        else:

            filePath = Path(f"{exportPath}{fileHistory}")
            filePath.touch(exist_ok=True)

        # Extract the query headers.
        headers = [i[0] for i in cursorHistory.description]

        # Join headers in a string separated by commas.
        headerRow = ",".join(f'"{item}"' for item in headers) + "\n"

        # Output browsing history information to the CSV file.
        with open(f"{exportPath}{fileHistory}", 'a') as file:

            # Write the headers to the CSV file.
            file.write(headerRow)

            # Write the rows of data to the CSV file.
            for rowHistory in resultsHistory:

                # Clean the title of non-printable characters.
                cleanTitle = re.sub(r'[\x00-\x1f\x7f\u200e]', '', rowHistory[1])

                # Add each piece of data from the row to the file.
                file.write("\"" + f"{rowHistory[0]}" + "\",")
                file.write("\"" + f"{cleanTitle}" + "\",")
                file.write("\"" + f"{rowHistory[2]}" + "\",")
                file.write("\"" + f"{rowHistory[3]}" + "\"\n")

        # Message stating Chrome browsing history export successful.
        print("Chrome browsing history export successful.")

    else:

        print("No Chrome browsing history to export.")

except sqlite3.DatabaseError as e:

    # Confirm error exporting Chrome browsing history information and quit.
    print("Error exporting Chrome browsing history information.")
    quit()

finally:

    # Close history database connection.
    connectHistory.close()

Below is an example of how web browsing history and cookie information can be retrieved from a Mozilla Firefox profile using Python. Both the browsing history and cookie information is stored in SQLite databases called 'places.sqlite' and 'cookies.sqlite' respectively.

Firstly, the file path and name are set for the browsing history export, and a check is made to see if the path exists. Similarly, a path to the 'places' database is set and a check is made to see if this is correct. A connection to the database is then established and a query is specified and executed. If data has been returned, the export file is either cleared of contents or created if it does not exist. The header row is then constructed and added to the file, followed by the data rows. A try-except-finally block is used to catch any errors that may arise, as well as close the database connection, regardless of whether the data was successfully retrieved or not.

The 'moz_places' table in the 'places.sqlite' database contains information relating to the URLs visited, including the URL itself, the title associated with the URL, along with other related details. Both the URL and the title are included in the output to the CSV file. The 'moz_historyvisits' table in the same database includes details about each visit to the URL, including the date and time of the visit, which again is included in the output to the CSV file. It should be noted that the date and time is stored as a Unix timestamp, so needs to be converted in order to be readable.

The details regarding cookies are retrieved and exported in a similar fashion. It resides in the 'moz_cookies' table in the 'cookies.sqlite' database. The host, name, and value are exported, along with the date and time the cookie was created and when it was last accessed. Again, the dates and times are Unix timestamps, so need to be converted before they are readable.

from datetime import datetime
from pathlib import Path
import os.path
import re
import sqlite3

# Export file path and name.
exportPath = 'c:\\demo\\'
fileHistory = 'firefox-history.csv'

# Check if the export path exists.
if not os.path.isdir(exportPath):

    # Message stating export path does not exist.
    print("Export path does not exist.")

    # Stop program execution.
    quit()

# Browsing history database and database connection.
dbHistory = 'C:\\path\\to\\profile\\places.sqlite'
connectHistory = None

# Check if the browsing history database file exists.
if not os.path.isfile(dbHistory):

    # Message confirming incorrect database location.
    print("Error locating browsing history database.")

    # Stop program execution.
    quit()

try:

    # Connect to database.
    connectHistory = sqlite3.connect(dbHistory)

    # Cursor to execute query.
    cursorHistory = connectHistory.cursor()

    # SQL to select browsing history information.
    sqlSelectHistory = \
        "SELECT DISTINCT \
            mp.url, mp.title, \
            datetime(mhv.visit_date/1000000, 'unixepoch') visit_date_time \
         FROM moz_places mp \
         INNER JOIN moz_historyvisits mhv ON mp.id = mhv.place_id \
         WHERE mp.visit_count > 0 \
         ORDER BY mhv.visit_date"

    # Execute query.
    cursorHistory.execute(sqlSelectHistory)

    # Fetch the data returned.
    resultsHistory = cursorHistory.fetchall()

    # Export browsing history if there is any.
    if len(resultsHistory) > 0:

        # If export file exists, clear its contents.
        if os.path.isfile(f"{exportPath}{fileHistory}"):

            with open(f"{exportPath}{fileHistory}", 'w') as file:
                pass

        # Create the file.
        else:

            filePathHistory = Path(f"{exportPath}{fileHistory}")
            filePathHistory.touch(exist_ok=True)

        # Extract the query headers.
        headersHistory = [i[0] for i in cursorHistory.description]

        # Join headers in a string separated by commas.
        headerHistoryRow = ",".join(f'"{item}"' for item in headersHistory) + "\n"

        # Output browsing history information to the CSV file.
        with open(f"{exportPath}{fileHistory}", 'a') as file:

            # Write the headers to the CSV file.
            file.write(headerHistoryRow)

            # Write the rows of data to the CSV file.
            for rowHistory in resultsHistory:

                # Clean the title of non-printable characters.
                cleanTitle = re.sub(r'[\x00-\x1f\x7f\u200e]', '', str(rowHistory[1]))

                # Add each piece of data from the row to the file.
                file.write("\"" + f"{rowHistory[0]}" + "\",")
                file.write("\"" + f"{cleanTitle}" + "\",")
                file.write("\"" + f"{rowHistory[2]}" + "\"\n")

        # Message stating Firefox browsing history export successful.
        print("Firefox browsing history export successful.")

    else:

        print("No Firefox browsing history to export.")

except sqlite3.DatabaseError as e:

    # Confirm error exporting Firefox browsing history information and quit.
    print("Error exporting Firefox browsing history information.")
    quit()

finally:

    # Close the browsing history database connection.
    connectHistory.close()

# Export file name.
fileCookies = 'firefox-cookies.csv'

# Cookies database and database connection.
dbCookies = 'C:\\path\\to\\profile\\cookies.sqlite'
connectCookies = None

# Check if the cookies database file exists.
if not os.path.isfile(dbCookies):

    # Message confirming incorrect database location.
    print("Error locating cookies database.")

    # Stop program execution.
    quit()

try:

    # Connect to database.
    connectCookies = sqlite3.connect(dbCookies)

    # Cursor to execute query.
    cursorCookies = connectCookies.cursor()

    # SQL to select cookie information.
    sqlSelectCookies = \
        "SELECT DISTINCT \
            host, name, value, \
            datetime(creationTime/1000000, 'unixepoch') created_date_time, \
            datetime(lastAccessed/1000000, 'unixepoch') last_accessed_date_time \
         FROM moz_cookies"

    # Execute query.
    cursorCookies.execute(sqlSelectCookies)

    # Fetch the data returned.
    resultsCookies = cursorCookies.fetchall()

    # Export cookie information if there is any.
    if len(resultsCookies) > 0:

        # If export file exists, clear its contents.
        if os.path.isfile(f"{exportPath}{fileCookies}"):

            with open(f"{exportPath}{fileCookies}", 'w') as file:
                pass

        # Create the file.
        else:

            filePathCookies = Path(f"{exportPath}{fileCookies}")
            filePathCookies.touch(exist_ok=True)

        # Extract the query headers.
        headersCookies = [i[0] for i in cursorCookies.description]

        # Join headers in a string separated by commas.
        headerCookiesRow = ",".join(f'"{item}"' for item in headersCookies) + "\n"

        # Output cookie information to the CSV file.
        with open(f"{exportPath}{fileCookies}", 'a') as file:

            # Write the headers to the CSV file.
            file.write(headerCookiesRow)

            # Write the rows of data to the CSV file.
            for rowCookies in resultsCookies:

                # Add each piece of data from the row to the file.
                file.write("\"" + f"{rowCookies[0]}" + "\",")
                file.write("\"" + f"{rowCookies[1]}" + "\",")
                file.write("\"" + f"{rowCookies[2]}" + "\",")
                file.write("\"" + f"{rowCookies[3]}" + "\",")
                file.write("\"" + f"{rowCookies[4]}" + "\"\n")

        # Message stating Firefox cookie information export successful.
        print("Firefox cookie information export successful.")

    else:

        print("No Firefox cookie information to export.")

except sqlite3.DatabaseError as e:

    # Confirm error retrieving Firefox cookie information and quit.
    print("Error retrieving Firefox cookie information.")
    quit()

finally:

    # Close cookies database connection.
    connectCookies.close()