R Kiselev R Kiselev - 12 days ago 10
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

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))
Comments