Pass String Variable In R Script To Use It In Sql Statement

I tried use a string variable in R script to use through SQL statement for example: x='PASS' SQL<- paste('select ID, NAME, STATUS from STUDENT where STATUS =(',x,')',sep='') Q1

Solution 1:

Use sprintf instead:

x <- "PASS"sprintf("select ID, NAME, STATUS from STUDENT where STATUS = '%s'", x)

## [1] "select ID, NAME, STATUS from STUDENT where STATUS = 'PASS'"

Solution 2:

Try this:

library(gsubfn)
x <- "PASS"

fn$dbGetQuery(con, "select ID, NAME, STATUS from STUDENT where STATUS = '$x' ")

This also works:

s <- fn$identity("select ID, NAME, STATUS from STUDENT where STATUS = '$x' ")
dbGetQuery(con, s)

Solution 3:

EDIT for windows

Try

x = "PASS"

SQL<- paste0("select ID, NAME, STATUS from STUDENT where STATUS = ", shQuote(x, 'sh'))
Q1 <- dbGetQuery(con, SQL)

More generally shQuote is useful for constructed things like:

paste0("SELECT * FROM urtable where urvar IN(", paste0(shQuote(LETTERS, 'sh'), collapse = ','), ")")
[1] "SELECT * FROM urtable where urvar IN('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')"#

If you have simple character strings. For more complicated character strings other approaches maybe necessary. For example in PoSTgreSQL you can use Dollar-Quoted String Constants to escape characters.

You dont mention what variant of SQL you are using or associated R package. Some R packages may have helper functions like postgresqlEscapeStrings in RPostgreSQL or dbEscapeStrings in RMySQL.

Solution 4:

Use glue_sql() from glue package.

x = "PASS"

glue_sql("select ID, NAME, STATUS from STUDENT where STATUS = {x}", .con = con)

see more examples here: https://glue.tidyverse.org/#glue_sql-makes-constructing-sql-statements-safe-and-easy