1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/sqlInterpolate.R
\name{sqlInterpolate}
\alias{sqlInterpolate}
\title{Safely interpolate values into an SQL string}
\usage{
sqlInterpolate(conn, sql, ..., .dots = list())
}
\arguments{
\item{conn}{A \linkS4class{DBIConnection} object, as returned by
\code{\link[=dbConnect]{dbConnect()}}.}
\item{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 \code{.}, and be followed
by a letter, digit, \code{.} or \verb{_}.}
\item{..., .dots}{Values (for \code{...}) or a list (for \code{.dots})
to interpolate into a string.
Names are required if \code{sql} uses the \code{?name} syntax for placeholders.
All values will be first escaped with \code{\link[=dbQuoteLiteral]{dbQuoteLiteral()}} prior
to interpolation to protect against SQL injection attacks.
Arguments created by \code{\link[=SQL]{SQL()}} or \code{\link[=dbQuoteIdentifier]{dbQuoteIdentifier()}} remain unchanged.}
}
\value{
The \code{sql} query with the values from \code{...} and \code{.dots} safely
embedded.
}
\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 \code{\link[=dbQuoteLiteral]{dbQuoteLiteral()}}
to protect against SQL injection attacks.
Placeholders can be specified with one of two syntaxes:
\itemize{
\item \verb{?}: each occurrence of a standalone \verb{?} is replaced with a value
\item \code{?name1}, \code{?name2}, ...: values are given as named arguments or a
named list, the names are used to match the values
}
Mixing \verb{?} and \code{?name} syntaxes is an error.
The number and names of values supplied must correspond to the placeholders
used in the query.
\Sexpr[results=rd,stage=render]{DBI:::methods_as_rd("sqlInterpolate")}
}
\section{Backend authors}{
If you are implementing an SQL backend with non-ANSI quoting rules, you'll
need to implement a method for \code{\link[=sqlParseVariables]{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)
}
}
|
