Pass string variable in R script to use it in SQL statement

Question

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 <- dbGetQuery(con, SQL)

The ERROR says:

Error in mysqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not run statement: Unknown column 'PASS' in 'where clause')

That means STATUS =(",x,")" = PASS and it must 'PASS' with add quote ''

I tried to put the '' but no success as the following.

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

I tested it with number and it is work well but when I use string it is not working, because the value must be in quotes ' '.

Jaap · Accepted Answer · 2016-06-22 07:06:01Z

8

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'"

edited Jun 22, 2016 at 7:06

Jaap

83.7k36 gold badges190 silver badges203 bronze badges

answered Jul 2, 2013 at 20:20

dickoa

18.5k3 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

linux Over a year ago

Thanks dickoa I tried with sprintf and it is not work, in my case I use other variables type which contain integer. The update of my case is: x="PASS" Y=1100 SQL<- paste("select ID, C_ID, NAME, STATUS from STUDENT where C_ID=(",Y,")", and STATUS =(",x,")",sep="") the previous case can deal Y but can not work with x Please could help and thanks in advance

G. Grothendieck · Accepted Answer · 2013-07-03 01:49:10Z

5

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)

edited Jul 3, 2013 at 1:49

answered Jul 2, 2013 at 21:31

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

1 Comment

linux Over a year ago

Thanks G. Grothendieck, I tried it and it is work well with diffrent type of Variable.. and I would also to thanks all who reply..

user1609452 · Accepted Answer · 2013-07-03 03:29:12Z

2

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.

edited Jul 3, 2013 at 3:29

answered Jul 2, 2013 at 20:20

user1609452

4,4641 gold badge17 silver badges20 bronze badges

2 Comments

user1609452 Over a year ago

You can keep the brackets or remove them.

Hong Ooi Over a year ago

This seems like a misuse of shQuote, which is meant for quoting strings for the OS shell. There's no guarantee that the same quotes are what a database would expect.

Zaki · Accepted Answer · 2020-09-13 20:05:40Z

0

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

answered Sep 13, 2020 at 20:05

Zaki

1413 bronze badges

Collectives™ on Stack Overflow

Pass string variable in R script to use it in SQL statement

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related