sqlInterpolate | R Documentation |
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.
sqlInterpolate(conn, sql, ..., .dots = list())
conn |
A DBIConnection object, as returned by
|
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 |
... , .dots |
Values (for |
The sql
query with the values from ...
and .dots
safely
embedded.
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.
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)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.