[R-sig-DB] Parameterised queries

[Hadley Wickham]

On Wed, Feb 11, 2015 at 8:39 AM, Hannes Mühleisen
<Hannes.Muehleisen using cwi.nl> wrote:
> Hi Hadley and list,
>
>> On 11 Feb 2015, at 15:01, Hadley Wickham <h.wickham using gmail.com> wrote:
>> 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).
> Makes a lot of sense, yes. MonetDB.R has had support for this from day one. Our syntax uses the list of parameters approach, e.g.
>
> dbSendUpdate(conn, "INSERT INTO sometable (a) VALUES (?)", “foobar”)
>
> of course, the parameter can be a vector, in which case the query is executed multiple times.

I deliberately chose not to allow vector parameters because I think
it's reasonable to say: "Each R function only ever generates a single
query to the database", leaving the onus of looping on the user, and
forcing them to think about how to ensure the vectors don't contain
bad values.  This the same principle behind disallowing multiple
queries (separated by ";") in a single string.

(Side note: if you create a new generic, can you please start it with
(e.g.) monet, not db, because it's doesn't live in the DBI package.
Alternatively, submit a pull request to DBI adding the generic, and
I'll review it. I don't think dbSendQuery is needed with the current
api, but I might be wrong.)

> Generally, I would be in favour of the “list of params” approach. Also, please note that the “bind by name” is not supported by all databases. Sticking to position-only parameter binding using ? would be most compatible (also used in JDBC for example).

Yup, postgresql doesn't support names either. In that case, providing
a named list would be an error. But where named binding is supported,
I think it's better to use it because it eliminates a class of
potential errors.

Hadley

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