DBI (version 0.3.0)

SQL: SQL quoting.

Description

This set of classes and generics make it possible to flexibly deal with SQL escaping needs. By default, any user supplied input to a query should be escaped using either dbQuoteIdentifier or dbQuoteString depending on whether it refers to a table or variable name, or is a literal string.

Usage

SQL(x)

dbQuoteIdentifier(conn, x, ...)

dbQuoteString(conn, x, ...)

Arguments

x
A character vector to label as being escaped SQL.
conn
A subclass of DBIConnection, representing an active connection to an DBMS.
...
Other arguments passed on to methods. Not otherwise used.

Implementation notes

DBI provides default methods for SQL-92 compatible quoting. If the database uses a different convention, you will need to provide your own methods. Note that because of the way that S4 dispatch finds methods and because SQL inherits from character, if you implement (e.g.) a method for dbQuoteString(MyConnection, character), you will also need to implement dbQuoteString(MyConnection, SQL) - this should simply return x unchanged.

Details

The SQL class has associated SQL() constructor function. This class is used to prevent double escaping of SQL strings, and to make it possible to tell DBI functions that you've done the escaping yourself.

Examples

Run this code
# Create a subclass of DBI connection since it's virtual
MockConnection <- setClass("MockConnection", "DBIConnection")
conn <- MockConnection()

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

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

dbQuoteIdentifier(conn, var_name)
dbQuoteString(conn, var_name)

# This mechanism is used to prevent double escaping
dbQuoteString(conn, dbQuoteString(conn, name))

Run the code above in your browser using DataCamp Workspace