Passing bound arguments into a query

[Ryan Hughes]

Is there a way to make a query with bound arguments? Like
res <- dbSendQuery(con, "SELECT * FROM table WHERE param=?",
data=c('value'))

Or do I have to quote the parameter value myself and definitely mess
it up?

All the documentation I can find seems to have queries that don't have
parameters.

Thanks.
--Ryan

[Ryan Hughes]

[Ian Gow]

Something like this?

sql <- paste("SELECT * FROM table WHERE param=", value, sep="")
res <- dbSendQuery(con, sql)

[Ryan Hughes]

On Aug 29, 12:55 pm, Ian Gow <iand...@gmail.com> wrote:
> Something like this?
> sql <- paste("SELECT * FROM table WHERE param=", value, sep="")
> res <- dbSendQuery(con, sql)

That doesn't quote the value. If value contains
'; DELETE FROM table; --

then you lose all your data.

--Ryan

[Joe Conway]

See quote_literal()
http://www.postgresql.org/docs/9.1/interactive/functions-string.html

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

[Ryan Hughes]

Isn't that a postgresql function? I'd have to get the string from R
into postgres in order for postgres to quote it. But I'd have to
quote the string before I'd be able to send it to postgres.

res <- dbSendQuery(paste("select quote_literal(", value, sep='"'))

that's not going to work if value contains
hello'there

because postgres will see the string as
select quote_literal('hello'there')

which is a syntax error.

Every database library that I've seen, other than R's DBI interface,
has some ability to do bind-variables, where you put a placeholder in
the query string and then separately pass an array of arguments to be
properly quoted and substituted into the query.

For example, libpq has PQexecParams.

However, just because DBI doesn't support this style of working
doesn't mean the RPostgreSQL can't support it. ROracle allows you to
use bind queries by passing a "data" parameter to dbSendQuery.

Is this omission here the result of the fact that R's developers are
statisticians writing programs for themselves, rather than software
developers that have to code defensively and check input from
untrusted users? True, my program's input will only be coming from
trusted users, but that doesn't mean that I'm comfortable opening the
floodgates for sql injection attacks.

--Ryan

On Aug 29, 1:27 pm, Joe Conway <m...@joeconway.com> wrote:
> On 08/29/2012 10:24 AM, Ryan Hughes wrote:
>
> > On Aug 29, 12:55 pm, Ian Gow <iand...@gmail.com> wrote:
> >> Something like this?
> >> sql <- paste("SELECT * FROM table WHERE param=", value, sep="")
> >> res <- dbSendQuery(con, sql)
>
> > That doesn't quote the value.  If value contains
> >   '; DELETE FROM table; --
>
> > then you lose all your data.
>

> See quote_literal()http://www.postgresql.org/docs/9.1/interactive/functions-string.html

[Ryan Hughes]

rsqlite also has an interface for doing bind variables.
http://stackoverflow.com/questions/2186015/bind-variables-in-r-dbi

I'm sure either of these projects could serve as a starting point for
getting this functionality into RPostgreSQL.

For now, I guess I'll whitelist the inputs I expect. I hope one day
to have enough time to put bind-variable support into RPostgreSQL. It
was a very surprising omission. Apparently, the documentation for DBI
specifically mentions the omission, and offers no suggestions for
workarounds.

--Ryan

[Joe Conway]

On 08/29/2012 10:53 AM, Ryan Hughes wrote:
> Isn't that a postgresql function? I'd have to get the string from R
> into postgres in order for postgres to quote it. But I'd have to
> quote the string before I'd be able to send it to postgres.
>
> res <- dbSendQuery(paste("select quote_literal(", value, sep='"'))
>
> that's not going to work if value contains
> hello'there
>
> because postgres will see the string as
> select quote_literal('hello'there')

True enough -- I didn't think it all the way through. But I'm a postgres
guy, I don't really know the R DBI specific stuff all that well to be
honest. Certainly Postgres supports bind variables (through prepared
statements) but I don't know if R DBI does or not.

[Tomoaki Nishiyama]

Hi,

Prepared statement is not yet implemented in RPostgreSQL.
It is just not yet implemented due to insufficient time
and a patch to support is welcome.

For proper quoting you might call postgresqlEscapeStrings(),
which is a wrapper of a libpq function, PQescapeStringConn().
This will do the conversion that will be right under given the
connection. "Proper" quoting is arguably complex if you
want to handle the encodings right.
If the problem is injection, this is perhaps sufficient.
Unfortunately this is not portable among different databases
under DBI, but if you implement prepared statement specifically
for PostgreSQL, it is not portable among databases either.
If you need prepared statement for higher performance,
implementing the adapter is necessary.
For this purpose, we need something like statement handle, and
the interface needs to be designed to support the concept.

[John Dickinson]

You can use prepared statements like this:

> library(RPostgreSQL)
Loading required package: DBI
> dbdrv <- dbDriver("PostgreSQL")
> dbcon <- try(dbConnect(dbdrv, dbname="dbname", port=5432))
> sql <- "prepare myselect (text) AS SELECT * from data d where d.name=$1 and d.starttime=$2;"
> res <- dbSendQuery(dbcon, sql)
> res1 <- dbSendQuery(dbcon,"execute myselect('myname",'2013-05-01 00:04:00+00');")
> data <- fetch(res1, n=-1)

HTH
John

[Peter]

Hi,

I similarly 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 ? WHERE id = ?", c("foo_bar", 1))

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)

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?

Thanks,
PGS

[NISHIYAMA Tomoaki]

Hi,

PostgreSQL uses $1, $2, $3, ... for the parameter.

> postgresqlExecStatement(con, "DELETE FROM ? WHERE id = ?", c("foo_bar", 1))

You might try:
postgresqlExecStatement(con, "DELETE FROM $1 WHERE id = $2", c("foo_bar", 1))

Best regards,
--
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan

> --
> You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.