This article explains how to fetch the browsing history of the most popular browser, Chrome.
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; 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];
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.
SYSTEMTIME intChromeTimeToSysTime(long long int UnixTime)
{
time_t dosTime(UnixTime);
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
History
- 14th June, 2019: Initial version