R中拼接含LIKE的SQL WHERE IN语句出现参数不足错误的解决咨询

我用R的sprintf拼接带WHERE IN子句的SQL查询，仅筛选指定a_id时代码可正常执行：

ids <- c("0000000000000000010000001", "0000000000000000010000002", 
         "0000000000000000010000003", "0000000000000000010000004", 
         "0000000000000000010000005", "0000000000000000010000006", 
         "0000000000000000010000007", "0000000000000000010000008", 
         "0000000000000000010000009", "0000000000000000010000010")

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s)", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

但添加FSAS.placement LIKE 'FR%'筛选条件后，执行报错too few arguments：

ids <- c("0000000000000000010000001", "0000000000000000010000002", 
         "0000000000000000010000003", "0000000000000000010000004", 
         "0000000000000000010000005", "0000000000000000010000006", 
         "0000000000000000010000007", "0000000000000000010000008", 
         "0000000000000000010000009", "0000000000000000010000010")

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s) and FSAS.placement LIKE 'FR%'", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

解决方法 #

方法1：转义LIKE语句中的%符号 #

报错核心原因是sprintf将LIKE 'FR%'里的单个%识别为格式化占位符，但你只提供了一个填充参数（paste0(...)），导致参数数量不匹配。在sprintf语法中，需要用%%表示一个实际的%符号，修改后的代码如下：

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s) and FSAS.placement LIKE 'FR%%'", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

方法2：使用参数化查询（更安全） #

直接拼接SQL存在SQL注入风险，推荐用参数化查询替代字符串拼接，既能避免格式化冲突，又能提升代码安全性。以DBI包为例：

library(DBI)

# 定义带占位符的查询语句
query <- "select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (?) AND FSAS.placement LIKE ?"

# 绑定参数并执行查询
result <- dbGetQuery(con, query, params = list(ids, "FR%"))

注：不同数据库驱动对IN子句的参数绑定支持可能有差异，部分驱动需要将IN的参数拆分为多个占位符，可根据使用的数据库调整。

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