Proper way to pass parameters to query in R DBI

Question

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,))

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

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.

The latest version of DBI has `sqlInterpolate` which will safely interpolate variables into a string. — hadley, May 10 '16 at 12:37

score 8 · Answer 1 · answered May 11 '16 at 18:55

8

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--;")

answered May 11 '16 at 18:55

cts

1,790
1
13
27

2

What is `ANSI()` and is it possible to interpolate unsafely (for example, table name as a parameter)? – Pranasas Apr 10 '17 at 09:28
It's a function from the DBI package, where the documentation says that it is "a dummy DBI connector that simulates ANSI-SQL compliance". – cts Apr 10 '17 at 22:39
1

I also started a question regarding unsafe interpolation http://stackoverflow.com/questions/43385119/how-to-use-dynamic-values-while-executing-sql-scripts-in-r – Pranasas Apr 13 '17 at 11:15
how do you run the query generated in sqlInterpolate on the MySQL db? The standard R documentation on sqlInterpolate function is inadequate. – Lazarus Thurston Jun 18 '17 at 05:23
2

I just wanted to add that you don't need to put quotes around ?name e.g. don't do "?name"...that causes no interpolation to happen, just a debugging tip for anyone that is having trouble – Colin D Jul 20 '17 at 03:46

score 1 · Answer 2 · answered May 10 '16 at 15:38

Indeed the use of bind variables is not really well documented. Anyway the ODBC commands in R work differently for different databases. One possibility for postgres would be like this:

res <- postgresqlExecStatement(con, "SELECT * FROM table WHERE value > $1", c(5))
postgresqlFetch(res)
postgresqlCloseResult(res)

Hope it helps.

Proper way to pass parameters to query in R DBI

2 Answers2

Linked