R Kiselev R Kiselev - 2 years ago 127
R Question

How to perform parameter substitution in SQL queries using R?

Programming SQL-specific stuff in

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
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
. I try to do this with the function
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
and then just feed it to
. 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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download