SQL: don't use bind variables (was Re: Using strings with ' in them in SQL-queries)

[Paul Boddie]

Aahz Maruch wrote:
> 
> In article <393510AB.D62AE9A5 at infercor.no>,
> Paul Boddie  <paulb at infercor.no> wrote:
> >
> >As the ideal solution, though, I would recommend using bind variables
> >(placeholders) if your database module supports it:
> >
> >  db.query("insert into test (id, name) value (1, ?)", (var,))

[...]

> I've been keeping silent for a while, but I'm now going to start
> speaking forcefully against this style.  It's a real PITA to maintain
> when you have complex queries, because you can't use dicts and named
> parameters.  I find that it works much better to do something like this:
> 
> dict = {....}
> q = """
>     SELECT %(field)s FROM %(table)s
>     WHERE %(key)s IN (SELECT %(key)s FROM %(jointable)s)
>     """ % dict

This doesn't address the problem with ' characters in values. In fact, you're
configuring the query, which is quite distinct from typical applications of bind
variables. As far as I am aware, many database systems (if not all mainstream
database systems) do not allow you to use bind variables to specify which
columns and tables you're using.

Anyway, some database systems do support named parameters, and the DB-API
specification (version 2) says:

  Parameters may be provided as sequence or mapping and will be bound to
  variables in the operation.

The key word here is "mapping". I don't know how many database modules support
such functionality, though. It would look something like this with Oracle, if I
remember correctly:

  db.execute("insert into test (id, name) value (1, :var)", {"var" : var})

(Now I look again, I don't know where the 'query' method came from, but I assume
that the thread's initiator meant 'execute'.)

> (This is a stupid example, but it demonstrates the problem that
> frequently occurs when you have to use the same variable more than
> once.)  Remember that the Python Way is to write code as clearly as
> possible until such time as speed is proven to be an issue.

But speed isn't the only issue. As the initiator of the thread had found, there
are encoding issues which are conveniently solved by using the database module's
functionality. One reason why I don't do what you're doing is to reduce the
number of potential pitfalls connected with "trivial" implementation details -
if the database system provides API functions to pass string values directly to
the system itself, then why mess around with encoding string literals? (And I'm
not even considering inconsistencies between the documentation and how the
system might actually interpret encoded string literals.)

why-spend-all-that-time-making-a-nice-round-rolling-thing-when-someone-is-only
-going-to-tell-you-that-you're-reinventing-the-wheel-ly y'rs - Paul