sqlite3_bind_text() and WHERE x IN (?)

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

sqlite3_bind_text() and WHERE x IN (?)

Simon Walter
I am trying to make a prepared statement that has a parameter such as
"1, 893, 121212". obviously it is text (a string) but it cannot be
quoted or the result will be:
SELECT id, data FROM val WHERE id IN ("1, 893, 121212");

I understand normally I would need the quotes, such as:
SELECT id, name, val FROM obj WHERE name = ?

I am not sure if this is happening. From a few tests, it seems to be
what is going on.

How does one use WHERE x IN (?) with a prepared statement? What is the
correct way to do this?

Thanks for your time.

Best regards,

Simon
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Fri, Nov 30, 2018 at 2:10 PM Simon Walter <[hidden email]> wrote:

> How does one use WHERE x IN (?) with a prepared statement? What is the
> correct way to do this?
>

You cannot do it. Must use  WHERE x IN (?, ?, ?), i.e. an explicit and
known in advance
number of bind placeholders. Or not use binding at all, and "paste" your
text value before
preparing the statements. --DD
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Simon Walter
Thanks Dominique,

Much appreciated. I can now stop pulling out my hair. I will do
something with sprintf.

Best regards,

Simon

On 11/30/2018 10:37 PM, Dominique Devienne wrote:

> On Fri, Nov 30, 2018 at 2:10 PM Simon Walter <[hidden email]> wrote:
>
>> How does one use WHERE x IN (?) with a prepared statement? What is the
>> correct way to do this?
>>
>
> You cannot do it. Must use  WHERE x IN (?, ?, ?), i.e. an explicit and
> known in advance
> number of bind placeholders. Or not use binding at all, and "paste" your
> text value before
> preparing the statements. --DD
> _______________________________________________
> 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: sqlite3_bind_text() and WHERE x IN (?)

Richard Hipp-3
On 11/30/18, Simon Walter <[hidden email]> wrote:
> Thanks Dominique,
>
> Much appreciated. I can now stop pulling out my hair. I will do
> something with sprintf.

See https://www.sqlite.org/carray.html

Or, failing that, at least use sqlite3_mprintf() rather than
sprintf(). https://www.sqlite.org/printf.html


--
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: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp <[hidden email]> wrote:

> On 11/30/18, Simon Walter <[hidden email]> wrote:
> > Thanks Dominique,
> >
> > Much appreciated. I can now stop pulling out my hair. I will do
> > something with sprintf.
>
> See https://www.sqlite.org/carray.html


Right. Any table-valued function would do too.

carray() is a dangerous one IMHO, since you expose a pointer to memory,
and must be certain to parameter it correctly so it "interprets" the C-heap
memory
correctly, and does not read past-the-array-end. It's also a security risk.

A safer alternative would be another table-valued function, which parses a
string
and returns the values as some primitive type. In fact, it's probably
possible right now via the
JSON1 extension, if your text value is JSON-formatted.

carray() will be faster of course. But a text-parsing table-valued function
would work well too.

FWIW, I've been asking for the ability to bind "officially" arrays for a
while :).
Just look at the ML archive.

Or, failing that, at least use sqlite3_mprintf() rather than
> sprintf(). https://www.sqlite.org/printf.html


Would that work here, when wanting to "paste" *several* values?
Preventing SQL injections by proper escaping works for "scalar" values, no?
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne <[hidden email]>
wrote:

> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp <[hidden email]> wrote:
>
>> On 11/30/18, Simon Walter <[hidden email]> wrote:
>> > Thanks Dominique,
>> >
>> > Much appreciated. I can now stop pulling out my hair. I will do
>> > something with sprintf.
>>
>> See https://www.sqlite.org/carray.html
>
>
> Right. Any table-valued function would do too.[...]
>
In fact, it's probably possible right now via the
> JSON1 extension, if your text value is JSON-formatted.
>

Yep, works fine, as expected. So that's another possibility too.
Assuming you can use the JSON1 extension and it's enabled in the SQLite
DDL. --DD

sqlite> .header on
sqlite> create table t (c, n);
sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three');
sqlite> select n from t where c in (select value from json_each('[1, 3]'));
n
one
three
sqlite> select n from t where c in (select value from json_each('[]'));
sqlite> select n from t where c in (select value from json_each('[2]'));
n
two
sqlite>

>
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

