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/ _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
