Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Dominique Devienne]

On Wed, Feb 12, 2020 at 9:02 PM Eric Grange  wrote:
> [...] This is completely safe vs SQL injection, and IME quite efficient. [...]

I disagree that this is efficient enough. I'd much rather have native support in
SQLite for array binding, in the public API, than this. That public
API could wrap
what carray does perhaps, except in a type-safe way (which carray is not IMHO).

e.g., the API could be, for SQL "select rowid from tab where owner = ?
and name_id in (?)":

sqlite3_bind_int(stmt, 1, scalar_int_val);
sqlite3_bind_array_begin(stmt, 2, vector_int_val.size()); // size
hint, to pre-size internal buffers
for (int i : vector_int_val) { // C++11 range-for loop
  sqlite3_bind_int(stmt, 2, i);
}
sqlite3_bind_array_end(stmt, 2);

That syntax is completely made up, but with the equivalent of carray(), SQLite
could efficiently "do the right thing" (perhaps rewriting the SQL into
a join), knows
the cardinatity of the array, so can order the join correctly, etc...

The above approach adds only two APIs, and reuses the existing bind APIs,
to avoid duplicating them all with array-variants. FWIW :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Eric Grange]

If the trouble comes from a big "IN()", an approach can be to pass all the
values in a JSON array (one parameter) and use json_each in the query.
This is completely safe vs SQL injection, and IME quite efficient.

IME using JSON + json_each is also very efficient to fill temporary tables
(indexed if appropriate), in case the filter is reused in multiple queries.

Le mar. 11 févr. 2020 à 20:39, J. King  a écrit :

> On February 11, 2020 1:43:30 p.m. EST, Jens Alfke 
> wrote:
> >I ran into this a few months ago. I ended up just biting the bullet and
> >constructing a SQL statement by hand, concatenating comma-separated
> >values inside an "IN (…)" expression.
> >
> >Yes, SQL injection is a danger. But if you're being bad in just one
> >place, and you review that code, you can do this safely. SQLite's C API
> >even has a function that escapes strings for you, and if you're not
> >coding in C/C++, it's easy to write your own; basically
> >   str ⟶ "'" + str.replace("'", "''") + "'"
>
>
> Same here, for what it's worth. Since SQLite also has a 1M byte statement
> length limit I had my application embed terms once an IN() expression
> exceeded a certain number of terms, but used parameters always for string
> terms longer than a couple hundred bytes.
> --
> 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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[J. King]

On February 11, 2020 1:43:30 p.m. EST, Jens Alfke  wrote:
>I ran into this a few months ago. I ended up just biting the bullet and
>constructing a SQL statement by hand, concatenating comma-separated
>values inside an "IN (…)" expression.
>
>Yes, SQL injection is a danger. But if you're being bad in just one
>place, and you review that code, you can do this safely. SQLite's C API
>even has a function that escapes strings for you, and if you're not
>coding in C/C++, it's easy to write your own; basically
>   str ⟶ "'" + str.replace("'", "''") + "'"


Same here, for what it's worth. Since SQLite also has a 1M byte statement 
length limit I had my application embed terms once an IN() expression exceeded 
a certain number of terms, but used parameters always for string terms longer 
than a couple hundred bytes. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Richard Hipp]

On 2/11/20, J. King  wrote:
> SQLite also has a 1M byte statement
> length limit ...

The statement length limit is yet another defense against mischief
caused by SQL injections.

-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Jens Alfke]

> On Feb 11, 2020, at 2:10 AM, Digital Dog  wrote:
> 
> Thanks for enlighening again. It was just a thought. It seems it would be a
> lot of design and code to maintain the performance while preventing the
> original problem from happening. Not worth the trouble. But maybe
> increasing the parameter count to 10.000 would not hurt?

I ran into this a few months ago. I ended up just biting the bullet and 
constructing a SQL statement by hand, concatenating comma-separated values 
inside an "IN (…)" expression.

