2

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.

R Kiselev
  • 1,124
  • 1
  • 9
  • 18
  • Why are you reading `psycopg2` docs which is a Postgres API when you are using SQLite? – Parfait Nov 22 '16 at 16:34
  • Possible duplicate of [Parameterized queries with RODBC](http://stackoverflow.com/questions/16178640/parameterized-queries-with-rodbc). See also [How to write parameterized sql query in R programming using Rstudio IDE](http://stackoverflow.com/questions/26754346/how-to-write-parameterized-sql-query-in-r-programming-using-rstudio-ide) – Serban Tanasa Nov 22 '16 at 16:37
  • I didn't know the `paste` command represented a security concern... – cory Nov 22 '16 at 16:52
  • @cory google "Bobby Tables". – Hong Ooi Nov 22 '16 at 17:11
  • @HongOoi Right, I know what SQL injection is. That's usually not that big of a concern in the R world as SQL queries aren't usually done interactively by random members of the public, they are done in your own script ran locally. So the question should ask how to sanitize inputs in an sql query or something that doesn't make it seem like this guy is bashing R users as careless and inelegant. – cory Nov 22 '16 at 18:17
  • @Parfait, just because I work simultaneously with Postgres and SQLite in the same project. – R Kiselev Nov 22 '16 at 19:50

1 Answers1

2

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))
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • 1
    Oh, thank you @Parfait, that is exactly what I was looking for! I know this doc and I actually used search to find the the word "parameter" inside, but they call this procedure "SQL preparation", so I did't find it. – R Kiselev Nov 22 '16 at 19:55