Trying to delete specific cookies from google chrome

Python Help
1

I am trying to make a script that opens the cookies’ database from Google Chrome so that’s it is possible to delete some, not all, cookies. The database opens and i can see the list with cookies. I am stucked with deleting the cookie.

I am even not sure if it’s possible

import sqlite3
conn = sqlite3.connect('c:/Users/gebruiker/AppData/Local/Google/Chrome/User Data/Default/Network/cookies')
cursor  = conn.cursor()

for row in cursor.execute("select host_key from cookies"):
    #print(row)
    if "www.msn.com" in row:
        print(row)
        cursor.execute("DELETE FROM cookies WHERE id=?", row)
       
        

results = cursor.fetchall()
#print(results)
conn.close()
1 Like
2

I’ve not tried your code and it’s been some time since I last constructed any SQL statements, but if I remember correctly, you need something along the lines of…

"DELETE FROM cookies WHERE id=1"

… assuming the said cookie has that ‘id’ and the cookies table has an ‘id’ column.

edit: code correction. I had id as a text field, rather than a int

3

thanks for answering, is there a way to display the columns?

4

Using Python? I don’t know, but I don’t see why not.

Have a look at the info in this website.

I’ve not fully explored that site, but it’s on my ‘to-do’ list.

tbh, I used a SQL browser when working with SQL DBs.

edit: link update relative to the topic of this thread.

5

I would recommend you installing the sqlite3 command line interface and playing with SQL commands interactively there first. You are going to use two languages (SQL and Python) so you need to have some knowledge of both of them. Example of listing the database schema and first five host_keys:

$ sqlite3 '/mnt/c/Users/vaclav.brozik/AppData/Local/Google/Chrome/User Data/Default/Network/Cookies'
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE meta(key LONGVARCHAR NOT NULL UNIQUE PRIMARY KEY, value LONGVARCHAR);
CREATE TABLE cookies(creation_utc INTEGER NOT NULL,host_key TEXT NOT NULL,top_frame_site_key TEXT NOT NULL,name TEXT NOT NULL,value TEXT NOT NULL,encrypted_value BLOB NOT NULL,path TEXT NOT NULL,expires_utc INTEGER NOT NULL,is_secure INTEGER NOT NULL,is_httponly INTEGER NOT NULL,last_access_utc INTEGER NOT NULL,has_expires INTEGER NOT NULL,is_persistent INTEGER NOT NULL,priority INTEGER NOT NULL,samesite INTEGER NOT NULL,source_scheme INTEGER NOT NULL,source_port INTEGER NOT NULL,is_same_party INTEGER NOT NULL,last_update_utc INTEGER NOT NULL);
CREATE UNIQUE INDEX cookies_unique_index ON cookies(host_key, top_frame_site_key, name, path);
sqlite> SELECT DISTINCT host_key FROM cookies LIMIT 5 ;
.0272ac85-7522-8265-a555-397c3d825d95.prmutv.co
.0cf.io
.1dmp.io
.1rx.io
.360yield.com
sqlite>

Before the experiments terminate your Chrome (to have the database consistent) and copy the database to a new file to play with so you do not wreck you Chrome data.

Notice that in the sqlite3 CLI you need to terminate the queries using semicolon ; (not needed in the Python’s execute() method).

There are also GUI tools to play with SQL databases and queries. They may be easier to use but can be a little overwhelming at the beginning. This one works with SQLite: https://sqlitebrowser.org/

Here is an example code listing distinct host_keys in Python:

import sqlite3
import contextlib

database_file = '/mnt/c/Users/vaclav.brozik/AppData/Local/Google/Chrome/User Data/Default/Network/Cookies'

with contextlib.closing(sqlite3.connect(database_file)) as connection:
    cursor = connection.cursor()
    for (host_key,) in cursor.execute('SELECT DISTINCT host_key FROM cookies'):
        print(host_key)

The tricky part for you could be the unpacking in the for loop: for (host_key,) in …[1]
When you iterate the cursor you are getting the individual rows. Every row is a tuple consisting of the values in individual columns. Python’s way to assign the values to individual variables is unpacking. In the example it is a single value assigned to the single variable host_key.

You can extend my example by adding commands inside the with block. When the block ends the database connection is automatically closed. Do not add new SQL queries inside the for loop - see below.

Currently I see these problems in your program:

  • cursor.execute("DELETE FROM cookies WHERE id=?", row)
    You try to filter by a column id but it looks like there is no such a column. I think you want to filter (and delete) by host_key.
  • This SQL query alone will do the work, there is no need to run it repeatedly in a loop (for) or conditionally using if:
    cursor.execute("DELETE FROM cookies WHERE host_key=?", (your_host_key,))
    Notice that we create a tuple for the parameters: (your_host_key,)
  • You process the cursor results in a for loop but inside the loop you execute another query using the (same!) cursor. This replaces the original cursor’s content. You cannot execute a new query this way when you intend to continue processing the previous query’s results.
  • Changes to the SQLite database must be committed using the commit() method before close() or the end of the with block.
  • You need to ensure that Chrome is closed so that you can modify the database without possible conflicts.

  1. It is possible to write just for host_key, in but I think that the single comma could be simply overlooked. ↩︎