Yes, SQL injection is a danger. But if you're being bad in just one place, and 
you review that code, you can do this safely. SQLite's C API even has a 
function that escapes strings for you, and if you're not coding in C/C++, it's 
easy to write your own; basically
str ⟶ "'" + str.replace("'", "''") + "'"

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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

On Mon, Feb 10, 2020 at 9:03 PM Richard Hipp  wrote:

> On 2/10/20, Digital Dog  wrote:
> > Maybe they should be treated as a
> > dictionary/hashtable/linked list or similar?
> >
>
>

> Parameter look-ups are on the critical path.  How much performance are
> you willing to give up in order to have parameters with larger
> numbers?
>
>
Thanks for enlighening again. It was just a thought. It seems it would be a
lot of design and code to maintain the performance while preventing the
original problem from happening. Not worth the trouble. But maybe
increasing the parameter count to 10.000 would not hurt?


> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Keith Medcalf]

On Monday, 10 February, 2020 14:36, Simon Slavin  wrote:

>Does this problem affect unnumbered indexes too ?  In other words if I
>have

>(?,?,?,?,?)

>and bind to the fifth one using the index do I have the same problems as
>having

>(?1,?2,?3,?4,?5)

>and bind to the fifth one using its number ?

What particular problem are you referring to?  When you use a "parameter" in an 
SQL statement, then during prepare time the total number of unique parameters 
(and the highest used index) is recorded.  An array must be allocated attached 
to the "statement" containing enough entries to allow all these parameters to 
be used.  So if you use a parameter "?437" then the array size will be 
allocated of sufficient size to hold 437 parameters.

In addition, whenever a "named parameter" is used (?N, :name, @name, $name) 
then the parameter name is entered into yet another data structure which 
records which "index position" corresponds to that name.  This is so that the 
sqlite3_bind_parameter_name and sqlite3_bind_parameter_index can map between 
the name and the number.

So if you use
(?,?,?,?,?)
then an array of 5 parameters will be required, and no name<->index mapping 
VList will be created.

However, if you use:
(?1,?2,?3,?4,?5)
then an array of 5 parameters will be required, and a name<->index mapping 
VList will be created for all 5 of the named parameters.

If you use:
(?,?,?,?5,?4)
then an array of 5 parameters will be required, and a name<->index mapping 
VList will be created for the 2 named parameters (?5 and ?4).

If you were to use this:
(?,:t,?5,?2)
then an array of 5 parameters will be required (the max index used is 5, even 
though the parameter at index 4 is not referenced) and the name<->index mapping 
VList will contain ":t"<->2, "?5"<->5. The "?2" name will not be recorded since 
parameter index 2 already has a name.

Perhaps in the case of ?N parameters the extra step of recording the name could 
be skipped since all "not otherwise named" ?N parameters will always have name 
?N and index N.  But at the moment the name is recorded in the VList for ?N 
parameters if no name is registered for the parameter at index N.  There is 
probably a reason for this though I don't know what it is.

The generated VDBE program will always refer to parameters by their index and 
not by their names.  The name<->index VList table is kept attached to the 
statement for use by the sqlite3_bind_parameter_name and 
sqlite3_bind_parameter_index API functions.

-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

Does this problem affect unnumbered indexes too ?  In other words if I have

(?,?,?,?,?)

and bind to the fifth one using the index do I have the same problems as having

(?1,?2,?3,?4,?5)

and bind to the fifth one using its number ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Richard Hipp]

On 2/10/20, Digital Dog  wrote:
> Maybe they should be treated as a
> dictionary/hashtable/linked list or similar?
>

Parameter look-ups are on the critical path.  How much performance are
you willing to give up in order to have parameters with larger
numbers?


-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

On Mon, Feb 10, 2020 at 8:27 PM Richard Hipp  wrote:

