How to use parameters with RPostgreSQL (to insert data)
I was looking for the same thing, for the same reasons, which is security.
Apparently dplyr package has the capacity that you are interested in. It's barely documented, but it's there. Scroll down to "Postgresql" in this vignette: http://cran.r-project.org/web/packages/dplyr/vignettes/databases.html
To summarize, dplyr offers functions sql() and escape(), which can be combined to produce a parametrized query. SQL() function from DBI package seems to work in exactly same way.
> sql(paste0('SELECT * FROM blaah WHERE id = ', escape('random "\'stuff')))<SQL> SELECT * FROM blaah WHERE id = 'random "''stuff'It returns an object of classes "sql" and "character", so you can either pass it on to tbl() or possibly dbSendQuery() as well.
The escape() function correctly handles vectors as well, which I find most useful:
> sql(paste0('SELECT * FROM blaah WHERE id in ', escape(1:5)))<SQL> SELECT * FROM blaah WHERE id in (1, 2, 3, 4, 5)Same naturally works with variables as well:
> tmp <- c("asd", 2, date())> sql(paste0('SELECT * FROM blaah WHERE id in ', escape(tmp)))<SQL> SELECT * FROM blaah WHERE id in ('asd', '2', 'Tue Nov 18 15:19:08 2014')I feel much safer now putting together queries.
As of the latest RPostgreSQL it should work:
db_connection <- dbConnect(dbDriver("PostgreSQL"), dbname = database_name, host = "localhost", port = database_port, password=database_user_password, user = database_user)qry = "insert into mytable (a,b,c) values ($1,$2,$3)"dbSendQuery(db_connection, qry, c(1, "some string", "some string with | ' "))
Here's a version using the DBI and RPostgres packages, and inserting multiple rows at once, since all these years later it's still very difficult to figure out from the documentation.
x <- data.frame( a = c(1:10), b = letters[1:10], c = letters[11:20])# insert your own connection infocon <- DBI::dbConnect( RPostgres::Postgres(), dbname = '', host = '', port = 5432, user = '', password = '')RPostgres::dbSendQuery( con, "INSERT INTO mytable (a,b,c) VALUES ($1,$2,$3);", list( x$a, x$b, x$c ))The help for dbBind() in the DBI package is the only place that explains how to format parameters:
The placeholder format is currently not specified by DBI; in thefuture, a uniform placeholder syntax may be supported. Consult thebackend documentation for the supported formats.... Known examples are:
?(positional matching in order of appearance) in RMySQL and RSQLite$1(positional matching by index) in RPostgres and RSQLite:nameand$name(named matching) in RSQLite
? is also the placeholder for R package RJDBC.