How to use SQL parameters in an R markdown SQL chunk

Question

What's the right way to include parameters in an Rmd SQL code chunk? Here's an example Rmd file:

---
title: "Rmd Example"
output:
  html_document:
    df_print: paged
  pdf_document: default
---
`r Sys.Date()`
This is an example of an Rmd file.
```{r}
library(tidyverse)
library(DBI)
library(odbc)
library(knitr)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
                dsn = "myDsn", 
                uid = "myUsername", 
                pwd = "myPassword")
d1=as.Date("2021-03-15")
```
Look for all encounters after `r d1`
```{sql, connection=con, output.var = "encounters"}
select *
from encounters
where date>?d1
```
There are `r nrow(encounters)` after `r d1` and they are
```{r}
kable(encounters)
```
This ends the document.

The ?d1 part in the SQL code chunk doesn't work because it's not a string. Pretend I'm getting d1 from some other source, it is something that can't be hard-coded into the SQL query itself. The normal way to interact with databases would be to parameterize this value, which as I understand it does two pretty important things:

1. ensure the parameter value has the correct datatype, and
2. prevent SQL injection by properly escaping characters

R does have the ability to support parameterization, for example

library(tidyverse)
library(DBI)
library(odbc)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
    dsn = "myDSN",
    uid = "myUsername", 
    pwd = "myPassword")
d1=as.Date("2021-03-15")
query="select * from encounters where date>:d1"
encounters=dbGetQuery(con,query,params=d1)

This R code runs fine. How can I get the R markdown SQL code chunk to accept an SQL parameter like this? The ?d1 syntax apparently only inserts text, it doesn't handle data types, and although the documentation isn't clear I'm doubtful it is properly escaping characters either.

To be clear I'm not looking for a funky hack to get the code to run. I can use straight R code instead of the SQL chunk--losing the syntax highlighting would be preferable to losing correct evaluation of parameters. I'm looking for the correct way to put SQL parameters in an SQL chunk.

Answer 1

Maybe this is a solution for you, using glue_sql, it works on my computer

https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/13

Rmd chunks:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

  SELECT * FROM mtcars
  WHERE cyl IN (?cyl_sql)
  LIMIT 3

For a date column, you may have to format it correctly according to your database or use to_date(etc, '%d%...') Here in sqlite it's easy with ISO-8601 dates, an example:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
mtcars$DATE_1 <- seq(from = Sys.Date() - 15, to = Sys.Date() + nrow(mtcars) -16, by = 1) %>% as.character()
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

d_date_sql <- glue_sql("{Sys.Date()*}", .con = con)

Note that you have to specify .con parameter for character parameters, as said here

  SELECT cyl, mpg, DATE_1 FROM mtcars
  WHERE DATE_1 >= ?d_date_sql
  ORDER BY DATE_1