Fetch and Search Your Chrome History

• Download BrowsingHistory_POC_Exe - 326.8 KB

How to View Your Chrome History

First, let's see what is the "normal" way to view your Chrome's history. Just go to History menu option, as shown below:

You will then see all recently visited web sites, last -> first.

Chrome Forensics

Google Chrome stores its browsing history as a SQLite database named "history", which is located in various locations, based on the OS. In Windows, the location is in the %APPDATA% folder.

There is also Archived History that predates information in the History file. Note that the Archived History only contains visits.

SQL Queries

For the purpose of fetching recent (or all) history, or alternatively, performing searches on the history database, I have prepared several SQL queries:

Fetch All History

SELECT last_visit_time, datetime(last_visit_time / 1000000 - 11644473600, _
'unixepoch', 'localtime'),url,title FROM urls ORDER BY last_visit_time DESC

so the code will be:

#define CHROME_HISTORY_SQL_QUERY "SELECT last_visit_time, 
datetime(last_visit_time / 1000000 - 11644473600, 'unixepoch', 'localtime'),
url,title FROM urls ORDER BY last_visit_time DESC"

Fetch history based on a search term

In this case, we would like to allow a search term and to make it easy, we assume that if the search term is "child", we will search for "*child*" which is in SQL syntax "%child%". So the code will be 2 preprocessor definitions:

#define CHROME_HISTORY_SQL_QUERY2_LEFT "SELECT last_visit_time, 
    datetime(last_visit_time / 1000000 - 11644473600, 'unixepoch', 'localtime'),
    url,title FROM urls WHERE title like '"

#define CHROME_HISTORY_SQL_QUERY2_RIGHT "' ORDER BY last_visit_time DESC"

Fetch history based on a complex query

When we want to add a query term, such as "Where last_visit_time > <date>", we use the following code:

#define CHROME_HISTORY_SQL_QUERY_LEFT "SELECT last_visit_time, 
  datetime(last_visit_time / 1000000 - 11644473600, 'unixepoch', 'localtime'),
  url,title FROM urls "

#define CHROME_HISTORY_SQL_QUERY_RIGHT " ORDER BY last_visit_time DESC"

Data Structure

First, we have defined a generic data structure for browsing history, which should support all browsers, and will be demonstrated in this article within the scope of Google Chrome:

typedef struct _SGBrowserHistory
{
    int Browser;  // 1 = chrome, 2 = firefox, 3 = ie
    WCHAR SiteURL[URL_MAXSIZE];
    WCHAR SiteName[1024];
    WCHAR LastVisitDate[256];
} SGBrowserHistory;

It is also very useful to take advantage of the CSimpleArray class. We define a new type SGBrowserHistoryArray which will be used to store a dynamic array containing elements of the SGBrowserHistory data structure. So we have:

• SGBrowserHistory, and
• SGBrowserHistoryArray

typedef CSimpleArray<SGBrowserHistory> SGBrowserHistoryArray;

Our Program

I have created a small program that uses a class library we developed for several products of ours.

Now we can dive into the code:

The simplest way of using our class is to just fetch all stored history. That can take time and we don't want to block or be blocked while Chrome is used, (and when it is used, its database is accessed and altered), so first was create our own backup database and call it temp.db. (define as TEMP_DB_NAME).

bool SGBrowsingHistory::GetChromeHistory
    (SGBrowserHistoryArray *history, CString FileName,CString SearchString)
{
    bool result = false;
    WCHAR szPath[MAX_PATH];
    //const char *lpTail;
    utils::SG_GetSpecialFolderPath(szPath, CSIDL_LOCAL_APPDATA);
    StrCat(szPath, CHROMEHISTORY);

    WCHAR filename[MAX_PATH + 1] = { TEMP_DB_NAME };

    if (CopyFile(szPath, filename, FALSE))
    {
        if (GetFileAttributes(filename) != 0xFFFFFFFF)
        {
        }
        else
        {
            wprintf(L"Error: Cannot find login data for Google Chrome browser\r\n");
        }
        bool result = db->GetChromeHistoryRecords(filename, history, SearchString);
    }

    return result;
}

Storing Results in Our Own Database

Our program not only interfaces with Chrome's database but creates our very own database where we can store the data as we need and see fit.

The SG_Database Class

I have created a class for handing databases, mostly I would use sqlite which I think is the best. I also use the CppSQLite3U by Tyushkov Nikolay which is a wrapper class for more easier support for UNICODE.

Creating the ChromeHistory Table

In order to create the ChromeHistory table, we use a preprocessor definition that goes as follows:

#define createTableChromeHistory L"CREATE TABLE IF NOT EXISTS ChromeHistory(\
id INTEGER PRIMARY KEY AUTOINCREMENT,\
created TIMESTAMP not null default CURRENT_TIMESTAMP,\
date_last_visited TEXT not null default '',\
site_name TEXT not null default '',\
size_url TEXT not null default '',\
);"

Logging Errors

I have written a lot about that subject in previous articles. In this case, here is a very simple function for logging SQL errors.

#define STR_ERROR_RUNNING_QEURY L"Error running query: %s. Error message: %s. Function = %s\n"

void CSGDatabase::LogSqlError(CppSQLite3Exception &e, CString Function, CString sql)
{
    wprintf(STR_ERROR_RUNNING_QEURY, sql.GetBuffer(), e.errorMessage(), Function.GetBuffer());
}

Then we create a Macro:

#define LOGSQLERROR(e,sql) LogSqlError(e, (CString)__FUNCTION__, sql);

for calling it already with the current function from which LogSqlError has been invoked.

LOGSQLERROR(e,sql);

Additional Information About the Chrome's Database

How Dates and Times Are Stored

The History file uses the different timestamps.

visits.visit_time

Chrome stores the date of each visit at a given web site in the visits.visit_time. The visits.visit_time is in (the number of) microseconds since January 1, 1601 UTC.

Here is a function for converting Chrome time to System time.

// convert chrome time (as long long integer) to system time
SYSTEMTIME intChromeTimeToSysTime(long long int UnixTime)
{
    time_t dosTime(UnixTime);
    // Note that LONGLONG is a 64-bit value
    SYSTEMTIME systemTime;


    LARGE_INTEGER utcFT = { 0 };
    utcFT.QuadPart = ((unsigned __int64)dosTime) * 10000000;

    FileTimeToSystemTime((FILETIME*)&utcFT, &systemTime);
    return systemTime;
}

Note that this timestamp is not the same as a Windows FILETIME which is (the number of) 100 nanoseconds since January 1, 1601 UTC.

Timestamps

The History file uses the different timestamps.

visits.visit_time

The visits.visit_time is in (the number of) microseconds since January 1, 1601 UTC.

The following code demonstrates the proper way for conversion of the Chrome Visit Time into a human readable format:

date_string = datetime.datetime( 1601, 1, 1 )
            + datetime.timedelta( microseconds=timestamp )

Note that this timestamp is not the same as a Windows FILETIME which is (the number of) 100 nanoseconds since January 1, 1601 UTC.

downloads.start_time

The downloads.start_time is in (the number of) seconds since January 1, 1970 UTC.

Further Reading

• About the CSimpleArray class
• Sqlite official web page

History

• 14^th June, 2019: Initial version