On 11/30/2018 11:20 PM, Dominique Devienne wrote:
> On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne <ddevie...@gmail.com>
> wrote:
>
>> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp <d...@sqlite.org> wrote:
>>
>>> On 11/30/18, Simon Walter <si...@gikaku.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