> On 2/10/20, Digital Dog  wrote:
> >
> > Nobody bothered to actually show the downside of increasing this value to
> > e.g. 10 thousands but everybody immediately proceeded to grumble.
> > What is the justifiable rationale to not change the default?
>
> The maximum number of variables used to be unlimited (or, at least,
> only limited by the ability of a 32-bit integer to count them).  But
> that was shown to facilitate a DOS attack.  Anyone who could inject
> SQL could do "SELECT ?2147483647;".  This would cause SQLite to
> allocate an array of 2147483648 elements, each of which is 72 bytes in
> size, in which to store all of the parameters, potentially using up
> all available memory in the process.  Even ?32767 uses a big chunk of
> heap memory - more than embedded systems people are comfortable giving
> up - especially if the attacker can trick the system into creating
> multiple prepared statements with ?32767 in them.  The allocated array
> lives for the life of the prepared statement.
>

Thanks, that makes a lot of sense. However maybe this needs rearchitecting,
so that only used keys are allocated, not all up to the one specified in
"SELECT ?2147483647".In this case I think SQLite should only allocate space
for this single parameter. Maybe they should be treated as a
dictionary/hashtable/linked list or similar?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Richard Hipp]

On 2/10/20, Digital Dog  wrote:
>
> Nobody bothered to actually show the downside of increasing this value to
> e.g. 10 thousands but everybody immediately proceeded to grumble.
> What is the justifiable rationale to not change the default?

The maximum number of variables used to be unlimited (or, at least,
only limited by the ability of a 32-bit integer to count them).  But
that was shown to facilitate a DOS attack.  Anyone who could inject
SQL could do "SELECT ?2147483647;".  This would cause SQLite to
allocate an array of 2147483648 elements, each of which is 72 bytes in
size, in which to store all of the parameters, potentially using up
all available memory in the process.  Even ?32767 uses a big chunk of
heap memory - more than embedded systems people are comfortable giving
up - especially if the attacker can trick the system into creating
multiple prepared statements with ?32767 in them.  The allocated array
lives for the life of the prepared statement.
-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

Huh so the typical ranting for a valid use case has happened.

> Another alternative is to construct the command as a string.
begging for SQL injection. Thanks, no.

> all the suggestions and examples with temporary tables
and that's what you call easy for the programmer?

Nobody bothered to actually show the downside of increasing this value to
e.g. 10 thousands but everybody immediately proceeded to grumble.
What is the justifiable rationale to not change the default?

And while I'm on the line, I would like to ask for another change: to
enable GENERATE_SERIES by default in command line shell builds.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Alex Bronstein]

Thanks, everyone, for your responses on Tuesday. I wasn't on the mailing
list, so didn't receive them in my email, but I am now, and I can see the
responses on
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg118979.html.
Much appreciated!

> Can you show us some place where using 999 parameters is a reasonable way
to use SQLite ?

The use-case I have is that I'm a maintainer of Drupal
, a CMS written in PHP. In Drupal, we support
multiple database backends. Typically, a production site will operate on
MySQL or PostgreSQL, but we also support SQLite, which is convenient for
local testing. We have a database abstraction API that allows module and
site developers to write code like:

$all_results = $db->select('table_foo')
  ->fields('table_foo', ['field_1', 'field_2'])
  ->condition('field_3', $large_array, 'IN')
  ->execute()
  ->fetchAll();

The implementation of execute() above ends up calling a PHP PDO
 statement that's more or less
as follows:

$statement = $pdo->prepare("SELECT field_1, field_2 FROM table_foo WHERE
field_3 IN (?, ?, ... ?)");
return $statement->execute($large_array);


A similar use case is for insertion. For example, a module or site
developer can call:

$upsert = $connection->upsert('kv')->key('k')->fields(['k', 'v']);
foreach ($large_associative_array as $key => $value) {
  $upsert->values(['k' => $key, 'v' => $value]);
}
$upsert->execute();

The execute() above ends up calling a PHP PDO statement that's appropriate
for the database backend. On SQLite prior to 3.24 it's more or less:

$statement = $pdo->prepare("INSERT OR REPLACE INTO kv (k, v) VALUES ((?,
?), (?, ?), ... (?, ?))");
return $statement->execute($values);


