[R] pass vector binding to DBI parameter (rsqlite)

[Dan Muresan]

Yes, of course, that's an obvious work-around, thanks. Another one is
to use temporary tables.

But I'd like to know if binding a vector to an SQL parameter is
possible in rsqlite (or even in the DBI API or with other drivers --
it seems to me it isn't). This seems like a nasty shortcoming
(especially in light of SQL injection, but there are other
considerations).

On 8/12/14, John McKown <john.archie.mckown at gmail.com> wrote:
> On Tue, Aug 12, 2014 at 10:55 AM, Dan Muresan <danmbox at gmail.com> wrote:
>> Hi, is there a way to bind vectors to DBI query parameters? The
>> following tells me that vectors are sent as separate values:
>>
>>> library("RSQLite")
>>> c <- dbConnect (SQLite())
>>> dbGetQuery(c, "create table tst (x int, y int)")
>>> dbGetQuery(c, "insert into tst values (?, ?)", data.frame(x=c (1,2,1,2),
>>> y=c(3, 4, 5, 6)))
>>> dbReadTable(c, "tst")
>>   x y
>> 1 1 3
>> 2 2 4
>> 3 1 5
>> 4 2 6
>>> dbGetQuery(c, "select * from tst where y not in (?)", c(7,6))
>>   x y
>> 1 1 3
>> 2 2 4
>> 3 1 5
>> 4 2 6
>> 5 1 3
>> 6 2 4
>> 7 1 5
>>
>> This looks like 2 result sets (4 + 3 entries), not one.
>>
>> Is there to send multiple values to a '?' binding? Is this at all
>> possible using the R DBI interface (not necessarily with rsqlite)?
>
> I don't really _know_ much, but what I would try would be something like:
>
> dbGetQuery(c,"select * from tst where y not in
> (?)",paste(c(7,6),collapse=','));
>
> The paste(c(7,6),collapse=',') results in the string "6,7". You could
> always subject yourself to a SQL injection attack by doing:
>
> dbGetQuery(c,paste("select * from tst where y not in
> (",c(7,6),")",collapse=','));
>
> If you do this and use a variable instead of the c(7,6), make sure you
> "cleanse" the contents of the variable. Just as making sure that there
> is no "bare" semi-colon in it. And other things that don't come to
> mind off hand.
>
> Hum, perhaps better:
>
> values<-c(7,6);
> dbGetQuery(c,paste("select * from tst where y not in (",
>
> paste(rep('?',length(values)),collapse=','),
>                                 ")"),
>                         values);
>
> As you can see, this dynamically adjusts the number of ? marks in the
> SELECT statement, based on the number of elements in the "values"
> variable.
>
> --
> There is nothing more pleasant than traveling and meeting new people!
> Genghis Khan
>
> Maranatha! <><
> John McKown
>