pass vector binding to DBI parameter (rsqlite)

Tue, Aug 12, 2014 12:46 PM

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")

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

pass vector binding to DBI parameter (rsqlite)

Thread (5 messages)