Trying to insert R data frame as parameter in SQL query where clause but I get a SQL error.
This is my code:
df <- data.frame(X=c(12691683,12693928)) %>% paste0(collapse = ",")
dbGetQuery(con2,"
SELECT * FROM ORDERS WHERE ORDER_ID IN (", df, ")
")
With glue::glue_sql(), you could input multiple values for use in SQL IN statements by putting * at the end of the value and the values will be collapsed and quoted appropriately.
df <- data.frame(X = c(12691683, 12693928))
glue::glue_sql(
"SELECT * FROM ORDERS WHERE ORDER_ID IN ({df$X*})",
)
# <SQL> SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683, 12693928)
In base you could also use sprintf() to create the SQL query:
sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)",
paste(df$X, collapse = ','))
# [1] "SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683,12693928)"
Use sprintf and toString. No packages are used. We assume this is an internal application where security issues are not relevant.
df <- data.frame(X=c(12691683,12693928))
sql <- sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)", toString(df$X))
sql
## [1] "SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683, 12693928)"
A variation of this is to use with
sql <- with(df,
sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)", toString(X)))
sQuote or similar is not a robust work-around. The only way to be agnostic to all of this variation is to use bound parameters (dbi.r-dbi.org/reference/dbbind and db.rstudio.com/best-practices/run-queries-safely). It's not much extra work and it saves so much trouble down the road.
DBI. 2. When somebody comes to this and applies your code to their problem, they may not understand when quotes are and are not needed. I know because there are still frequent questions that use this technique here without quotes (and don't work). 3. I understand that many R users are not deploying code to thousands of consumers, but shiny is easy, and that deployment can easily break on simple things. 4. This isn't groupthink, it's a "best practice" of SQL, even in R. My point though is not to debate, it's to add a comment to your answer.glue_sql, though I find it much safer than paste, sprintf, and toString, if for no other reason than it properly quotes as required by the specific DBMS (not required with numbers, true). The reason I commented here was because of the mention of "security" as the only reason to worry about data binding: while that is the populous reason, it is not the only justification. I respect your work, even if we disagree on the value of the teaching moment here.
DBIsupports parameterized queries;RODBCdoes not (becauseRODBCexthas since been removed).