Quote literal strings

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.

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.

• adbi::dbQuoteString("AdbiConnection", "character")

• bigrquery::dbQuoteString("BigQueryConnection", "character")

• bigrquery::dbQuoteString("BigQueryConnection", "SQL")

• DatabaseConnector::dbQuoteString("DatabaseConnectorConnection", "character")

• dittodb::dbQuoteString("DBIMockMariaDBConnection", "character")

• dittodb::dbQuoteString("DBIMockMariaDBConnection", "SQL")

• dittodb::dbQuoteString("DBIMockRPostgresConnection", "character")

• dittodb::dbQuoteString("DBIMockRPostgresConnection", "SQL")

• odbc::dbQuoteString("Hive", "character")

• pool::dbQuoteString("Pool", "ANY")

• RAthena::dbQuoteString("AthenaConnection", "character")

• RAthena::dbQuoteString("AthenaConnection", "Date")

• RAthena::dbQuoteString("AthenaConnection", "POSIXct")

• RMariaDB::dbQuoteString("MariaDBConnection", "character")

• RMariaDB::dbQuoteString("MariaDBConnection", "SQL")

• RMySQL::dbQuoteString("MySQLConnection", "character")

• RMySQL::dbQuoteString("MySQLConnection", "SQL")

• RPostgres::dbQuoteString("PqConnection", "character")

• RPostgres::dbQuoteString("PqConnection", "SQL")

• sparklyr::dbQuoteString("spark_connection", "character")

Usage

dbQuoteString(conn, x, ...)

Arguments

conn

A DBI::DBIConnection object, as returned by dbConnect().

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)
#> <SQL> 'Robert''); DROP TABLE Students;--'

# NAs become NULL
dbQuoteString(ANSI(), c("x", NA))
#> <SQL> 'x'
#> <SQL> NULL

# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name
#> <SQL> select
dbQuoteString(ANSI(), var_name)
#> <SQL> select

# This mechanism is used to prevent double escaping
dbQuoteString(ANSI(), dbQuoteString(ANSI(), name))
#> <SQL> 'Robert''); DROP TABLE Students;--'