[R-sig-DB] Parameterised queries

[Hadley Wickham]

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.

Hadley

-- 
http://had.co.nz/