2 Likes
6

Another resource that could be of use for anyone interested in using a Python/SQL combo

It seems to be under active development and as such has some issues, so one needs to be a mindful of that.

7

PyMySQL is a client for MySQL. The database in Chrome is an SQLite file (different SQL database).

2 Likes
8

I’ve been doing a little hacking around and I’ve come up with this script.

It’s none too clean as it’s an amalgamation from a number of sites, together with some of my own work and as such can be improved upon, but it seems to work.

import sqlite3 as db

db_name = "file_name.db"

conn = db.connect(db_name)

c = conn.cursor()

c.execute("SELECT name FROM sqlite_master WHERE type='table'")

tables = c.fetchall() #a List of the tables
for index, table in enumerate(tables):
    table_name = table[index]
    print(f"Table {index} in {db_name} Name: {table_name}")

statement = "SELECT * FROM "+table_name
c.execute(statement)

columns = list(map(lambda x: x[0], c.description))

print(f"Columns in {table_name}: {columns}")

rows = c.fetchall()
for index, row in enumerate(rows):
    print(f"Data in row {index} {row}")

conn.close()

Note: I’ve not tried this with said chrome db; only tested with a sqlite3 db file that I’ve created.

{edit for typos made}

9

Just few suggestions to improve your code:

The suggestion I hide here is wrong as Erlend kindly notified me

It is normally preferred to use the connection as a context manager. Then you do not need to think about close(), sanitizing exceptions etc:

with db.connect(db_name) as conn:   # The context manage does not close the connection!
    ...

My old assumption was wrong as the context manager does not call close().

It is easier to use the sqlite3 client to explore a database interactively.

A cursor works as an iterator. You need fetchall() only if you want to store the resulting rows directly as they are returned:

for index, table in enumerate(c):
    ...

This looks like a bug. table should contain the table name directly. To me it looks like you are retrieving n-th letter from the table name.

This is normally a big NO NO! Please do not get used to compose SQL queries like this.

  • The parameter (in this case identifier - a table name) is not quoted, so it will fail if it contains certain characters (for example a space).
  • More importantly if the parameter can come from outside of your application, code like this is the reason of the infamous SQL injection vulnerabilities.

Examples of fixed code:

# Code for identifiers, table_name should be sanitized before that if it comes from possibly untrusted source.
c.execute(f'SELECT * FROM "{table_name}"')
# For values:
c.execute("SELECT * FROM table1 WHERE name = ?", (requested_name,))
10

Thanks.

As I commented earlier in this thread, I’d use a DB Browser for anything like this, as that wheel (so to speak) has already been invented, tried and tested. Not that one should not reinvent the wheel, as an academic coding exercise.

11

The connection context manager found in the stdlib sqlite3 extension module does not implicitly close the connection[1]. It only implicitly commits[2] if there was a pending transaction at __exit__. Also note that no transaction is implicitly opened during __enter__.

  1. ↩︎

  2. or rolls back in case exception was raised and not caught in the with block ↩︎

2 Likes
12

Thank you for the information!

The behaviour towards the transactions is what I would have expected but I did not know that __exit__ does not close the connection. I have to say that I do not understand why it is so and I feel uncomfortable with that until I understand it more :slight_smile:

I am not completely sure yet about all the practical implications. I would say that after leaving the with block:

  • The database file stays opened.
  • The database also stays locked. (when not opened read-only)
  • The with block does not create a scope so the connection object still exists after leaving it.
  • When the scope the connection variable is in ceases to exist, __del__ is called and the database is finally closed. This happens also when the scope is exited by an exception.
  • Is my description correct?
  • Is __del__ called also when the exception is not caught by the application and is left up to the Python intepreter?
  • What is the reason for not calling close at __exit__?
  • Is it good idea to create a wrapper which would call close?
13

A side from the improvements already noted, the script that I’ve posted could also be modified to use list comprehension for the columns

columns = [description[0] for description in c.description]
2 Likes
14

No. What this does (as a part of the loop) is to extract the table name so that it’s not displayed as [('table_name',)]

As with the last mod I posted, this could be a job for list comprehension.

I know the code is far from perfect, more a proof of concept.

edit-to-add: The loop was supposed to enumerate and display all the tables (in a case where there’s more than one table), but having tested this with such, it fails to do what I had in mind. I’ll work on a fix at my end, but unless I’m encouraged to do so, I’ll not post my fix, rather I’ll leave this as a kind of coding project for others to do.

So yes, it’s a bug, if the DB file has more than one table.

