How to use parameterized SQL with dplyr?

Question

I'm trying to execute a SQL query with dplyr on SQL Server:

tbl(con, sql(sqlQuery))

The query is generated dynamically using sprintf("SELECT ... WHERE a = '%s'). This is a bad practice because it can be abused for SQL injection, but I can't find any documentation or working example for parameterized queries in dplyr. Can it be done, how?

The connection (con) is using DBI, the odbc library, and the {SQL Server Native Client 11.0} ODBC driver:

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "{SQL Server Native Client 11.0}",

There is some documentation [here](https://db.rstudio.com/run-queries-safely/). — Scarabee, Aug 21 '17 at 20:51
I don't think that will work with the tbl function. In the options listed It seems only dbGetQuery would work with tbl, but dbGetQuery doesn't support parameters. (tbl wouldn't work with a series of function calls such as dbSendQuery/dbBind/dbFetch/dbClearResults). sqlInterpolate may work, but I was hoping for something that use the underlying SQL driver's syntax. — ckarras, Aug 24 '17 at 17:08
`sqlInterpolate` does work with `tbl`: `sqlQuery <- sqlInterpolate(con, "select count(*) from mytable where var = ?value", value = 10) ; tbl(con, sql(sqlQuery))`. I'm not sure I understand what you expect, could you give more details? — Scarabee, Aug 25 '17 at 12:40
I was looking for something that use the underlying driver's syntax for parameters, for example "@ParameterName" for SQL Server, to facilitate reusing existing queries. But I tested your example with sqlInterpolate and it can work if it's the only option, thanks — ckarras, Aug 25 '17 at 18:04
Now I understand, but I'm not aware of any good solution. You could add a `gsub("@", "?", .)` around your query but that's not very clean. — Scarabee, Aug 25 '17 at 20:36

score 0 · Answer 1 · answered Nov 14 '17 at 14:56

Depending on what you need your parameters for, based on your example it looks like maybe the WHERE statement, you can just define the parameter in R and then use it your dplyr verbs.

my_param <- "FILTER_VALUE" #create param
my_table <- tbl(con, "TABLE_NAME") #create ref tibble
my_table <- my_table %>% filter( a == my_param ) # filter by param
my_table <- my_table %>% collect() # execute query

Anything you assign in R can be referenced in filter or mutate.

How to use parameterized SQL with dplyr?

1 Answers1