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
|

Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

Richard Hipp-3
On 2/11/20, J. King <[hidden email]> 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
[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

Eric Grange-3
In reply to this post by J. King-3
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 <[hidden email]> a écrit :

> 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
>
_______________________________________________
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

Dominique Devienne
On Wed, Feb 12, 2020 at 9:02 PM Eric Grange <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12