15

You make a lot of assumptions :slight_smile:

The database file stays opened as long as the connection object exists, and is not explicitly closed.

The sqlite3 context manager does all it can to prevent leaving the database in a locked state.
After leaving the with block there are roughly four paths for __exit__:

  1. no transaction is open; __exit__ is a no-op; no lock is held
  2. pending transaction, no exceptions raised; implicit commit that succeeds; no lock is held
  3. pending transaction, no exceptions raised; implicit commit that fails; rollback; no lock is held
  4. pending transaction, exception raised; implicit rollback; no lock is held

For 3. and 4., there might be a possibility that the rollback fails, but I don’t know what kind of scenario that could be. I tried to provoke such a scenario sometime last year, but I failed. I guess you’d have more serious problems than a locked database file in that case :slight_smile:

Note that not all queries hold the database lock.

Also note, that it is possible to open a database without locking, using the uri parameter to connect.

I’m not sure what you mean with this. I don’t think the context manager requires the (optional) target variable to cease to exist. For example, this is a common idiom in the CPython test suite:

class Tests(unittest.TestCase):
    def test_thing(self):
        with self.assertWarns(DeprecationWarning) as cm:
            some_deprecated_func()
        self.assertEqual(cm.filename, __file__)

You can also try this in your REPL:

>>> import sqlite3
>>> with sqlite3.connect(":memory:") as cx: pass
...
>>> cx  # what do you expect to see when you press enter?

No, you should not make assumtions regarding when an object is implicitly deleted. This goes for all resources, IMO, not just sqlite3 connection objects. If you want to make sure that connection cx is closed, do it explicitly by calling cx.close() or del cx. Explicit is better than implicit :wink:

__del__ is not called at all by the sqlite3 context manager; the sqlite3 context manager only calls two methods[1]: commit and rollback. It does nothing else. It does not care about the scope of the variable. It does not know about the GC. It does not know about __del__.

I have no idea :slight_smile: I would expect MAL to have a clue, though.

Depends on your use case. I have a short snippet that uses contextlib.closing to simplify resource management in the sqlite3 test suite. In my own code, I’ve normally used a single worker thread and a job queue, probably because I’m lazy. For other use cases, where multiple connections/cursors are expected, I’d use a simple contextlib wrapper (contextlib FTW).

  1. if there’s a pending transaction ↩︎

16

Thank you very much for the detailed explanation.

Yes, I am keeping this in my mind. In the past I was using the connection from connect() as a context manager thinking that it closes at __exit__ so I was just reasoning what can go wrong in those applications. I have just a small experience with SQL and I used sqlite3 just for reading queries and creating temporary views. I have never worked with transactions.

I am guessing it could be this use-case?

with contextlib.closing(sqlite3.connect(file)) as connection:
    with connection:
        ...  # do transaction1
    with connection:
        ...  # do transaction2

But I think the behaviour is really unexpected and against the Python philosophy. Earlier I quickly skimmed through your topic Deprecate the sqlite3 context manager and/or add a SAVEPOINT based context manager and originally I thought it must be a different context manager because a (single) connection cannot be used to control (multiple) transactions :smiley: If there is a chance to change the behaviour in the future I would be for the change.

I think I will try to use something like this:

with contextlib.ExitStack() as sql_stack:
    connection = sql_stack.enter_context(contextlib.closing(sqlite3.connect(file)))
    sql_stack.enter_context(connection)
    ...  # do my SQL queries

…but it is really hard to read and understand :frowning:

17

Why not just do:

def managed_connect(*args, **kwds):
    cx = sqlite3.connect(*args, **kwds)
    return contextlib.closing(cx)

with managed_connect(":memory:") as cx:
    cx.execute(...)
2 Likes
18

f.y.i: for anyone that’s interested in this; I now have a working scrip which is producing this output:

Table 1 in test.db: table1
Fields in table1: ['Field1', 'Field2']
Data in row 1: ('Text in Field 1 of Table 1', 'Text in Field 2 of Table 1')

Table 2 in test.db: table2
Fields in table2: ['Field1', 'Field2']
Data in row 1: ('Text in Field 1 of Table 2', 'Text in Field 2 of Table 2')

It’s still not perfect and is really just a coding exercise for me, but it could come in handy.

@famko We kinda hijacked your thread here, but the information provided by the OPs is not only relevant, but also very insightful, from which a good deal can be learned.

19

Yes, sorry, we got a little carried away.

20

You are right, I missed one level of iteration :slight_smile: A direct fix should be:
a) table_name = table[0]
b) (table_name,) = table
The b) solution has advantage that it fails when table has unexpected number of items.

I was productive with mistakes :slight_smile: The following one is wrong too. Placeholders like ? can be used to replace values, not identifiers!

Fixed (and going to fix my original post):

c.execute(f'SELECT * FROM "{table_name}"')
2 Likes