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