1

I am bridging R and psql, wish to remove vulnerability to sql injection. Looking at documentation, I had hoped that:

postgresqlExecStatement(con, statement, params, ...)

Would allow use of something like:

postgresqlExecStatement(con, "DELETE FROM foos WHERE id = ? AND baz = ?", c(1, "bar"))

But unfortunately this does not seem to work. Maybe I'm using the wrong symbol for parameter (something other than ?).

Best compromise I've found is escaping strings via:

postgresqlEscapeStrings(con, string)

(note: connection is necessary so function can know how to properly escape).

Means I have to escape every string I use in a paste when putting together my queries. Not so elegant. But seems best option. Anyone have other ideas?

shf8888
  • 255
  • 1
  • 9
  • You might want to try https://github.com/rstats-db/RPostgres - it has full support for parameterised queries and implements the dbi generic `dbQuoteString()` and `dbQuoteIdentifier()` – hadley Feb 24 '15 at 16:15

1 Answers1

0

Use

postgresqlExecStatement(con, "DELETE FROM foos WHERE id = $1 AND baz = $2", list(1, "bar"))

I always pass my parameters to be bound as a list since c will force it into one mode. You also have to clear the results belonging to con if this statement succeeds before you can use it again.

Also, please note hadley's comments to use the new package RPostgres.

Alex
  • 15,186
  • 15
  • 73
  • 127
  • I don't think that will work because parameterised queries can usually only insert values, not parameters (i.e. the table name) – hadley Feb 24 '15 at 16:16
  • You are correct. I was answering from the perspective that one should use `$n` where `n` is an integer to construct a parametrised query with `postgresqlExecStatement`. – Alex Feb 26 '15 at 04:07
  • 1
    Thanks to both of you for the comments. Your answer was great. I've edited my question to remove the somewhat unreasonable request that the passed table name be escaped. – shf8888 Mar 30 '15 at 20:20