[DB-SIG] paramstyles (mysql string length?)

Ian Bicking ianb at colorstudy.com
Wed Apr 19 18:29:03 CEST 2006

Carsten Haese wrote:
> On Wed, 2006-04-19 at 11:38, Gerhard Häring wrote:
>>Abolishing (py)format certainly means additional work for the module 
>>authors. If we do so - I haven't read the whole thread, so I don't know 
>>what the arguments are for it - then we should include example code in 
>>the DB-API for parsing qmarks out of ANSI SQL statements.
> The main argument for abolishing (py)format is that it blurs the line
> between parameter passing, which is good, and hand-rolling a query via
> string substitution, which is bad because it invites SQL injections if
> not done carefully, and it's almost never done carefully.

It's not *so* bad, because this works:

   execute('... where x = %s', (x,))

And this will fail immediately:

   execute('... where x = %s' % (x,))

It's only repr() which really confuses things.  Well, that and cases 
where you have integers.  Anyway, it's significantly better than PHP.

> Especially newbies seem to have a problem with telling the two apart and
> understanding why parameter binding is better than string substitution.
> Abolishing %s should make it a lot easier to clearly separate the two
> concepts.
> Ian also brought up the point that implementations that use (py)format
> have a rather ugly wart: Literal % signs in queries have to be doubled
> up to prevent accidental parameter markers. This is ugly and makes
> writing portable code unnecessarily hard.
> I agree that if we decide to abolish (py)format, we should help out
> module authors for databases that don't natively support '?' by
> providing example code for performing the necessary parsing.

When using a higher-level SQL generator the parsing overhead would be 
unnecessary, as such a library can produce proper pyformat queries 
itself.  Using a parser and whatnot might be more reasonable if the user 
could select the format in some manner; then libraries that didn't care 
could stick with the most efficient one for the driver.

OK, crazy idea: use \x00 as a marker, which requires no quoting or 
parsing (as it can't be included in any SQL literal anyway).

Ian Bicking  /  ianb at colorstudy.com  /  http://blog.ianbicking.org

More information about the DB-SIG mailing list