R Smith-2
In reply to this post by Simon Walter
On 2018/11/30 2:52 PM, Simon Walter wrote:
> I am trying to make a prepared statement that has a parameter such as
> "1, 893, 121212". obviously it is text (a string) but it cannot be
> quoted or the result will be:
> SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
>
> I understand normally I would need the quotes, such as:
> SELECT id, name, val FROM obj WHERE name = ?

There is one slightly convoluted but easy copy-paste solution - if this
is not part of a very cpu-intensive query and you just want an easy way
to get this done this one time...

The following Query will do exactly what you need:
(It's simply a CTE that unravels the comma-separated format line you
give in ? into a table form and then uses that to do the lookup with)

WITH csvrec(i, l, c, r) AS (
       SELECT 1, 1, ?||',', ''
     UNION ALL
       SELECT i,
              instr( c, ',' ) AS vLength,
              substr( c, instr( c, ',' ) + 1) AS vRemainder,
              trim( substr( c, 1, instr( c, ',' ) - 1) ) AS vCSV
         FROM csvrec
        WHERE vLength > 0
     )
     
SELECT id, data
   FROM val
  WHERE id IN (SELECT r FROM csvrec WHERE r <> '');
;

  -- The "... WHERE r <> '' " bit might not be needed here depending on your use case.





This one you can run straight in sqlite to more clearly see what is happening inside the CTE bit using your '1, 893, 121212' example (for fun):

WITH csvrec(i, l, c, r) AS (
       SELECT 1, 1,'1, 893, 121212'||',', ''
     UNION ALL
       SELECT i,
              instr( c, ',' ) AS vLength,
              substr( c, instr( c, ',' ) + 1) AS vRemainder,
              trim( substr( c, 1, instr( c, ',' ) - 1) ) AS vCSV
         FROM csvrec
        WHERE vLength > 0
     )
SELECT * FROM csvrec
;



Disclaimer: This comes as part of the sqlitespeed install example
scripts and as such are simplified to handle basic Comma-separated text
only and does not cover all the very complex multi-quoted, multi-line
stuff that might be found in a complex CSV data file as described in
RFC4180 - so as long as you control the format of the "csv" input text,
all is well.

Cheers,
Ryan

_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Simon Walter
In reply to this post by Dominique Devienne
On 11/30/2018 11:20 PM, Dominique Devienne wrote:

> On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne <[hidden email]>
> wrote:
>
>> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp <[hidden email]> wrote:
>>
>>> On 11/30/18, Simon Walter <[hidden email]> wrote:
>>>> Thanks Dominique,
>>>>
>>>> Much appreciated. I can now stop pulling out my hair. I will do
>>>> something with sprintf.
>>>
>>> See https://www.sqlite.org/carray.html
>>
>>
>> Right. Any table-valued function would do too.[...]
>>
> In fact, it's probably possible right now via the
>> JSON1 extension, if your text value is JSON-formatted.
>>
>
> Yep, works fine, as expected. So that's another possibility too.
> Assuming you can use the JSON1 extension and it's enabled in the SQLite
> DDL. --DD
>
> sqlite> .header on
> sqlite> create table t (c, n);
> sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three');
> sqlite> select n from t where c in (select value from json_each('[1, 3]'));
> n
> one
> three
> sqlite> select n from t where c in (select value from json_each('[]'));
> sqlite> select n from t where c in (select value from json_each('[2]'));
> n
> two
> sqlite>
>


I suppose an array of ints or an array of pointers to \000 terminated
char arrays or any other kind of array could be escaped correctly
provided the caller give some kind of hint as to what the type of data is.

To be honest, I am using apr_dbd as I would like to support more than
just SQLite. So I will need to play around with MySQL and PostgreSQL at
least and maybe branch if SQLite is in use. I have no idea yet if MySQL
and/or PostgreSQL can handle this scenario and how they do it. Though
the possibility for SQL injections is nil, as the comma separated list
is generated by the application and not user input... famous last words?
I must check again.

Interesting tidbit:
...WHERE id IN ("1") actually works. As soon as there is a comma, SQLite
returns 0 rows.

I will keep hacking. Thanks for the advice! Much appreciated.

Simon
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Fri, Nov 30, 2018 at 3:26 PM Simon Walter <[hidden email]> wrote:

> On 11/30/2018 11:20 PM, Dominique Devienne wrote:
> To be honest, I am using apr_dbd as I would like to support more than
> just SQLite. So I will need to play around with MySQL and PostgreSQL at
> least and maybe branch if SQLite is in use. I have no idea yet if MySQL
> and/or PostgreSQL can handle this scenario and how they do it.
>

