I need to submit the following query through my R code:

select t."date"
from db.table t
where t."date" > '2016-01-01';

To generate this quoted string properly, I tried:

sqlquery <- dbQuoteString(ANSI()
              , paste0("select t.", '"', "date", '"',"
                from db.table t
                where t.", '"',"date", '"'," > '2016-01-01';")
              )

Which output is:

<SQL> 'select t."date"
                from db.table t
                where t."date" > ''2016-01-01'';'

So I can use:

data <- DBI::dbGetQuery(con, sqlquery)

However, there are double '' instead of a single one ' (around 2016-01-01). How do I overcome that?

CodePudding user response：

Several layers to this onion.

1. If you need to quote something, use dQuote or sQuote, they handle both the beginning and end for you. For instance,

   dQuote("date")
   # [1] "\"date\""
   

2. The use of dbQuoteString is quoting your whole query as if it is a string literal. Note the ' before select, indicating that it is a string literal surrounding in single quotes (a common SQL way of delineating string literals). You could also just as easily written

   dbQuoteString(ANSI(), "Four score and seven years ago ... said by \"Lincoln\" in '1863'")
   # <SQL> 'Four score and seven years ago ... said by "Lincoln" in ''1863'''
   

   The reason you see '' is because it is trying to escape the single quotes that SQL uses to surround string literals. This produces a string, not something that can be executed as a query. In fact, dbQuoteString is something you should be using for your original query, instead of literal quotes and/or my dquote in point 1:

   DBI::SQL(paste("select t.", DBI::dbQuoteIdentifier(DBI::ANSI(), "date"), "from db.table t where t.", DBI::dbQuoteIdentifier(DBI::ANSI(), "date"), ">", DBI::dbQuoteString(DBI::ANSI(), "2016-01-01")))
   # <SQL> select t. "date" from db.table t where t. "date" > '2016-01-01'
   

   (Admittedly, DBI::SQL is not strictly necessary here, it's really just a character string with a wrapper that makes it print more prettily on the R console.)

3. Consider not manually encoding data and such into your queries, minor mistakes break queries (or possibly worse, though unlikely in the general case). I strongly urge you to read through https://solutions.rstudio.com/db/best-practices/run-queries-safely/. There, they identify the use of dbQuote* functions as "Manual escaping", which is the last method recommended (least-preferred while still somewhat safe). Other options include:

   • Parameterized queries, using ? placeholders for data in the query.

     ret <- DBI::dbGetQuery(con, 'select t."date" from db.table where "date" > ?', params = list("2016-01-01"))
     

   • Use glue::glue_sql:

     mydate <- "2016-01-01"
     sql <- glue::glue_sql(.con = con, 'select t."date" from db.table where "date" > {mydate}')
     sql
     # <SQL> select t."date" from db.table where "date" > '2016-01-01'
     ret <- DBI::dbGetQuery(con, sql)
     

   • Use DBI::sqlInterpolate:

     sql <- DBI::sqlInterpolate(con, 'select t."date" from db.table where "date" > ?mydate', mydate = "2016-01-01")
     sql
     # <SQL> select t."date" from db.table where "date" > '2016-01-01'
     ret <- DBI::dbGetQuery(con, sql)