DBI: sqlInterpolate – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

sqlInterpolate

Safely interpolate values into an SQL string

Description

Accepts a query string with placeholders for values, and returns a string with the values embedded. The function is careful to quote all of its inputs with dbQuoteLiteral() to protect against SQL injection attacks.

Placeholders can be specified with one of two syntaxes:

?: each occurrence of a standalone ? is replaced with a value
?name1, ?name2, ...: values are given as named arguments or a named list, the names are used to match the values

Mixing ? and ?name syntaxes is an error. The number and names of values supplied must correspond to the placeholders used in the query.

Usage

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

Arguments

`conn`	A DBIConnection object, as returned by `dbConnect()`.
`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`	Values (for `...`) or a list (for `.dots`) to interpolate into a string. Names are required if `sql` uses the `?name` syntax for placeholders. All values will be first escaped with `dbQuoteLiteral()` prior to interpolation to protect against SQL injection attacks. Arguments created by `SQL()` or `dbQuoteIdentifier()` remain unchanged.

Value

The sql query with the values from ... and .dots safely embedded.

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

# This is safe because the single quote has been double escaped
sqlInterpolate(ANSI(), sql, name = "H'); DROP TABLE--;")

# Using paste0() could lead to dangerous SQL with carefully crafted inputs
# (SQL injection)
name <- "H'); DROP TABLE--;"
paste0("SELECT * FROM X WHERE name = '", name, "'")

# Use SQL() or dbQuoteIdentifier() to avoid escaping
sql2 <- "SELECT * FROM ?table WHERE name in ?names"
sqlInterpolate(ANSI(), sql2,
  table = dbQuoteIdentifier(ANSI(), "X"),
  names = SQL("('a', 'b')")
)

# Don't use SQL() to escape identifiers to avoid SQL injection
sqlInterpolate(ANSI(), sql2,
  table = SQL("X; DELETE FROM X; SELECT * FROM X"),
  names = SQL("('a', 'b')")
)

# Use dbGetQuery() or dbExecute() to process these queries:
if (requireNamespace("RSQLite", quietly = TRUE)) {
  con <- dbConnect(RSQLite::SQLite())
  sql <- "SELECT ?value AS value"
  query <- sqlInterpolate(con, sql, value = 3)
  print(dbGetQuery(con, query))
  dbDisconnect(con)
}

DBI

R Database Interface

v1.1.1

LGPL (>= 2.1)

Authors

R Special Interest Group on Databases (R-SIG-DB) [aut], Hadley Wickham [aut], Kirill Müller [aut, cre] (<https://orcid.org/0000-0002-1416-3412>), R Consortium [fnd]

Initial release

2021-01-04