R 使用 dbBind 将参数传递给 SQL IN 子句(可能没有胶水包？)

Question

只是想知道是否可以使用 DBI 将参数传递给 SQL 查询 IN 子句？已尝试以下(以及许多变体，包括未命名参数)

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)

此链接，Parameterized Queries ，显示了一种使用胶水包的方法，但是，我想知道仅使用 DBI 是否可行。

谢谢。

注意，供引用，这里是使用胶水的方法:

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)

Answer 1

如果您想使用一个参数绑定(bind)未定义数量的实际值，请使用dbBind() SQL 的IN 子句 :你不能!

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.

这仅在您为 IN 子句的每个元素定义一个参数时有效，请参阅 SQLite 的语法图:

• https://www.sqlite.org/lang_select.html
• https://www.sqlite.org/lang_expr.html

选项 1(如果 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

选项 2(如果 IN 元素的数量发生变化):

您还可以计算实际参数的数量，并在 IN 子句中生成相同数量的查询参数，然后使用 dbSendQuery 准备 SQL 查询。这可以防止 SQL 代码注入(inject):

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

但这也意味着不要重用准备好的语句，如果这不是您想要的，则只有 SQL 语句的经典字符串连接:

选项 3:自己使用 SQL 字符串连接:

如果不使用 glue 包，您只能自己连接 SQL 字符串，如果(不良)用户可以输入参数值，则尽量降低 SQL 代码注入(inject)的风险。

您可以使用 DBI 中的 dbQuote* 函数(RSQLite 符合 DBI 接口(interface))...