PostgreSQL has native array support. Also has JSON support.
So I'm fairly sure both a possible with PG, except with different syntaxes
of course.
Don't know about MySQL.

Oracle has a VARRAY datatype, and you use the TABLE() operator to turn
its content into a table-values "thing", so you can write WHERE c in
(TABLE(:1))
and bind the VARRAY, which you've built-up "client-side".

BTW, the fact there's no "client-side" with SQLite is typically used as an
argument
for not supporting "natively" binding the RHS of the WHERE IN clause, and
that the
work-arounds are good enough. Obviously I disagree :). --DD
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

James K. Lowden
In reply to this post by Simon Walter
On Fri, 30 Nov 2018 23:25:48 +0900
Simon Walter <[hidden email]> wrote:

> > SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
...
> I have no idea yet if MySQL and/or PostgreSQL can handle this
> scenario and how they do it.

The important thing to understand about parameterized queries is that
they are not a generalized macro system. Only data -- not metadata, not
arbitrary strings -- can be parameterized.  That's why your IN list
can't be parameterized (except as individual elements) and why can't say

        SELECT id, data FROM ?
or
        SELECT id, ? FROM val

as would occasionally be convenient.  

Other than string-slinging, the only generalized standard solution for
your parameterized IN list, where the number of elements is variable,
is to first insert the list into a table, then use IN or EXISTS against
it.  

--jkl
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Hick Gunter
... which is what SQLite does internally if you provide a list of literal values inside the parentheses. In some cases, SQLite 3.24 has been observed to use such an ephemeral table as the outer table of a join; with detrimental effects on query performance and no CROSS JOIN syntax available to force a different query plan

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von James K. Lowden
Gesendet: Mittwoch, 05. Dezember 2018 19:04
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)

On Fri, 30 Nov 2018 23:25:48 +0900
Simon Walter <[hidden email]> wrote:

> > SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
...
> I have no idea yet if MySQL and/or PostgreSQL can handle this scenario
> and how they do it.

The important thing to understand about parameterized queries is that they are not a generalized macro system. Only data -- not metadata, not arbitrary strings -- can be parameterized.  That's why your IN list can't be parameterized (except as individual elements) and why can't say

        SELECT id, data FROM ?
or
        SELECT id, ? FROM val

as would occasionally be convenient.

Other than string-slinging, the only generalized standard solution for your parameterized IN list, where the number of elements is variable, is to first insert the list into a table, then use IN or EXISTS against it.