In the case of the multi-row insert/upsert, we can change our
implementation to execute multiple PDO statements in succession in order to
keep each one below the 999 limit.

In the case of the IN (...), thank you for your suggestions to use a temp
table. Because there can be multiple calls to ->condition(), including
nested ones, it might be a bit tricky for us to get the temp tables
implemented correctly. We'd need to track multiple temp tables and then
drop them at the correct time, which would break the current encapsulation
of our API, but it's potentially doable with some refactoring. However,
your temp table idea inspired me to come up with an alternate solution of
using json_each(). So that first example could be implemented as:

$statement = $pdo->prepare("SELECT field_1, field_2 FROM table_foo WHERE
field_3 IN (select value from json_each(?))");
return $statement->execute([json_encode($large_array)]);


If you're curious, I posted a proof of concept patch for both the upsert
and the large IN condition to
https://www.drupal.org/project/drupal/issues/2031261#comment-13454464.


In summary, yes, I think we can change things in Drupal to work within the
999 limit. I think there might be other PHP projects that could benefit
from a higher limit (for example, ones that don't have Drupal's abstraction
API around PDO), but I don't know enough specifics about those to argue on
their behalf.

Thanks again for your consideration and insights.

Alex.


On Tue, Feb 4, 2020 at 10:27 AM Alex Bronstein 
wrote:

> Prepared statements are good practice and recommended in places such as
> https://www.php.net/manual/en/pdo.prepared-statements.php. There are use
> cases for using them with many items in an IN() clause, or when inserting
> many rows in a single INSERT statement. In such cases, you can easily end
> up with more than 999 parameters.
>
> While sqlite can be compiled with a larger SQLITE_MAX_VARIABLE_NUMBER
> flag, there are situations where the application developer doesn't have
> control over how the system libraries are compiled. For example, a given
> PHP application could run on either a stock Debian/Ubuntu installation, a
> stock Fedora/RHEL/CentOS installation, or other systems. Debian compiles
> sqlite with SQLITE_MAX_VARIABLE_NUMBER=25
>  (issue
> ) whereas
> Fedora doesn't set the flag
>  (
> issue ), so gets
> sqlite's default.
>
> According to some of the answers on
> https://stackoverflow.com/questions/6581573/what-are-the-max-number-of-allowable-parameters-per-database-provider-type,
> PostgreSQL and some other databases support a 16 bit parameter count (64K
> parameters). Given current memory availability, can sqlite's default be
> raised to something similar to that?
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Keith Medcalf]

On Tuesday, 4 February, 2020 17:23, J. King  wrote:

>Not everyone has access to carrays and intarrays, either, such as PHP
>users like myself.

Then you should probably be creating a temporary table and using that/

begin immediate;
create temporary table inlist(x primary key(x)) without rowid;
insert into temp.inlist values (?); -- for each value you want in your IN list, 
one after each
commit;
select ... from ... where x IN (select x from temp.inlist);
drop table temp.inlist;


In Python one would do something like:

bloodybiglist = [...]
cursor.execute('begin immediate;')
cursor.execute('create temporary table inlist(x primary key(x)) without rowid;')
cursor.executemany('insert into temp.inlist values (?);', list(tuple((x,)) for 
x in bloodybiglist))
cursor.execute('commit;')
cursor.execute('select ... from ... where x in (select x from inlist);')
cursor.execute('drop table temp.inlist;')

In other interface wrappers you would still have to bind the parameters one at 
a time.  Cannot you prepare a statement in PHP (eg, the insert statement) and 
loop though binding the parameters and executing the prepared statement one 
binding parameter by each?

eg:

stmt = prepare('insert into temp.inlist values (?);')
for item in bloodybiglist:
  stmt.bind(stmt, 1, item)
  stmt.execute()
  
Which is what executemany does behind the curtain ...

-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Robison]

On Tue, Feb 4, 2020, 5:23 PM J. King  wrote

> Not everyone has access to carrays and intarrays, either, such as PHP
> users like myself.
>

