2.3.1 Manually checking data types

You can escape your data manually, e.g.

• cast numeric columns to numbers using as.numeric(column) or sprintf(“%d %f”, integerColumn, realColumn),
• cast dates using as.character(as.Date(column)),
• escape strings using gsub(“‘“,”’’”, column),
• etc.

This is possible but is also very error prone, especially when escaping string values. Everyone knows that apostrophes have to be escaped, but:

• Different database systems may use different escape sequences (e.g. C-style with a backslash or repeat-style a with double apostrophe).
• our database system may handle HTML/XML entities or inserting characters by a Unicode value (or many, many other strange ways of data input), so e.g. my’value or my\U0027value will be converted into my’value and then lead to errors in your query.

It is almost impossible to remember all caveats by yourself, so it is strongly advised not to use this method.

2.3.2 Using parameterized queries

Another solution is to separate the query string from its parameters (data). In such case a query execution is divided into two steps:

• query parsing and planing,
• passing parameter values to query and query execution.

As query parameters are passed separately, parameter values cannot modify (and break) the query string. To indicate places in the query where parameters will be placed, a special character is used, typically a question mark.

Let us rewrite our cakes example using the sqlExecute(connHandle, queryString, data) function from the RODBCext package:

We replaced the parameter values in query with a question mark and passed query and data as separate function parameters. We made our code not only SQL injection resistant, but also easier to read.

Moreover, the function function sqlExecute() supports vectorized data, so we can make it even simpler:

2.3.3 Using high-level functions which deal with escaping values for us

This would be the most straightforward solution.

An excellent example is dplyr, which provides a complete R to SQL mapper and allows us to completely forget about the SQL. Another example are the sqlSave(), sqlUpdate(), sqlCopy() and sqlCopyTable() functions from the RODBC package which deal with escaping values for us.

The problem is that: * Dplyr escapes values rather naively. With respect to strings only simple ‘to’’ escaping is performed which is enough to prevent silly errors but will fail against more advanced SQL injections. * RODBC’s high-level functions escape values in a safe way (by internally using parameterized queries), but have very limited functionality. Interestingly, judging from the comments in the source code, the parameterized queries have been introduced to them not to make them safe but to improve speed.

4.1.1 sqlExecute()

Allows execution of SQL queries separated from query parameters values, e.g.:

The nice thing is that sqlExecute() (in opposite to sqlQuery()) supports vectorization. In the example below data will contain results of all five queries bound by rows.

Results can be also fetched separately using RODBC’s sqlGetResults(). This also provides a way to fetch results in parts:

As sqlExecute() uses internally sqlGetResults() to fetch results of the query, it also accept all parameters of the sqlGetResults():

4.1.2 sqlPrepare()

Parses a query string and plans a query. Query can be executed later using sqlExecute() with a parameter query set too NULL. This can provide some performance gain when executing the same query multiple times (see the chapter Speeding up query execution using parameterized queries). Usage example: