Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

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

Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Alex Bronstein
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=250000
<https://sources.debian.org/src/sqlite3/3.27.2-3/debian/rules/#L50> (issue
<https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=717900>) whereas
Fedora doesn't
set the flag
<https://git.centos.org/rpms/sqlite/blob/c8/f/SPECS/sqlite.spec#_152> (issue
<https://bugzilla.redhat.com/show_bug.cgi?id=1798134>), 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Simon Slavin-3
On 4 Feb 2020, at 6:27pm, Alex Bronstein <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> On Behalf Of Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Simon Slavin-3
On 4 Feb 2020, at 7:13pm, Deon Brewis <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Keith Medcalf
In reply to this post by Deon Brewis

On Tuesday, 4 February, 2020 12:14, Deon Brewis <[hidden email]> 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 ?, ?1000000;

then the VDBE program will allocate an array to hold 1000000 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Scott Perry
In reply to this post by Simon Slavin-3
On Feb 4, 2020, at 12:26 PM, Simon Slavin <[hidden email]> wrote:
>
> On 4 Feb 2020, at 7:13pm, Deon Brewis <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

J. King-3
On February 4, 2020 7:10:52 p.m. EST, Scott Perry <[hidden email]> wrote:

>On Feb 4, 2020, at 12:26 PM, Simon Slavin <[hidden email]> wrote:
>>
>> On 4 Feb 2020, at 7:13pm, Deon Brewis <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Scott Robison-2
On Tue, Feb 4, 2020, 5:23 PM J. King <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Keith Medcalf
In reply to this post by J. King-3

On Tuesday, 4 February, 2020 17:23, J. King <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Hick Gunter
In reply to this post by Deon Brewis
WHERE x IN carray($PTR, $DIM)

With $PTR being the address of the array and $DIM ist cardinality. Should be tons faster than parsing and binding a gazillion parameters.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Deon Brewis
Gesendet: Dienstag, 4. Februar 2020 20:14
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

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 <[hidden email]> On Behalf Of Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Alex Bronstein
In reply to this post by 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@.../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
<https://www.drupal.org/>, 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
<https://www.php.net/manual/en/book.pdo.php> 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 <[hidden email]>
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=250000
> <https://sources.debian.org/src/sqlite3/3.27.2-3/debian/rules/#L50> (issue
> <https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=717900>) whereas
> Fedora doesn't set the flag
> <https://git.centos.org/rpms/sqlite/blob/c8/f/SPECS/sqlite.spec#_152> (
> issue <https://bugzilla.redhat.com/show_bug.cgi?id=1798134>), 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Richard Hipp-3
On 2/10/20, Digital Dog <[hidden email]> 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
[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: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Digital Dog
On Mon, Feb 10, 2020 at 8:27 PM Richard Hipp <[hidden email]> wrote:

> On 2/10/20, Digital Dog <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Richard Hipp-3
On 2/10/20, Digital Dog <[hidden email]> 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
[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: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Keith Medcalf

On Monday, 10 February, 2020 14:36, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Digital Dog
In reply to this post by Richard Hipp-3
On Mon, Feb 10, 2020 at 9:03 PM Richard Hipp <[hidden email]> wrote:

> On 2/10/20, Digital Dog <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Jens Alfke-2


> On Feb 11, 2020, at 2:10 AM, Digital Dog <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

J. King-3
On February 11, 2020 1:43:30 p.m. EST, Jens Alfke <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12