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

[Gordon McMillan]

Aahz Maruch <aahz at netcom.com> wrote:

>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:

>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. 

Many DB servers cache compiled queries with SQL text as key and 
parsed statement as value. If an SQL statement is *exactly* 
identical, it can skip the compile step.

Some DB servers (Oracle, in particular) are very bad about 
managing their cache. Eventually it eats up all memory, and the 
server has to be brought down to clear it. So, at least in some 
circumstances, your advice is a recipe for disaster ;-).

Personally, I go one step further and use prepared statements. All of 
them get compiled at the start of the app. Unfortunately, the 
Python DB API doesn't expose this capability. (Yes, it's a PITA, 
but so is the impedance mismatch between SQL and the host 
language).

- Gordon