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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/dbQuoteIdentifier.R
\name{dbQuoteIdentifier}
\alias{dbQuoteIdentifier}
\title{Quote identifiers}
\usage{
dbQuoteIdentifier(conn, x, ...)
}
\arguments{
\item{conn}{A \linkS4class{DBIConnection} object, as returned by
\code{\link[=dbConnect]{dbConnect()}}.}
\item{x}{A character vector, \link{SQL} or \link{Id} object to quote as identifier.}
\item{...}{Other arguments passed on to methods.}
}
\value{
\code{dbQuoteIdentifier()} returns an object that can be coerced to \link{character},
of the same length as the input.
For an empty character vector this function returns a length-0 object.
The names of the input argument are preserved in the output.
When passing the returned object again to \code{dbQuoteIdentifier()}
as \code{x}
argument, it is returned unchanged.
Passing objects of class \link{SQL} should also return them unchanged.
(For backends it may be most convenient to return \link{SQL} objects
to achieve this behavior, but this is not required.)
}
\description{
Call this method to generate a string that is suitable for
use in a query as a column or table name, to make sure that you
generate valid SQL and protect against SQL injection attacks. The inverse
operation is \code{\link[=dbUnquoteIdentifier]{dbUnquoteIdentifier()}}.
\Sexpr[results=rd,stage=render]{DBI:::methods_as_rd("dbQuoteIdentifier")}
}
\section{Failure modes}{
An error is raised if the input contains \code{NA},
but not for an empty string.
}
\section{Specification}{
Calling \code{\link[=dbGetQuery]{dbGetQuery()}} for a query of the format \verb{SELECT 1 AS ...}
returns a data frame with the identifier, unquoted, as column name.
Quoted identifiers can be used as table and column names in SQL queries,
in particular in queries like \verb{SELECT 1 AS ...}
and \verb{SELECT * FROM (SELECT 1) ...}.
The method must use a quoting mechanism that is unambiguously different
from the quoting mechanism used for strings, so that a query like
\verb{SELECT ... FROM (SELECT 1 AS ...)}
throws an error if the column names do not match.
The method can quote column names that
contain special characters such as a space,
a dot,
a comma,
or quotes used to mark strings
or identifiers,
if the database supports this.
In any case, checking the validity of the identifier
should be performed only when executing a query,
and not by \code{dbQuoteIdentifier()}.
}
\examples{
# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteIdentifier(ANSI(), name)
# Use Id() to specify other components such as the schema
id_name <- Id(schema = "schema_name", table = "table_name")
id_name
dbQuoteIdentifier(ANSI(), id_name)
# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name
dbQuoteIdentifier(ANSI(), var_name)
# This mechanism is used to prevent double escaping
dbQuoteIdentifier(ANSI(), dbQuoteIdentifier(ANSI(), name))
}
\seealso{
Other DBIConnection generics:
\code{\link{DBIConnection-class}},
\code{\link{dbAppendTable}()},
\code{\link{dbAppendTableArrow}()},
\code{\link{dbCreateTable}()},
\code{\link{dbCreateTableArrow}()},
\code{\link{dbDataType}()},
\code{\link{dbDisconnect}()},
\code{\link{dbExecute}()},
\code{\link{dbExistsTable}()},
\code{\link{dbGetException}()},
\code{\link{dbGetInfo}()},
\code{\link{dbGetQuery}()},
\code{\link{dbGetQueryArrow}()},
\code{\link{dbIsReadOnly}()},
\code{\link{dbIsValid}()},
\code{\link{dbListFields}()},
\code{\link{dbListObjects}()},
\code{\link{dbListResults}()},
\code{\link{dbListTables}()},
\code{\link{dbReadTable}()},
\code{\link{dbReadTableArrow}()},
\code{\link{dbRemoveTable}()},
\code{\link{dbSendQuery}()},
\code{\link{dbSendQueryArrow}()},
\code{\link{dbSendStatement}()},
\code{\link{dbUnquoteIdentifier}()},
\code{\link{dbWriteTable}()},
\code{\link{dbWriteTableArrow}()}
}
\concept{DBIConnection generics}
|
