Safely interpolate values into an SQL string

Safely interpolate values into an SQL string

sqlInterpolate(conn, sql, ..., .dots = list())

Arguments

conn	A database connection.
sql	A SQL string containing variables to interpolate. Variables must start with a question mark and can be any valid R identifier, i.e. it must start with a letter or ., and be followed by a letter, digit, . or _.
..., .dots	Named values (for ...) or a named list (for .dots) to interpolate into a string. All strings will be first escaped with dbQuoteString() prior to interpolation to protect against SQL injection attacks.

Backend authors

If you are implementing an SQL backend with non-ANSI quoting rules, you'll need to implement a method for sqlParseVariables(). Failure to do so does not expose you to SQL injection attacks, but will (rarely) result in errors matching supplied and interpolated variables.

Examples

sql <- "SELECT * FROM X WHERE name = ?name"
sqlInterpolate(ANSI(), sql, name = "Hadley")
#> <SQL> SELECT * FROM X WHERE name = 'Hadley'

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