Re: [sqlite] WITHOUT ROWID tables

[x]

Re my earlier post (which didn’t go out to mailing list)

sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
returns false if table tblName is a without rowid table

seems to work for me.

Sent from Mail for Windows 10


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Sunday, February 16, 2020 6:11:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] WITHOUT ROWID tables

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:



" as of SQLite version 3.30.0 on 2019-10-04 "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_HAS_CODEC

[Olaf Schmidt]

Have just seen (in https://www.sqlite.org/src/timeline),
that this compiletime-option was removed (2020-02-07).

Speaking as a wrapper-author...

Are there alternatives in sight, to further support:
- a "ReKey"-feature in a relatively compatible manner,
  without breaking existing (user-)code, which currently does
  make use of "already existing, encrypted DBs" out there?

Background:
I've implemented this in the COM-wrapper for SQLite over
a decade ago - in a compatible way to what was once used
in the .NET-wrapper (at a time, where Robert Simpson was
still developing and maintaining it).

We had kind of an agreement, to not "torpedo" the income-
generating official crypto-module of SQLite, by sticking
to the weaker RC4-Stream-Cipher (being "decent enough"
for "home-usage and non-critical-businesses", IMO).

IIRC - 2 or 3 years ago, I've re-checked sources of the now official
.NET-wrapper - and it seemed that the compatibility (regarding that
"weaker" crypto-support) between the two wrappers was still there
(not sure, if that is still the case with the most recent .NET-wrapper).

In case the feature is still in the .NET wrapper - what is planned
(regarding the "ReKey-feature", and further support for already
existing encrypted DBs, which were encoded with the feature)?

Is there a chance, that the SQLITE_HAS_CODEC compile-switch
can be "re-activated" (to be able to update our wrappers
to newer SQLite-versions without larger efforts)?

If not, could a new-written VFS-module with such "weaker crypto-
support" be imlemented in a way, that it will not break existing
DBs out there?

Kind Regards,

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Peter da Silva]

If you have control over the schema you can put a schema version in some
table.

On Sat, Feb 15, 2020, 13:21 J. King  wrote:

> On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz 
> wrote:
> >Wouldn't be something like
> >
> >SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
> >contains "WITHOUT ROWID"
> >
> >be sufficient?
> >
> >Just being curious.
> >
> >- Original Message -
> >From: sky5w...@gmail.com 
> >To: SQLite mailing list 
> >Sent: Saturday, February 15, 2020, 18:06:47
> >Subject: [sqlite] WITHOUT ROWID tables
> >
> >Ok, not ideal. Still confusing, but I see the difference.
> >For my code, I know the schemas. I guess a SQL builder could offer up
> >query
> >options to the user browsing new databases.
> >
> >On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
> >wrote:
> >
> >> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
> >
> >> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
> >is a
> >> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> >> table.
> >> >>
> >> > Confused...What if I made an index on a ROWID table?
> >> > CREATE INDEX "Z" ON "DOC" ("n_id");
> >
> >> The parameter in index_info() is normally the name of an index.  So
> >if you
> >> create an index "Z" and ask for index_info("Z") you will get
> >information on
> >> that index.
> >
> >> If you create a WITHOUT ROWID table with name 'Y", and ask for
> >> index_info("Y") you will get information on the primary key of that
> >table.
> >
> >> If both exist, you get information about the index.
> >
> >> Simon
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> CREATE TABLE t(
> c TEXT DEFAULT 'WITHOUT ROWID'
> -- this comment mentions something about WITHOUT ROWID
> );
>
>
> Contrived, no question, but possible.
> --
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  
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

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 16 Feb 2020, at 8:44pm, Stefan Brüns  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Stefan Brüns]

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

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

On Sunday, 16 February, 2020 10:25, Richard Hipp  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 16 Feb 2020, at 8:02pm, Stefan Brüns  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: more robust handling of invalid UTF-16 data

[Maks Verver]

*Richard:* the issue with the JSON extension seems unrelated to the issue
that I reported originally, which relates to the SQLite C API
(specifically, the sqlite3_bind_text16() and sqlite3_bind_text16()
functions). My issue is still not fixed.

I've expanded my original sample code to make it easier to run the test and
reproduce the problems:
https://gist.github.com/maksverver/c3d5da8a0a9f2ec1c2a225209f290e13

Let me know if you need more help understanding/reproducing the problem.

*Dennis:* thanks for raising the issue again, and for digging through the
Unicode standard to confirm the most reasonable behavior.

I think your proposed patch is not quite correct. I think I spot two
problems. One:

  if( c>=0xDC00 && c<=0xE000 && TERM ) {

.. here, you should drop the `&& TERM`, otherwise you'll fail to replace a
high surrogate when it occurs at the end of the string. Also, you should
check c<0xE000 because 0xE000 is a valid character by itself. Two:

} else if( c>=0xD800 && TERM ){

Here, you should also check c<0xDC00 (or c<0xE000), otherwise you'll
misinterpret valid characters with code points 0xE000 and above as part of
a surrogate pair.

I believe my original patch handles these and all other cases correctly.

  - Maks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Simon Slavin]

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:



" as of SQLite version 3.30.0 on 2019-10-04 "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Bernardo Ramos]

I've got no rows for both with and without rowid tables:

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> create table t1(name);
sqlite> create table t2(name primary key, phone) without rowid;
sqlite> pragma index_info(t1);
sqlite> pragma index_info(t2);
sqlite> pragma index_info('t1');
sqlite> pragma index_info('t2');
sqlite> .tab
t1  t2
sqlite> select * from t1 where rowid=1;
sqlite> select * from t2 where rowid=1;
Error: no such column: rowid




To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');".  If you get back one or more rows, then XYZ is a
WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
table.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 16 Feb 2020, at 5:15pm, Peng Yu  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 2/16/20, Peng Yu  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users