[R-sig-DB] Parameterised queries

[Tim Keitt]

On Wed, Feb 11, 2015 at 8:01 AM, Hadley Wickham <h.wickham using gmail.com> wrote:

> Hi all,
>
> As part of my work modernising R's database connnectors, I've been
> working on improving the support for parameterised queries. I'd love
> to get your feedback on the API I'm proposing.
>
> The goal is to encourage people to use parameterise queries instead of
> pasting together SQL strings because it is much safer (no worries
> about SQL injection attacks) and somewhat more performant (becuase the
> database only needs to parse the query once).
>
> There are basically two ways to use it:
>
> * A parameterised query + multiple calls to `dbBind()`
> * A parameterised query + a list of params.
>
> Here's an example using the dev version of RSQLite available from
> https://github.com/rstats-db/RSQLite:
>
> ```
> library(DBI)
> con <- RSQLite::datasetsDb()
>
> rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = $x")
> # Not bound, so throws error
> dbFetch(rs)
>
> # Bind by position
> dbBind(rs, list(8))
> dbFetch(rs)
>
> # Bind by name
> dbBind(rs, list(x = 8))
> dbFetch(rs)
>
> # Or do when you create the query
> rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = $x", list(x = 8))
> dbFetch(rs)
>
> # Or all at once with dbGetQuery
> dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = $x", list(x = 8))
> ```
>
> What do you think? I've deliberately designed the syntax to be
> backward compatible, although it obviously requires some changes to
> DBI (such as the introduction of the dbBind() generic).
>
> I also have an implementation available for Postgres (in my new
> RPostgres package, https://github.com/rstats-db/RPostgres) and I'll be
> working on RMySQL later this week.
>

This seems like a reasonable extension to DBI.

Just for comparison, in rpg (https://github.com/thk686/rpg) I have:

query(sql, pars)  # pars are substituted for $ vars if provided
fetch(sql, pars) # also fetches result

The latest version (not yet on CRAN) has:

f = prepare(sql)
f(pars)

for prepared queries. f is a closure around the auto-generated
prepared-query id-string. The function f does loop at the C++ level over a
vector or matrix of parameters.

THK




>
> Hadley
>
> --
> http://had.co.nz/
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>



-- 
http://www.keittlab.org/

	[[alternative HTML version deleted]]