Bits from Bill

Technology thoughts leaking from the brain of "Bill Pytlovany"

Friday, September 25, 2009

SQLite C Code to Read Cookies

While most of the technology leaking from my brain is for a wide audience, today’s post is very technical in nature. I expected the information below to be available online but since it wasn’t I felt obligated to share my discovery with others.

One of the features of my WinPatrol program is to help manage and remove unwanted internet cookies. Starting with Firefox3 I wasn’t able to read cookies because of the change from a cookies.txt to a SQLite database format.  Luckily, the code to access to access SQLite files is public domain and freely accessible. I was hesitant to include the code to access SQLite files fearing it would increase the small size of WinPatrol.exe.  Since SQLite is completely public domain I was able to strip it down to include only those functions I needed.

The next step was figure out the format used by Firefox and find some sample code for accessing SQLite in C/C++. This wasn’t as easy as I had expected but by piecing together little bits of knowledge here and there I was eventually successful. Apparently, not many programmers are still using C/C++ but if you do here’s an example of how to read cookie information stored locally by both Firefox 3.x and Google’s Chrome browsers.

The following is pretty much the identical code used by the soon to be released, WinPatrol 17 to read cookie data used by Firefox3 and Chrome. My actual code includes a little more error checking and validation. If you’re using a different programming language you’ll still want to note the column or field names used in the SQLite table.

There are two based functions that you’ll need.

//  Firefox3 uses the table name of "moz_cookies"
//  Chrome uses table name of just "cookies"
//  This routine opens up the cookies table and passes a callback routine
//  send requested cookies, one row at a time.
// IN: the fullpath to the cookie file name
// IN: the defined type of if it’s a Chrome or Firefox3 type
int    GetSQLiteCookies( LPSTR szCookieFile, int iType )
{
    char       *zErrMsg = 0;
    int         rc;
    sqlite3    *dbCookie;   // defined in sqlite.h
    TCHAR      szSQLiteExec[MAX_SQLITEEXE]; 

    rc = sqlite3_open(szCookieFile, &dbCookie);
    if( rc )
    {
        sqlite3_close(dbCookie);
        return 0;
    }

    // Create SQL statement depending on the browser 
    if(iType == COOKIE_FIREFOX)
          StringCbCopy(szSQLite ,MAX_SQLEXEC, TEXT( "SELECT * FROM moz_cookies") );
    else    // then it much be Chrome
          StringCbCopy(szSQLite ,MAX_SQLEXEC, TEXT( "SELECT * FROM cookies") );

    rc = sqlite3_exec(dbCookie, szSqLiteExec, CookieCallback, 0, &zErrMsg); 
    if( rc!=SQLITE_OK ) 
    {
        sqlite3_free(zErrMsg); 
    }
    sqlite3_close(dbCookie);

    return 1;
}

The next routine is a “Callback” routine.  The result of calling the sqlite3_exe is above is that SQLite will call our Callback routine for every cookie or “row” in the database.  Each call will contain information about each cookie in a array.

// IN iCount = number of columns in this row
// IN szColValue = The data in the column
// IN szColName = Name of column
static int CookieCallback(void *NotUsed, int iCount, char **szColValue, char **szColName)
{
    int    i;
    char    *zErrMsg = 0; 
    COOKIEINFO   ciCookie;  // WinPatrol specific cookie structure
    SYSTEMTIME        myTime;
    memset(&ciCookie, 0, sizeof(COOKIEINFO));

// I’m going to go through each column and check to see
// see if the name matches one of the cookie fields and if
// it matches we grab the value and store it in our cookie
// structure. The actual code is a little more optimize but I’ve broken
// out each test to make it a little clearer.
// Some column names are different firefox vs chrome

  for(i=0; i<iCount; i++)
  {
      if(!lstrcmpi(TEXT("name"), szColName[i] ))
          StringCbCopy(ciCookie.szName,MAX_COOKIE_NAME, szColValue[i] );

      if(!lstrcmpi(TEXT("value"), szColName[i] ))
          StringCbCopy(ciCookie.szValue,MAX_COOKIE_VALUE, szColValue[i] );

      if(!lstrcmpi(TEXT("path"), szColName[i] ))
          StringCbCopy(ciCookie.szPath,MAX_PATH, szColValue[i] );

      if(!lstrcmpi(TEXT("host"), szColName[i] ))  // firefox3
          StringCbCopy(ciCookie.szDomain, MAX_COOKIE_NAME, szColValue[i] );
      if(!lstrcmpi(TEXT("host_key"), szColName[i] ))   // chrome
          StringCbCopy(ciCookie.szDomain, MAX_COOKIE_NAME, szColValue[i] );

      // firefox3 uses expiry, chrome uses expires_utc
      if ( (!lstrcmpi(TEXT("expiry"), szColName[i] )) || (!lstrcmpi(TEXT("expires_utc"), szColName[i] )))
      {
            long lUnixTime;
            lUnixTime = atol( szColValue[i] );
            UnixTimeToSystemTime((time_t)lUnixTime, &myTime);
            GetLocalTimeString(&myTime, ciCookie.szDate);
      }

      if(!lstrcmpi(TEXT("isSecure"), szColName[i] ))    //firefox 3 field name.
                ciCookie.bSecure = (BOOL)szColValue[i];
      if(!lstrcmpi(TEXT("secure"), szColName[i] ))        // chrome field name
                ciCookie.bSecure = (BOOL)szColValue[i];

  }

// Here's where I call another routine which adds my cookie structure
// to a list of cookies so I can display them in WinPatrol.
// your method of storing each cookie will vary.

  return 0;
}

 

I’m now a big fan so if you’re interested in using SQLite you can find everything you need to get started at http://www.sqlite.org/. I didn’t have time to get a book but for more C/C++ code I recommend The Definitive Guide to SQLite

Share on Facebook


5 Comments:

Anonymous Anonymous said...

Funny you should mention support for FF3.x and cookies.....I was just getting ready to rattle your cage and ask about it's progress. That's great news (SQL) and look forward to WP17, which will be released............?

3:00 PM  
Blogger Jonathan Arnold said...

Yeah, I'm a big SQLite fan myself.

One C/C++ shortcut I'm always fond of passing out - this code:

COOKIEINFO ciCookie; // WinPatrol specific cookie structure
memset(&ciCookie, 0, sizeof(COOKIEINFO));

can be replaced by the 100% ANSI standard:

COOKIEINFO ciCookie={0}; // WinPatrol specific cookie structure

and is just safer IMHO.

9:03 AM  
Blogger Aaron aka Frank said...

I'm a huge SQLite fan - to the point that I write almost all of my web applications for a SQLite database. Great news if you're on a memory-tight VPS - nginx and PHP in FastCGI mode using SQLite as the database uses (along with the system itself) about 40 megabytes compared to 192mb for Apache, mod_php5 and MySQL.

6:31 PM  
Anonymous Anonymous said...

It works good with chrome, but in FF i am not able to open cookies.sqlite while FF is running. Any idea how to solve this problem.

1:55 AM  
Blogger Unknown said...

Yea, Firefox locks the file when it's running to prevent others from making changes that it might remove when it closes the file.

WinPatrol keeps track of your requested changes and we detect as soon as FF closes so we can update changes at that time.

Bill

11:01 AM  

Post a Comment

<< Home