But everyone has access to temp tables, and I think the idea of creating a
temp table, inserting 1000 items in a loop, and using that temp table in
the query, is a lot more clear than formatting a query as a string with
?1000 or more bound parameters. It is more idiomatic SQL, I would think.


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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[J. King]

On February 4, 2020 7:10:52 p.m. EST, Scott Perry  wrote:
>On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
>> 
>> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
>> 
>>> WHERE x IN (?1,?2,?3,?4...,?1000 )
>> 
>> People really do this ?  Sheesh.
>
>It's a pretty common pattern. Sure, queries that are likely to use very
>large IN groups ought to be using carray or intarray but not everyone
>knows they exist :)

Not everyone has access to carrays and intarrays, either, such as PHP users 
like myself. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Perry]

On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
> 
> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
> 
>> WHERE x IN (?1,?2,?3,?4...,?1000 )
> 
> People really do this ?  Sheesh.

It's a pretty common pattern. Sure, queries that are likely to use very large 
IN groups ought to be using carray or intarray but not everyone knows they 
exist :)

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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Keith Medcalf]

On Tuesday, 4 February, 2020 12:14, Deon Brewis  wrote:

>WHERE x IN (?1,?2,?3,?4...,?1000 )

That would be a really silly construct to use.  Why are you bothering to name 
all the parameters?  Anonymous parameters are merely an array of pointers to 
values.  When you give the parameters names then a linkage between the "name" 
and the "position" needs to be kept, as well as a hash table so that the "name" 
can be looked up.  When you refer to duplicate anonymous parameters you have to 
use a name for the one of them that is not the next anonymous parameter in 
line, but sheesh, naming them all?  Why?

>And the IN clause is filled by a list or array that's held inside the
>calling application memory rather than in SQLITE.

The VDBE program still needs an array of pointers for all used parameter range 
(from 1 to the highest parameter used).  That means that if you use something 
like:

select ?, ?100;

then the VDBE program will allocate an array to hold 100 parameter pointers 
(assuming that number of parameters were allowed).  And bind parameters are 
only stored in application memory if they are TEXT or BLOB type and you make 
the bind call providing a de-allocator (ie, not SQLITE_TRANSIENT) and even then 
if and only if no conversions need to be performed (for example from you 
external encoding to the internal database encoding).

>The alternate to this is to create a virtual table wrapper over the
>internal datasets of the app. Which is of course better, but harder. (We
>need an STL for SQLite. SqliteTL?).

see the carray extension ...

>PS: Doesn't SQLITE internally order an IN list and do a join across it?
>It seems to perform better than I would expect from a flat array.

Sort of.  When you do an IN (?,?,?,?) or IN (value, value, value ...) the 
values or parameters are loaded one after each into a without rowid table 
(effectively, that looks like "create temporary table temptable (variable 
primary key variable) without rowid") and then the table.variable IN 
(...list...) is treated as a "table JOIN temptable ON table.variable == 
temptable.variable".  NULLS in the IN list are silently discarded of course 
since they cannot be used with IN (which is defined as == not IS for each in 
turn).

-- 
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:

> WHERE x IN (?1,?2,?3,?4...,?1000 )

People really do this ?  Sheesh.

> The alternate to this is to create a virtual table wrapper over the internal 
> datasets of the app. Which is of course better, but harder. (We need an STL 
> for SQLite. SqliteTL?).

One alternative is to create and populate a temporary table, then use an INNER 
JOIN or an EXCEPT.  You can keep the temporary table around until the app quits.

Another alternative is to construct the command as a string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Deon Brewis]

WHERE x IN (?1,?2,?3,?4...,?1000 )

And the IN clause is filled by a list or array that's held inside the calling 
application memory rather than in SQLITE.

The alternate to this is to create a virtual table wrapper over the internal 
datasets of the app. Which is of course better, but harder. (We need an STL for 
SQLite. SqliteTL?).

PS: Doesn't SQLITE internally order an IN list and do a join across it? It 
seems to perform better than I would expect from a flat array.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users