| dbQuoteString {DBI} | R Documentation |
Quote literal strings
Description
Call this method to generate a string that is suitable for use in a query as a string literal, to make sure that you generate valid SQL and protect against SQL injection attacks.
Usage
dbQuoteString(conn, x, ...)
Arguments
conn |
A DBI::DBIConnection object,
as returned by |
x |
A character vector to quote as string. |
... |
Other arguments passed on to methods. |
Value
dbQuoteString() returns an object that can be coerced to character,
of the same length as the input.
For an empty character vector this function returns a length-0 object.
When passing the returned object again to dbQuoteString()
as x
argument, it is returned unchanged.
Passing objects of class DBI::SQL should also return them unchanged.
(For backends it may be most convenient to return DBI::SQL objects
to achieve this behavior, but this is not required.)
Failure modes
Passing a numeric,
integer,
logical,
or raw vector,
or a list
for the x argument raises an error.
Specification
The returned expression can be used in a SELECT ... query,
and for any scalar character x the value of
dbGetQuery(paste0("SELECT ", dbQuoteString(x)))[[1]]
must be identical to x,
even if x contains
spaces,
tabs,
quotes (single
or double),
backticks,
or newlines
(in any combination)
or is itself the result of a dbQuoteString() call coerced back to
character (even repeatedly).
If x is NA, the result must merely satisfy is.na().
The strings "NA" or "NULL" are not treated specially.
NA should be translated to an unquoted SQL NULL,
so that the query SELECT * FROM (SELECT 1) a WHERE ... IS NULL
returns one row.
See Also
Other DBIResult generics:
DBIResult-class,
dbBind(),
dbClearResult(),
dbColumnInfo(),
dbFetch(),
dbGetInfo(),
dbGetRowCount(),
dbGetRowsAffected(),
dbGetStatement(),
dbHasCompleted(),
dbIsReadOnly(),
dbIsValid(),
dbQuoteLiteral()
Examples
# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteString(ANSI(), name)
# NAs become NULL
dbQuoteString(ANSI(), c("x", NA))
# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name
dbQuoteString(ANSI(), var_name)
# This mechanism is used to prevent double escaping
dbQuoteString(ANSI(), dbQuoteString(ANSI(), name))