R pass parameter to SQL IN clause using dbBind (possible without glue package?)

Question

Just wondering if it is possible to pass parameters to the SQL query IN clause using DBI? Have tried the following (and many variations, including unnamed parameters)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > $PW and [Petal.Length] in ($PL)")
dbBind(iris_result, list(PW=2.3, PL={6.0, 5.1}))
dbFetch(iris_result)

This link, Parameterized Queries, shows a method using the glue package, however, I would like to know if it is possible with just DBI.

Thanks.

Note, for reference, here is the method using glue:

rs_sql <- glue_sql("SELECT * FROM iris WHERE [Petal.Width] > {pwin} and [Petal.Length] IN ({lengths*})", 
                        pwin = 2.3, lengths = c(6.0, 5.1),
                        .con = con
                        )
iris_result <- dbSendQuery(con, rs_sql)
dbFetch(iris_result)



rs_sql <- glue_sql("SELECT * FROM iris WHERE [Petal.Width] > {pwin} and [Species] IN ({species*})", 
                        pwin = 2.3,
                        species = c('virginica'),
                        .con = con
                        )
iris_result <- dbSendQuery(con, rs_sql)
dbFetch(iris_result)

`fn$` from the gsubfn package when prefaced to any command performs substitution on its arguments. This is not specific to sql although sqldf does use the facility. ``library(gsubfn); PW <- 2.3; PL <- c(6, 5.1); fn$dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > $PW and [Petal.Length] in (`toString(PL)`)")`` — G. Grothendieck, Feb 14 '18 at 01:42
I would guess it is NOT possible with native `DBI` (which `RSQLite` is based on) when using parameterized queries since a parameter in the `in` clause cannot be parameterized in SQL normally but requires string concatenation to build the SQL query string (opening all kinds of SQL code injection!) — R Yoda, Feb 14 '18 at 08:23
As original post perhaps wasn't clear, just wanted to add that the aim of my post was to determine if it was possible using native DBI and that I didn't have a syntax error. I am otherwise not opposed to using the glue package. Still to check below answers/other comments (not in front of R at present), however, @R Yoda, this otherwise answers my question. — user1420372, Feb 14 '18 at 12:55

R Yoda · Accepted Answer · 2018-02-14T13:01:26.390

If you want to use one single parameter to bind an undefined number of actual values in the IN clause of SQL using dbBind(): You can't!

library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)

iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > $PW and [Petal.Length] in ($PL)")
dbBind(iris_result, list(PW=2.3, PL=list(6.0, 5.1)))
# Error in rsqlite_bind_rows(res@ptr, params) : Parameter 2 does not have length 1.

This works only when you define one parameter per element of the IN clause, see the syntax diagrams for SQLite:

Option 1 (if the number of `IN` elements is always the same):

A possible work-around is to predefine a number of parameters and always deliver values for them in dbBind.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)

# Works only if you know the number of IN-elements in adavance...
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > $PW and [Petal.Length] in ($PL1, $PL2)")
dbBind(iris_result, list(PW=2.3, PL1=6.0, PL2=5.1))
dbFetch(iris_result)
#   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
# 1          6.3         3.3          6.0         2.5 virginica
# 2          5.8         2.8          5.1         2.4 virginica

Option 2 (if the number of `IN` elements is changing):

You could also count the number of actual parameters and generate the same number of query parameters in the IN clause, then prepare the SQL query with dbSendQuery. This prevents SQL code injection:

in.params <- c(PL1=6.0, PL2=5.1, PL3=5.6)
sql <- paste0("SELECT * FROM iris WHERE [Petal.Width] > $PW and [Petal.Length] in (",
              paste0("$", names(in.params), collapse = ", "),
              ")")
iris_result <- dbSendQuery(con, sql)
dbBind(iris_result, c(list(PW=2.3), in.params))
dbFetch(iris_result)
#   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
# 1          6.3         3.3          6.0         2.5 virginica
# 2          5.8         2.8          5.1         2.4 virginica
# 3          6.3         3.4          5.6         2.4 virginica
# 4          6.7         3.1          5.6         2.4 virginica

But this also means not to reuse a prepared statement and if this is not what you want there is only classical string concatenation of the SQL statement:

Option 3: Do it yourself with SQL string concatenation:

Without using the glue package you can only concatenate the SQL string yourself and try to minimize the risk of SQL code injection if the parameter values can be entered by a (bad) user.

You can use the dbQuote* functions from DBI (RSQLite is DBI interface compliant) for that...

score 2 · Answer 2 · answered Feb 14 '18 at 01:28

Both elements in the list has to be of the same length. From Value section in ?dbBind:

Binding too many or not enough values, or parameters with wrong names or unequal length, also raises an error. If the placeholders in the query are named, all parameter values must have names (which must not be empty or NA), and vice versa, otherwise an error is raised.

Further in Specification in ?dbBind:

All elements in this list must have the same lengths and contain values supported by the backend

The below works for me:

library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Length] = $PL and [Petal.Width] > $PW")
pl <- c(6.0, 5.1)
dbBind(iris_result, list(PL=pl, PW=rep(2.3, length(pl))))
dbFetch(iris_result)

Victor Burnett · Answer 3 · 2021-10-20T23:09:52.383

0

According to the documentation available here https://dbi.r-dbi.org/reference/dbbind

It seems you can accomplish the equivalent of an IN condition with the following:

iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Species] = $species")

dbBind(iris_result, list(species = c("setosa", "versicolor", "unknown")))

I personally had success with the following scheme found in https://cran.r-project.org/web/packages/DBI/vignettes/DBI-advanced.html:

res <- dbSendQuery(con, "SELECT * FROM film WHERE rating = ?")

dbBind(res, list(c("G", "PG")))

edited Oct 20 '21 at 23:09

answered Oct 20 '21 at 22:05

Victor Burnett

588
6
10

Sure, but the problem is when you have two WHERE conditions (i.e. see answer from chinsoon12). – Roman Luštrik Dec 03 '21 at 13:46

R pass parameter to SQL IN clause using dbBind (possible without glue package?)

3 Answers3

Option 1 (if the number of IN elements is always the same):

Option 2 (if the number of IN elements is changing):

Option 3: Do it yourself with SQL string concatenation:

Option 1 (if the number of `IN` elements is always the same):

Option 2 (if the number of `IN` elements is changing):