Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

DBI DBD::SQLite unable to open Firefox cookies.sqlite database

by Discipulus (Abbot)
on Nov 29, 2022 at 11:39 UTC ( #11148433=perlquestion: print w/replies, xml ) Need Help??

Discipulus has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

inspired by Using LWP (or some other module) to Dowload HTML with Cookie Session ID I tried to unlock the cookies case of Firefox using perl. I have installed DBD::SQLite but the following code fails to open the DB even if Firefox is closed or if try the readonly flag.

I also tried to make a copy of the DB cookiesTEST.sqlite in the case some lock was there.. but nothing but failures.

use strict; use warnings; use Data::Dumper; use DBI; use DBD::SQLite; my $db = $ENV{AppData}.'\Mozilla\Firefox\Profiles\nwk2oixj.default-rel +ease\cookiesTEST.sqlite'; die "DB file not found!" unless -e $db; print "sqlite DBD version: $DBD::SQLite::sqlite_version\n"; my $dbh = DBI->connect(qq(dbi:SQLite:dbname=$db,'','',{ sqlite_open_flags => SQLITE_OPEN_READONLY, PrintError => 1, RaiseError => 1, })) or die $DBI::errstr; my $sth = $dbh->prepare(q(SELECT * FROM moz_cookies )); print Dumper $sth->fetchall_arrayref({}); __END__ sqlite DBD version: 3.39.4 DBI connect('dbname=C:\Users\ME\AppData\Roaming\Mozilla\Firefox\Profil +es\nwk2oixj.default-release\cookiesTEST.sqlite,'','',{ sqlite_open_flags => SQLITE_OPEN_REA +DONLY, PrintError => 1, RaiseError => 1, }','',...) failed: unable to open dat +abase file at firefox-cookies.pl line 11. unable to open database file at firefox-cookies.pl line 11.

With FF open both DBeaver and sqlite3 command line utility can access the DB without problem. DBeaver uses jdbc:sqlite driver and sqlite> .version shows SQLite 3.40.0 2022-11-16 12:10:08 so not the 3.39.4 shown by DBD.

I have no sqlite in the path.

Searching I found HTTP::Cookies::Mozilla and I installed it: it comes with a stringy documentation ( yes! adopt it ;) but, lurking in /examples I have found /examples/convert-to-mojo.pl that slightly modified to work on windows (around line 19 my @cookies_files = glob(     $ENV{AppData}.'\Mozilla\Firefox\Profiles\*\cookies.sqlite'); ) accesses the db without any issue.

The module documentation ( scan method not even mentioned ;) says explicitly:

> .. so you will need to have either DBI/DBD::SQLite, or the sqlite3 executable somewhere in the path.

Since I have not sqlite in the path, it should use the very same driver used by my failing script, ie: DBD::SQLite

So why the above code fails?

L*

PS ..and now crack the Chrome cookies jar

There are no rules, there are no thumbs..
Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.

Replies are listed 'Best First'.
Re: DBI DBD::SQLite unable to open Firefox cookies.sqlite database
by Corion (Patriarch) on Nov 29, 2022 at 11:59 UTC

    This seems to be a simple syntax error. The DSN string is too long:

    qq(dbi:SQLite:dbname=$db,'','',{ ...

    The qq() extends beyond the first comma, which is not what you want. You want:

    qq(dbi:SQLite:dbname=$db),'','',{ ...

    Usually, in such situations, I try to separate the steps:

    my $dsn = "qq(dbi:SQLite:dbname=$db)"; my $user; my $password; my %dbi_options = ( RaiseError => 1, PrintError => 1, sqlite_open_flags => SQLITE_OPEN_READONLY, ); my $dbh = DBI->connect($dsn, $user, $password, \%dbi_options);
      Ah ah ah Corion what a shame from my part :)

      I modified the code but it fails anyway with empty var (both cookiesTEST.sqlite and cookies.sqlite tested) so it access the DB but still fails reading it

      use strict; use warnings; use Data::Dumper; use DBI; use DBD::SQLite; my $db = $ENV{AppData}.'\Mozilla\Firefox\Profiles\nwk2oixj.default-rel +ease\cookiesTEST.sqlite'; die "DB file not found!" unless -e $db; print "sqlite DBD version: $DBD::SQLite::sqlite_version\n"; my $dsn = qq(dbi:SQLite:dbname=$db); my $user; my $password; my %dbi_options = ( RaiseError => 1, PrintError => 1, sqlite_open_flags => 'SQLITE_OPEN_READONLY', ); my $dbh = DBI->connect($dsn, $user, $password, \%dbi_options); my $sth = $dbh->prepare(q(SELECT * FROM moz_cookies )); $sth->execute(); # PS added this and it works :) print Dumper $sth->fetchall_arrayref({}); __END__ sqlite DBD version: 3.39.4 $VAR1 = [];

      L*

      PS ..obviously executing the statement helps :) thanks 1nickt++

      There are no rules, there are no thumbs..
      Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
Re: DBI DBD::SQLite unable to open Firefox cookies.sqlite database
by Corion (Patriarch) on Nov 29, 2022 at 12:31 UTC

    The open manpage of SQLite also shows the ?immutable parameter when using the uri= form of connecting to a DB. With that, I am able to read the Firefox cookies from a live SQLite database while Firefox is running.

    use strict; use warnings; use Data::Dumper; use DBI; use DBD::SQLite; my $mozilla_path = "$ENV{AppData}\\Mozilla\\Firefox\\Profiles"; opendir my $dh, $mozilla_path or die "Couldn't read Mozilla profiles directory $mozilla_path: $! +"; my ($profile) = grep { /\.default/ } readdir $dh; my $file = "$mozilla_path/$profile/cookies.sqlite"; $file =~ s!\\!/!g; die "DB file '$file' not found!" unless -e $file; print "sqlite DBD version: $DBD::SQLite::sqlite_version\n"; my $dsn = "dbi:SQLite:uri=file:///$file?immutable=1"; my $user; my $password; my %dbi_options = ( RaiseError => 1, PrintError => 1, sqlite_open_flags => 'SQLITE_OPEN_READONLY', ); my $dbh = DBI->connect($dsn, $user, $password, \%dbi_options); my $sth = $dbh->prepare(q(SELECT * FROM moz_cookies )); $sth->execute; print Dumper $sth->fetchall_arrayref({});

    The URI needs to be different between Windows and Unixish path specifications, but the SQLite documentation has examples for all of them.

      Hello Corion,

      just to mention all combinations, I can read the live cookies.sqlite DB even commenting out the sqlite_open_flags => 'SQLITE_OPEN_READONLY'

      L*

      There are no rules, there are no thumbs..
      Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
Re: DBI DBD::SQLite unable to open Firefox cookies.sqlite database
by 1nickt (Canon) on Nov 29, 2022 at 12:27 UTC

    Hi Discipulus,

    Don't forget $sth->execute; ...

    Hope this helps!


    The way forward always starts with a minimal test.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11148433]
Approved by marto
Front-paged by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (3)
As of 2023-02-04 02:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer not to run the latest version of Perl because:







    Results (30 votes). Check out past polls.

    Notices?