--jkl
_______________________________________________
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: [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Thu, Dec 6, 2018 at 8:49 AM Hick Gunter <[hidden email]> wrote:

> ... which is what SQLite does internally if you provide a list of literal
> values inside the parentheses.
>

Which is IMHO a pity that there's no API to bind such an ephemeral table
and 'bind" it.

Heck, given how the carray() eponymous vtable works, just have SQLite
itself manage that
array internally, tying its lifetime to the statement's lifetime, and
provide ways to "pushback"
typed values in a safe manner, possibly with the existing bind APIs, would
achieve the desired
result with very little code I suspect. Oh well... --DD
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Hick Gunter
Maybe someone can come up with a CTE that works for this...

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 06. Dezember 2018 10:38
An: General Discussion of SQLite Database <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

On Thu, Dec 6, 2018 at 8:49 AM Hick Gunter <[hidden email]> wrote:

> ... which is what SQLite does internally if you provide a list of
> literal values inside the parentheses.
>

Which is IMHO a pity that there's no API to bind such an ephemeral table and 'bind" it.

Heck, given how the carray() eponymous vtable works, just have SQLite itself manage that array internally, tying its lifetime to the statement's lifetime, and provide ways to "pushback"
typed values in a safe manner, possibly with the existing bind APIs, would achieve the desired result with very little code I suspect. Oh well... --DD _______________________________________________
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: [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Thu, Dec 6, 2018 at 11:10 AM Hick Gunter <[hidden email]> wrote:

> Maybe someone can come up with a CTE that works for this...
>

How so? I'm not following you. Ryan already provided a CTE to transform a
string into values,
but that involves string parsing, and is kinda ugly (no offence Ryan :) ),
and unlikely to be that
efficient relative to carray. The point is that binding is *scalar only* in
SQLite.

If OTOH, we could write:

...prepare...(db, "select ... from t where c in (?)", ..., &stmt, ...);
...bind_array(stmt, 1, SQLITE_INTEGER, vec.size());
for (auto elem : vec) {
...bind_int(stmt, 1, elem);
}

...bind_array would instantiate the same array the carray() extension
supports.
Existing typed bind APIs would fill in that array, with usual SQLite
conversions,
error checking against declared array-type.
And ...prepare would transparently transform WHERE c IN (?) with WHERE c IN
(carray(?))
with the internally managed C array.

Of course, at prepare time, SQLite doesn't know yet ? is not a scalar as
usual, so perhaps
a different notation is necessary, like the TABLE() operator in Oracle SQL
for example.
But given carray() and the existing infrastructure, I naively fail to see
how the above wouldn't work.

My $0.02. --DD

[1] https://www.sqlite.org/c3ref/c_blob.html
_______________________________________________
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: sqlite3_bind_text() and WHERE x IN (?)

Hick Gunter
Original query:

SELECT ... FROM mytable WHERE field_a IN (<list of values>)AND field_b IN (<list-of-values>) AND <key constraints>

This is sometimes (when mytable is a virtual table that offers to handle "field_a =" and "field_b =" constraints internallly) resolved as
- create table eph_a
- create table eph_b
- full table scan eph_a
- full table scan eph_b
- scan table mytable index #n

Target query:

WITH (...) SELECT ... FROM mytable LEFT JOIN cte_a ON (mytable.a = cte_a.a) LEFT JOIN cte_b ON (mytable.b = cte_b.b) WHERE <key constraints>

Which should resolve as
- create table eph_a
- create table eph_b
- scan table mytable index #n
- lookup mytable.a in eph_a
- lookup mytable.b in eph_b

The cost of a partial index scan is O(log n) to locate the first record and O(m) for retrieving m consecutive records.
The cost of a lookup in an ephemeral table is O(log n).
The cost of a full table scan for an ephemeral table is O(n)

The cost of the first query plan is therefore O(a * b * (m + log n)) or O(a *b *m) + O(a * b * log n)
The cost of the second query plan is only O(log n + m * (log a + log b)) or O((log a*b) * m) + O(log n)

This makes the second query plan much less costly for IN lists of 2 or more elements.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 06. Dezember 2018 11:32
An: General Discussion of SQLite Database <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

On Thu, Dec 6, 2018 at 11:10 AM Hick Gunter <[hidden email]> wrote:

> Maybe someone can come up with a CTE that works for this...
>

How so? I'm not following you. Ryan already provided a CTE to transform a string into values, but that involves string parsing, and is kinda ugly (no offence Ryan :) ), and unlikely to be that efficient relative to carray. The point is that binding is *scalar only* in SQLite.

If OTOH, we could write:

...prepare...(db, "select ... from t where c in (?)", ..., &stmt, ...); ...bind_array(stmt, 1, SQLITE_INTEGER, vec.size()); for (auto elem : vec) { ...bind_int(stmt, 1, elem); }

...bind_array would instantiate the same array the carray() extension supports.
Existing typed bind APIs would fill in that array, with usual SQLite conversions, error checking against declared array-type.
And ...prepare would transparently transform WHERE c IN (?) with WHERE c IN
(carray(?))
with the internally managed C array.

Of course, at prepare time, SQLite doesn't know yet ? is not a scalar as usual, so perhaps a different notation is necessary, like the TABLE() operator in Oracle SQL for example.
But given carray() and the existing infrastructure, I naively fail to see how the above wouldn't work.

My $0.02. --DD

[1] https://www.sqlite.org/c3ref/c_blob.html
_______________________________________________
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: [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

Dominique Devienne
On Thu, Dec 6, 2018 at 12:12 PM Hick Gunter <[hidden email]> wrote:

> The cost of the first query plan is therefore O(a * b * (m + log n)) or
> O(a *b *m) + O(a * b * log n)
> The cost of the second query plan is only O(log n + m * (log a + log b))
> or O((log a*b) * m) + O(log n)
>
> This makes the second query plan much less costly for IN lists of 2 or
> more elements.
>

Sure. I get that. But how does that relate to CTE? Or carray()? or
array-binding?

Perhaps there's an opportunity for a better plan in the context of
ephemeral tables and/or carray()-wrapped "vtables".
But that's a different and separate issue. I guess we each have our own
pet-peeves :). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users