Properly quoting SQL query with " and ' in R

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?

Solution

Several layers to this onion.

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\""
```
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.)

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)