4

I am trying to generate a bunch of SQL scripts using DBI::sqlInterpolate function but constantly get SQL error as the script embeds quotes that are returned with the R variable.

Here is the code:

> x<-'state_transtions'
> y<-'transition_time'
> script<-"select * from ?x  WHERE DATE(?y)> DATE_SUB(NOW(), INTERVAL 1 DAY)"
> sqlInterpolate(ANSI(),script,x=x,y=y)
#<SQL> select * from 'state_transtions'  WHERE DATE('transition_time')> DATE_SUB(NOW(), INTERVAL 1 DAY)

As you see I am generating the desired SQL table names and column names through R code. Hence the injection values (?x, ?y) are passed as variables.

I looked up this link which was closest to find me a solution but honestly, I do not understand it. [https://rstats-db.github.io/DBI/reference/sqlParseVariables.html#examples]

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Lazarus Thurston
  • 1,197
  • 15
  • 33

1 Answers1

2

1) gsubfn gsubfn in the gsubfn package can do substitutions. The regular expression here matches a question mark followed by word characters and then it uses the correspondences defined in the second argument to perform the substitutions on the part of the regular expression matched by the part within parentheses.

library(gsubfn)

gsubfn("[?](\\w+)", list(x = x, y = y), script)

giving the following:

[1] "select * from state_transtions  WHERE DATE(transition_time)> DATE_SUB(NOW(), INTERVAL 1 DAY)"

2) fn$ The gsubfn package also provides fn$ which can prefix any function and will perform string interpolation on its arguments giving the same result. identity could be replaced with any other suitable R function.

It replaces $x (where x can be any name consisting only of letters and numbers) in the string with the contents of the variable named x.

library(gsubfn)

script2 <- "select * from $x  WHERE DATE($y)> DATE_SUB(NOW(), INTERVAL 1 DAY)"
fn$identity(script2)

The gsubfn package is automatically loaded by sqldf and is frequently used with it so, for example,

library(sqldf)

var <- "Time"
fn$sqldf("select $var from BOD where $var > `mean(1:7)`")

giving:

  Time
1    5
2    7

3) sprintf It is also possible to do this without any packages just using sprintf

sprintf("select * from %s  WHERE DATE(%s)> DATE_SUB(NOW(), INTERVAL 1 DAY)", x, y)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341