How to use parameters with RPostgreSQL (to insert

问题:

I'm trying to insert data into a pre-existing PostgreSQL table using RPostgreSQL and I can't figure out the syntax for SQL parameters (prepared statements).

E.g. suppose I want to do the following

insert into mytable (a,b,c) values ($1,$2,$3)

How do I specify the parameters? dbSendQuery doesn't seem to understand if you just put the parameters in the ....

I've found dbWriteTable can be used to dump an entire table, but won't let you specify the columns (so no good for defaults etc.). And anyway, I'll need to know this for other queries once I get the data in there (so I suppose this isn't really insert specific)!

Sure I'm just missing something obvious...

回答1:

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.

回答2:

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