在R中安全参数化SQL查询的WHERE IN列表并保留命名参数

问题描述 #

尝试用命名参数执行带IN子句的参数化查询时，会遇到不符合预期的结果：

原始写法（返回非预期结果） #

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)

上述代码会对params$id中的每个条目单独执行一次查询，最终返回c(1,1,1)，而非预期的符合条件的总条数。

拼接字符串的无效写法 #

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

这种写法会生成WHERE id IN ('5,6,7')的SQL语句，相当于匹配字符串'5,6,7'而非数字列表，无法得到正确结果。

现有方案（使用位置占位符?并拼接多个?）会失去命名参数的便利性，尤其在存在多个参数时更为明显。以下是几种替代解决方案：

解决方案 #

方案1：动态生成命名参数占位符 #

根据目标id的数量生成对应数量的命名占位符，既保留命名参数的特性，又避免SQL注入风险：

# 目标id列表
target_ids <- c(5,6,7)
# 生成对应数量的命名占位符（如$id1, $id2, $id3）
placeholders <- paste0("$id", seq_along(target_ids))
# 拼接SQL语句
q <- sprintf("SELECT COUNT(*) FROM iris_id WHERE id IN (%s)", paste(placeholders, collapse = ", "))
# 构建命名参数列表
params <- setNames(as.list(target_ids), paste0("id", seq_along(target_ids)))
# 执行查询
res <- dbSendQuery(con, q)
dbBind(res, params)
result <- dbFetch(res)
dbClearResult(res)
print(result)

该方法可与其他命名参数结合使用，适合多参数场景。

方案2：使用dbplyr接口（简洁高效） #

借助dbplyr可以用R语法直接编写查询，它会自动处理参数化的IN子句转换：

library(dbplyr)

# 直接用dbplyr编写查询
result <- tbl(con, "iris_id") |>
  filter(id %in% target_ids) |>
  tally() |>
  collect()

print(result)

这种方式无需手动拼接SQL，代码可读性高，同时自动保障参数化查询的安全性。

方案3：利用临时表关联查询（适合大列表） #

如果数据库支持临时表（如SQLite），可将id列表写入临时表后关联查询，适合处理超长id列表：

# 将id列表转为数据框
params_df <- data.frame(id = target_ids)
# 写入临时表
dbWriteTable(con, "temp_ids", params_df, temporary = TRUE)
# 执行关联查询
q <- "SELECT COUNT(*) FROM iris_id i JOIN temp_ids t ON i.id = t.id"
res <- dbSendQuery(con, q)
result <- dbFetch(res)
dbClearResult(res)
print(result)

该方法避免了大量占位符的生成，同时保持查询的高效性。

内容的提问来源于stack exchange，提问作者meow