RPostgreSQL-将R中的参数传递给RPostgreSQL中的查询

Question

问题：如何在RPostgreSQL查询中传递变量？

示例：在下面的示例中，我试图将日期"2018-01-03"传递给查询

library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(PostgreSQL(), 
dbname="test",
host="localhost",
port=5432,
user="user", 
password="...")
result <- dbGetQuery(connect,
"SELECT * FROM sales_tbl WHERE date = @{dt}")

Answer 1

您可以使用paste0生成查询并将其传递给dbGetQuery:

library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(PostgreSQL(), 
dbname="test",
host="localhost",
port=5432,
user="user", 
password="...")
query <- paste0("SELECT * FROM sales_tbl WHERE date='", dt, "'")
result <- dbGetQuery(connect, query)

Answer 2

最安全的方法是参数化这里提到的查询

示例：

library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(drv = PostgreSQL(), 
dbname ="test",
host = "localhost",
port = 5432,
user = "user", 
password = "...")
query <- "SELECT * FROM sales_tbl WHERE date= ?"
sanitized_query <- dbSendQuery(connect, query)
dbBind(sanitized_query, list(dt))
result <- dbFetch(sanitized_query)

在这里，通过传递?，您可以净化查询以避免SQL注入攻击。

我喜欢做的另一件事是创建.Renviron文件来存储我的credentials。例如，对于上面的连接，.Renviron文件将如下所示。

dbname = test
dbuser = me
dbpass = mypass
dbport = 5432
dbhost = localhost

保存文件，重新启动RStudio(在启动时加载.Renviron文件(。然后使用Sys.getenv(variable)访问凭据

#example:
connect <- dbConnect(drv = PostgreSQL(), 
dbname = Sys.getenv("dbname"),
host = Sys.getenv("dbhost"),
port = Sys.getenv("dbport"),
user = Sys.getenv("dbuser"), 
password = Sys.getenv("dbpass"))