Is it possible to dump a sqlite db that has an associated -wal file?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to dump a sqlite db that has an associated -wal file?

Peng Yu
Hi,

I am trying to see what tables are available in sqlite_master from
firefox cookies sqlite3 fire.

~/Library/Application
Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

But the error message says "Error: database is locked".

I see a cookies.sqlite-wal file in the same directory.

Is there a way to inspect this db file without closing firefox?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Keith Medcalf

On Saturday, 15 February, 2020 19:27, Peng Yu <[hidden email]> wrote:

>I am trying to see what tables are available in sqlite_master from
>firefox cookies sqlite3 fire.

>~/Library/Application
>Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

>But the error message says "Error: database is locked".

>I see a cookies.sqlite-wal file in the same directory.

>Is there a way to inspect this db file without closing firefox?

Does it work when you close Firefox?  If it works when Firefox is closed but not when Firefox is open, then the answer is probably no.  If you still get an error message when Firefox is closed, then you probably have too old a version of whatever version of SQLite3 you are using to look at the database.

I can, but then I am on Windows and using the latest mostest up-to-date version of the SQLite3 command line tool.  

There are rumours on the various message boards that Firefox opens the databases with exclusive access to prevent fiddling.  Not having looked at the relevant Firefox source code, I have no clue whether or not this is accurate.  However the rumoured exclusive access seems to "go away" when an up-to-date version of SQLite3 is used to look at the databases.  Plus, of course, if the access is supposed to be exclusive then it is broken on Windows.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Peng Yu
> Does it work when you close Firefox?  If it works when Firefox is closed but
> not when Firefox is open, then the answer is probably no.

I can check the content when Firefox is closed (the -wal file
disappears after Firefox is closed).

Why the database can not be read by another sqlite3 session when the
corresponding -wal file exists? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Richard Hipp-3
On 2/16/20, Peng Yu <[hidden email]> wrote:
>> Does it work when you close Firefox?  If it works when Firefox is closed
>> but
>> not when Firefox is open, then the answer is probably no.
>
> I can check the content when Firefox is closed (the -wal file
> disappears after Firefox is closed).
>
> Why the database can not be read by another sqlite3 session when the
> corresponding -wal file exists? Thanks.

Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;"

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Simon Slavin-3
In reply to this post by Peng Yu
On 16 Feb 2020, at 5:15pm, Peng Yu <[hidden email]> wrote:

> Why the database can not be read by another sqlite3 session when the
> corresponding -wal file exists? Thanks.

This is done on purpose by the developers of Firefox to prevent a security vulnerability which I will not describe in public.

One of the Mozilla developers involved in the decision reads this list.  If you have a good reason why you want to open the SQLite database while Firefox is running, you could post it here.  You might be able to persuade them to reconsider the decision.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Stefan Brüns
On Sonntag, 16. Februar 2020 18:36:15 CET Simon Slavin wrote:
> On 16 Feb 2020, at 5:15pm, Peng Yu <[hidden email]> wrote:
> > Why the database can not be read by another sqlite3 session when the
> > corresponding -wal file exists? Thanks.
>
> This is done on purpose by the developers of Firefox to prevent a security
> vulnerability which I will not describe in public.

Will this stop anyone from just copying the DB without the -wal file?
Afterwards, the DB can be read, as there is no longer any associated log.

> One of the Mozilla developers involved in the decision reads this list.  If
> you have a good reason why you want to open the SQLite database while
> Firefox is running, you could post it here.  You might be able to persuade
> them to reconsider the decision.

One use case I am aware of (although this targets places.sqlite, not
cookies.sqlite) is reading the history, bookmarks and tags.

Kind regards,

Stefan

--
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034     mobile: +49 151 50412019
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (201 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Simon Slavin-3
On 16 Feb 2020, at 8:02pm, Stefan Brüns <[hidden email]> wrote:

> Will this stop anyone from just copying the DB without the -wal file?
> Afterwards, the DB can be read, as there is no longer any associated log.

The purpose of the locking is to prevent changes being made to the database during a browser session.  The problem does not occur if changes are made between sessions.  Having a third-party app read the database could be considered a vulnerability (a question for the Mozilla developer team, not me), but it is not related to this specific problem.

> One use case I am aware of (although this targets places.sqlite, not  cookies.sqlite) is reading the history, bookmarks and tags.

These things can be done using the bookmarks API, WebExtensions API, and other methods.  Reading the SQLite database is actually more difficult.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Keith Medcalf
In reply to this post by Richard Hipp-3

On Sunday, 16 February, 2020 10:25, Richard Hipp <[hidden email]> wrote:

>> Why the database can not be read by another sqlite3 session when the
>> corresponding -wal file exists? Thanks.

>Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;"

Perhaps on some platforms, but Firefox 73.0.0 on Windows 10 18636.657 does not -- or if it does, then it doesn't work properly as I can still read/write the various database files while Firefox is running.

However, when I open a database and set locking_mode=exclusive then connection to the same database cannot access the database.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Stefan Brüns
In reply to this post by Simon Slavin-3
On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:

> > One use case I am aware of (although this targets places.sqlite, not
> > cookies.sqlite) is reading the history, bookmarks and tags.
> These things can be done using the bookmarks API, WebExtensions API, and
> other methods.  Reading the SQLite database is actually more difficult.

AFAIK this only works while FF is running ...

Regards,

Stefan

--
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034     mobile: +49 151 50412019
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (201 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Simon Slavin-3
On 16 Feb 2020, at 8:44pm, Stefan Brüns <[hidden email]> wrote:

> On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
>
>>> One use case I am aware of (although this targets places.sqlite, not cookies.sqlite) is reading the history, bookmarks and tags.
>>
>> These things can be done using the bookmarks API, WebExtensions API, and other methods.  Reading the SQLite database is actually more difficult.
>
> AFAIK this only works while FF is running ...

That is the problem that started this thread: that the database file could not be opened while FF was running.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to dump a sqlite db that has an associated -wal file?

Stefan Brüns
On Sonntag, 16. Februar 2020 21:50:18 CET Simon Slavin wrote:
> On 16 Feb 2020, at 8:44pm, Stefan Brüns <[hidden email]>
wrote:
> > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
> >>> One use case I am aware of (although this targets places.sqlite, not
> >>> cookies.sqlite) is reading the history, bookmarks and tags.>>
> >> These things can be done using the bookmarks API, WebExtensions API, and
> >> other methods.  Reading the SQLite database is actually more difficult.>
> > AFAIK this only works while FF is running ...
>
> That is the problem that started this thread: that the database file could
> not be opened while FF was running.

The database being inaccessible while FF is running does not equate to FF is
always running. Both cases (with and without FF running) have to be covered.

And having to write two different access methods (direct access and through
bookmarks API) is obviously the worst of all variants.

Regards,

Stefan

--
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034     mobile: +49 151 50412019
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (201 bytes) Download Attachment