Custom web proxies and Power Query – decrypting Google Chrome cookies

It turns out that decrypting Google Chrome cookies is a non-trivial process, so I thought I’d use it as an introduction to the use of custom web proxies.

There are functions available in the .NET framework that allow you to decrypt Google Chrome cookies, however, they are not directly accessible via Power Query (as far as I can tell) and even with VBA you would need to write a COM wrapper.

Instead, I decided to write a very short script in Python which would decrypt the cookies and send the result back to Excel/Power Query.  How to do that?  Well, by calling the running Python script from a Power Query Web contents query.

The solution below has two parts, a Python web server and Power Query.  Power Query imports the Google cookies database.  It then executes a Web query to contact the Python web server and send it a list of encrypted cookies.  The Python script receives the encrypted cookies, decrypts them and send back the result.  Power Query takes the decrypted cookies and combines it with the other data it has for the cookies.

To make this work, you need to have Python (both 2.x and 3.x should work) installed.  You’ll also need the Python extensions for Windows (for the win32crypt library) and the bottle web framework.

I won’t cover the installation of these here, but happy to assist if you run into trouble.

So basically, this short script starts a tiny local web server.

Assuming you run it from your desktop PC it will respond to requests sent to http://localhost:8080/decryptCookie

And if we send it a list of encrypted cookies, it will decrypt them using Microsoft’s libraries and send back the decrypted cookies.

For the purposes of this exercise, we will manually launch it, but we could trigger it in a more sophisticated fashion directly from Excel.

The M code on the Power Query side of things, builds on the post from yesterday which showed how to access the Google Chrome cookie SQLite database.

I did run into a strange issue where the query I put together yesterday was truncating the encrypted cookie field from the cookies database.   For some reason, this was corrected by adding a SQL query – select * from cookies rather than simply connecting to the table.  I would have expected this to produce identical results but it didn’t.

And here is the annotated M query:

Happy to walkthrough this in more detail if necessary or respond to questions.

This data can now be used much like the “harvested” Internet Explorer cookies from other posts.

Bear in mind, that there are countless other uses for custom Web proxies with Power Query. For example, you can use them to perform more complex web authentication, web scraping with BeautifulSoup (no more crazy HTML/XML guesswork), data manipulation using Python libraries such as pandas, execute other applications on your computer,  etc. etc.

Of course, on the flipside, you need to have Python installed and have the Python script running before it will work.  On your home PC, this probably doesn’t matter.  In a corporate environment, you would want this to be hosted on a proper web server.

How to access an SQLite database from Power Query

SQLite is an increasingly common database format for both mobile applications and small desktop applications.  Many applications on iOS/Android use them and Google/Mozilla use them for their browsers.

Reading an SQLite database using Power Query isn’t difficult but does require a few initial steps.

To begin with, you need to install an ODBC SQLite3 driver.

I recommend using the one located here:

http://www.ch-werner.de/sqliteodbc/

 

2016-02-01 11_47_35-SQLite ODBC Driver - Internet Explorer

Make sure that you install the correct version – either 32 bit or 64 bit.

It would also be a good idea to close Excel and re-open before going any further.

Now we create an ODBC query, by selecting From Other Sources->From ODBC:

2016-02-01 11_37_38-chrome cookies.xlsx - Excel

The next step is to specify a connection string.

Here’s an example that will read the Google Chrome Cookies database (assuming you have Chrome installed and specify your username):

DRIVER=SQLite3 ODBC Driver;Database=C:\Users\<your username>\AppData\Local\Google\Chrome\User Data\Default\Cookies;LongNames=0;Timeout=1000;NoTXN=0;

You will then get asked what credentials to use.  In our case, we just want the default of no credentials:

2016-02-01 11_43_28-

You will then see a dialog box asking you to select a table.  For our example, select the “cookies” table

2016-02-01 11_56_42-

and – hopefully – you will see a table containing all the cookies from Google Chrome:2016-02-01 12_14_49-cookies - Query Editor

You will note that most of them are encrypted and therefore not immediately available for us to use.

That’s a problem for another day :)

Although this example used the Google Chrome Cookies sqlite3 database, this approach should work with minimal change for most sqlite3 databases.