Parameterized queries with RODBC

Question

I have a variable in R that I would like to pass to a database. I could use paste like many suggest when reading Google results, but that is unsafe because of SQL injection vulnerabilities. I'd rather prefer something like this:

x <- 42
sqlQuery(db, 'SELECT Id, Name FROM People WHERE Age > ?;', bind=c(x))

Is it possible to use parameterized queries with RODBC? If not, is there an alternative library that supports them?

I'm using SQL Server, RODBC 1.3-6 and R 3.0.0.

Northumbria

It seems to be mentioned here as "placeholders" but I still couldn't find how to use them. – Northumbria

would cleaning the string before pasting it not accomplish the same thing? – Whinstone

Take a look at the RODBCext package – Singer

Answer 1

Mateusz Zoltak wrote RODBCext package in 2014 (based on work by Brian Ripley and Michael Lapsley):

conn = odbcConnect('MyDataSource')

sqlPrepare(conn, "SELECT * FROM myTable WHERE column = ?")
sqlExecute(conn, 'myValue')
sqlFetchMore(conn)

Source: http://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html

Burbage

Yes - this is a better solution. Proper parameterization reduces security issues (SQL Injection) and can improve performance due to cached query plans (only important if the query is executed a lot). As a better software Craftsman pattern - I recommend this kind of solution. – Hudgens