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 -- http://mail.python.org/mailman/listinfo/python-list