在R DBI中传递参数以进行查询的正确方法 [英] Proper way to pass parameters to query in R DBI

问题描述

在perl/python中，DBI API具有一种可以安全地将参数插值到sql查询的机制.例如，在python中，我会这样做:

In perl/python DBI APIs have a mechanism to safely interpolate in parameters to an sql query. For example in python I would do:

cursor.execute("SELECT * FROM table WHERE value > ?", (5,))    

execute方法的第二个参数是要添加到sql查询中的参数的元组

Where the second parameter to the execute method is a tuple of parameters to add into the sql query

R的DBI兼容API是否有类似的机制?我见过的示例从不显示传递给查询的参数.如果不是，那么将参数插值到查询中最安全的方法是什么?我正在专门研究使用RPostgresSQL.

Is there a similar mechanism for R's DBI compliant APIs? The examples I've seen never show parameters passed to the query. If not, what is the safest way to interpolate in parameters to a query? I'm specifically looking at using RPostgresSQL.

推荐答案

出于完整性考虑，我将基于Hadley的评论添加一个答案. DBI软件包现在具有功能sqlInterpolate，该功能也可以执行此操作.它要求在sql查询中要命名的函数参数列表，所有参数都必须以?开头.摘录自以下 DBI手册

Just for completeness, I'll add an answer based on Hadley's comment. The DBI package now has the function sqlInterpolate which can also perform this. It requires a list of function arguments to be named in the sql query that all must start with a ?. Excerpt from the DBI manual below

sql <- "SELECT * FROM X WHERE name = ?name"
sqlInterpolate(ANSI(), sql, name = "Hadley")
# This is safe because the single quote has been double escaped
sqlInterpolate(ANSI(), sql, name = "H'); DROP TABLE--;")

这篇关于在R DBI中传递参数以进行查询的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！