Search code examples
rr-dbi

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

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

  • 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...