On Thu, 2006-10-05 at 16:15, John Salerno wrote:
But I think SQL has other recommended methods. At least with SQLite, it
is recommended you not use Python's %s formatter but instead the "?"
formatter.
While I wholeheartedly agree with the sentiment, calling the "?" a
formatter only blurs the already blurred distinction between string
formatting and parameter passing. The "?" is a parameter placeholder.
I'm not gonna go into the reasons for why one should always use
parametrized queries instead of rolling queries via string formatting,
but the keywords are "SQL injection attack" and "poor performance". I
would like to point out, though, that parameter passing in DB-API
compliant database access modules is in general very different from
string formatting.
In most databases, when you say cur.execute("update sometable set
somecolumn = ? where somekey = ?", ("spam", "eggs")), the database
driver does *not* build a query string with string literals for "spam"
and "eggs" substituted into the query. Real databases have a native API
that allows passing a parametrized query and a set of parameter
bindings, no string substitution required or desired.
Some databases do not have such an API, and their respective DB-API
modules emulate parameter passing by string substitution, but that is an
implementation detail nobody should care about. However, it is precisely
those databases that blur the distinction between parameter passing and
string substitution, especially because their implementations tend to
use "%s" parameter placeholders to make the internal string substitution
easier, thus leaking an implementation detail into application code in
an unfortunate way. (This is also the reason why I'd like to see %s
parameter placeholders banned from future versions of the DB-API spec.)
The bottom-line is, when writing parametrized queries, the "?" or "%s"
or whatever is used to indicate that "here be parameters" is a parameter
placeholder, not a formatter.
Thanks for listening, I hope somebody out there finds this helpful ;)
-Carsten
