Problem with MySQL cursor

[Carsten Haese]

On Fri, 2007-10-12 at 13:12 +0200, Florian Lindner wrote:
> Carsten Haese wrote: 
> > sql = "INSERT INTO "+DOMAIN_TABLE+"("+DOMAIN_FIELD+") VALUES (%s)"
> > executeSQL(sql, domainname)
> 
> Ok, I understand it and now it works, but why is limitation? Why can't I
> just the string interpolation in any playes and the cursor function escapes
> any strings so that they can't do harm to my query?

What's happening here is not string interpolation. It's called parameter
binding, and the SQL standard defines exactly what parameter binding can
and can not do. (The SQL standard also defines that parameter markers
are question marks, and the fact that DB-API v2 modules are allowed to
blur the distinction between string interpolation and parameter binding
by choosing to use %s markers is very unfortunate.)

A primary purpose of parameter binding is the ability to prepare a query
once and then execute it many times over with different values. This
saves time because the query only needs to be parsed and planned once.
For this to be useful, parameters can't be bound to anything that would
alter the query plan. Consequently, parameter binding can't "substitute"
any of the following:

* The type of statement (SELECT/UPDATE/etc.)
* The tables involved
* The columns involved
* The structure of the join and where clauses
* The group by, order by, and having clauses
* The names of called functions and procedures
* Probably a whole lot of other things I'm not thinking of right now

Once you exclude all the things that parameter binding can't substitute,
you're left with only a very small segment of the SQL language that
parameter binding *can* substitute, which basically boils down to
"anything that could be a literal value".

HTH,

-- 
Carsten Haese
http://informixdb.sourceforge.net