Quote identifiers

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 dbUnquoteIdentifier().

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::dbQuoteIdentifier("AdbiConnection", "character")

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

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

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

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

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

• duckdb::dbQuoteIdentifier("duckdb_connection", "ANY")

• duckdb::dbQuoteIdentifier("duckdb_connection", "character")

• duckdb::dbQuoteIdentifier("duckdb_connection", "Id")

• duckdb::dbQuoteIdentifier("duckdb_connection", "SQL")

• implyr::dbQuoteIdentifier("impala_connection", "character")

• implyr::dbQuoteIdentifier("impala_connection", "dbplyr_table_ident")

• odbc::dbQuoteIdentifier("OdbcConnection", "character")

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

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

• RMariaDB::dbQuoteIdentifier("MariaDBConnection", "Id")

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

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

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

• RPostgres::dbQuoteIdentifier("PqConnection", "Id")

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

• RPresto::dbQuoteIdentifier("PrestoConnection", "dbplyr_schema")

• RSQLite::dbQuoteIdentifier("SQLiteConnection", "character")

• RSQLite::dbQuoteIdentifier("SQLiteConnection", "SQL")

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

Usage

dbQuoteIdentifier(conn, x, ...)

Arguments

conn

A DBIConnection object, as returned by dbConnect().

x

A character vector, SQL or Id object to quote as identifier.

...

Other arguments passed on to methods.

Value

dbQuoteIdentifier() 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. The names of the input argument are preserved in the output. When passing the returned object again to dbQuoteIdentifier() 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

An error is raised if the input contains NA, but not for an empty string.

Specification

Calling DBI::dbGetQuery() for a query of the format 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 SELECT 1 AS ... and 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 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 dbQuoteIdentifier().

See also

Other DBIConnection generics: DBIConnection-class, dbAppendTable(), dbAppendTableArrow(), dbCreateTable(), dbCreateTableArrow(), dbDataType(), dbDisconnect(), dbExecute(), dbExistsTable(), dbGetException(), dbGetInfo(), dbGetQuery(), dbGetQueryArrow(), dbIsReadOnly(), dbIsValid(), dbListFields(), dbListObjects(), dbListResults(), dbListTables(), dbReadTable(), dbReadTableArrow(), dbRemoveTable(), dbSendQuery(), dbSendQueryArrow(), dbSendStatement(), dbUnquoteIdentifier(), dbWriteTable(), dbWriteTableArrow()

Examples

# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteIdentifier(ANSI(), name)
#> <SQL> "Robert'); DROP TABLE Students;--"

# Use Id() to specify other components such as the schema
id_name <- Id(schema = "schema_name", table = "table_name")
id_name
#> <Id> "schema_name"."table_name"
dbQuoteIdentifier(ANSI(), id_name)
#> <SQL> "schema_name"."table_name"

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

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