Safely parametrize WHERE ... IN lists for SQL queries in R-CodePudding

library(DBI)
library(RSQLite)
library(dplyr)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
iris_id <- iris |>
  mutate(id = row_number())
dbWriteTable(con, "iris_id", iris_id)

params <- list(id = c(5,6,7))
q <- "SELECT COUNT(*) FROM iris_id WHERE id IN ($id)"
res <- dbSendQuery(con, q)
dbBind(res, params)
dbFetch(res)

As per documentation, this performs the query once per entry in params$id and returns c(1,1,1).

This also doesn't work, because this query is actually WHERE id IN ('5,6,7'):

id <- c(5L,6L,7L)
stopifnot(is.integer(id))
params <- list(id = paste(id, collapse=","))
res <- dbSendQuery(con, q)
dbBind(res, params)
dbFetch(res)

The answer to question [0] suggests using positional ? and pasting a list of ? together. However, this loses the possibility of using named params, which would be beneficial if I have multiple parameters. Is there another way?

[0] Passing DataFrame column into WHERE clause in SQL query embedded in R via parametrized queries

CodePudding user response：

One solution would be to pass all the ids as a comma separated string (no spaces) and use instead of IN the operator LIKE:

params <- list(id = "5,6,7")
q <- "SELECT COUNT(*) FROM iris_id WHERE ',' || $id || ',' LIKE '%,' || id || ',%'"
res <- dbSendQuery(con, q) 
dbBind(res, params)
dbFetch(res)

CodePudding user response：

library(dbplyr)
translate_sql(id %in% c(4L,5L,6L))