RPostgreSQL - Passing Parameter in R to a Query in RPostgreSQL

Question

Question: How do I pass a variable in the RPostgreSQL query?

Example: In the example below I try to pass the date '2018-01-03' to the query

library(RPostgreSQL)

dt <- '2018-01-03'

connect <- dbConnect(PostgreSQL(), 
                 dbname="test",
                 host="localhost",
                 port=5432,
                 user="user", 
                 password="...")
result <- dbGetQuery(connect,
                "SELECT * FROM sales_tbl WHERE date = @{dt}")

See [here](https://stackoverflow.com/questions/37131569/proper-way-to-pass-parameters-to-query-in-r-dbi) and [here](https://stackoverflow.com/questions/20201221/how-to-use-parameters-with-rpostgresql-to-insert-data) — A. Suliman, Aug 02 '18 at 08:18

score 2 · Accepted Answer · answered Aug 02 '18 at 08:15

2

You can use paste0 to generate your query and pass it to dbGetQuery:

library(RPostgreSQL)

dt <- '2018-01-03'

connect <- dbConnect(PostgreSQL(), 
  dbname="test",
  host="localhost",
  port=5432,
  user="user", 
  password="...")

query <- paste0("SELECT * FROM sales_tbl WHERE date='", dt, "'")
result <- dbGetQuery(connect, query)

answered Aug 02 '18 at 08:15

Paul

2,877
1
12
28

Do you know how to handle the case when "dt" is a list of dates or values? e.g. '2018-01-03', '2017-01-03' and so forth. – Daniel Aug 03 '18 at 13:23
Try this: dt <- c('2018-01-03', '2017-01-04', '2017-01-04') *then* paste("SELECT * FROM sales_tbl WHERE date =", paste0("'", dt, "'", collapse = " OR date = ")) – Paul Aug 04 '18 at 04:41
8

This is vulnerable to SQL injection. An improvement is to use the `dbQuoteString()` or `sqlInterpolate()` functions [see RStudio Best Practices](https://db.rstudio.com/best-practices/run-queries-safely/). As far as I know, there is no way to do this with the `dbBind` function that is called by `dbGetQuery(con, sql, params)`, at least for the RPostgreSQL implementation of DBI. – ichbinallen Oct 17 '18 at 16:29
@ichbinallen: read the answer below if you are still looking to sanitize sql with `dbBind`. Didn't realize I didn't respond to the second part of of your comment previously. – x85ms16 Oct 07 '19 at 18:54

score 2 · Answer 2 · answered Feb 15 '19 at 18:33

The safest way is to parameterize the query as mentioned here

Example:

library(RPostgreSQL)

dt <- '2018-01-03'

connect <- dbConnect(drv = PostgreSQL(), 
  dbname ="test",
  host = "localhost",
  port = 5432,
  user = "user", 
  password = "...")

query <- "SELECT * FROM sales_tbl WHERE date= ?"
sanitized_query <- dbSendQuery(connect, query)
dbBind(sanitized_query, list(dt))
result <- dbFetch(sanitized_query)

Here by passing ? you are sanitizing your query to avoid SQL injection attacks.

Another thing I like to do is to create .Renviron file to store my credintials. For example, for the connection above, the .Renviron file will look like this.

dbname = test
dbuser = me
dbpass = mypass
dbport = 5432
dbhost = localhost

save the file, restart RStudio (to load the .Renviron file at startup). Then access the credentials using the Sys.getenv(variable)

#example:
connect <- dbConnect(drv = PostgreSQL(), 
  dbname = Sys.getenv("dbname"),
  host = Sys.getenv("dbhost"),
  port = Sys.getenv("dbport"),
  user = Sys.getenv("dbuser"), 
  password = Sys.getenv("dbpass"))

what if you have a start date and an end date? how does the query then change? — kRazzy R, Jun 23 '19 at 23:18
@kRazzyR yes. You'd need to change your SQL query. Sth like `SELECT * from table WHERE date between "?" and "?"`. Then send the parameter for those "?" placeholders on second parameter like `dbBind(query, list(date1, date2)`. Let me know if that worked for you or not. — x85ms16, Jun 25 '19 at 19:31

RPostgreSQL - Passing Parameter in R to a Query in RPostgreSQL

2 Answers2

Linked