Call these methods to generate a string that is suitable for use in a query as a literal value of the correct type, to make sure that you generate valid SQL and protect against SQL injection attacks.
Methods in other packages
This documentation page describes the generics. Refer to the documentation pages linked below for the documentation for the methods that are implemented in various backend packages.
duckdb::dbQuoteLiteral("duckdb_connection", "ANY")pool::dbQuoteLiteral("Pool", "ANY")RMariaDB::dbQuoteLiteral("MariaDBConnection", "ANY")RPostgres::dbQuoteLiteral("PqConnection", "ANY")RPresto::dbQuoteLiteral("PrestoConnection", "ANY")sparklyr::dbQuoteLiteral("spark_connection", "ANY")
Arguments
- conn
A DBIConnection object, as returned by
dbConnect().- x
A vector to quote as string.
- ...
Other arguments passed on to methods.
Value
dbQuoteLiteral() returns an object that can be coerced to character,
of the same length as the input.
For an empty
integer,
numeric,
character,
logical,
date,
time,
or blob vector,
this function returns a length-0 object.
When passing the returned object again to dbQuoteLiteral()
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.)
Specification
The returned expression can be used in a SELECT ... query,
and the value of
dbGetQuery(paste0("SELECT ", dbQuoteLiteral(x)))[[1]]
must be equal to x
for any scalar
integer,
numeric,
string,
and logical.
If x is NA, the result must merely satisfy is.na().
The literals "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(),
dbQuoteString()
Examples
# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteLiteral(ANSI(), name)
#> <SQL> 'Robert''); DROP TABLE Students;--'
# NAs become NULL
dbQuoteLiteral(ANSI(), c(1:3, NA))
#> <SQL> 1
#> <SQL> 2
#> <SQL> 3
#> <SQL> NULL
# Logicals become integers by default
dbQuoteLiteral(ANSI(), c(TRUE, FALSE, NA))
#> <SQL> 1
#> <SQL> 0
#> <SQL> NULL
# Raw vectors become hex strings by default
dbQuoteLiteral(ANSI(), list(as.raw(1:3), NULL))
#> <SQL> X'010203'
#> <SQL> NULL
# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name
#> <SQL> select
dbQuoteLiteral(ANSI(), var_name)
#> <SQL> select
# This mechanism is used to prevent double escaping
dbQuoteLiteral(ANSI(), dbQuoteLiteral(ANSI(), name))
#> <SQL> 'Robert''); DROP TABLE Students;--'