R Kiselev R Kiselev - 10 months ago 59
R Question

How to perform parameter substitution in SQL queries using R?

Programming SQL-specific stuff in

Python
I've got used to always use parameter substitution when executing plain SQL queries, like this:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)


In the
psycopg2
documentation they even write:


Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.


Now I have to save data into an SQLite database from
R
. I try to do this with the function
dbSendQuery
that accepts only two arguments: connection handler and the query itself. But how can I provide parameters for substitution?!

Googling around I have found (surprisingly!) that in the R community people always suggest to build an SQL query using
paste
and then just feed it to
dbSendQuery
. But what about security and elegance? It seems like nobody cares... I personally don't understand this.

Answer Source

With RSQLite, you can use dbGetPreparedQuery for parameterized queries (i.e., bind values to prepared statements). See docs. However, you must pass the binded value as a dataframe object:

sql <- "SELECT * FROM stocks WHERE symbol=?"

t <- 'RHAT'
df <- dbGetPreparedQuery(con, sql, bind.data=data.frame(symbol=t))