db question

[Lawrence D'Oliveiro]

In message <hoKdnehFvIQ_uBnVnZ2dnUVZ_oLinZ2d at earthlink.com>, Dennis Lee
Bieber wrote:

> On Sun, 20 Jul 2008 18:43:03 -0700, "bruce" <bedouglas at earthlink.net>
> declaimed the following in comp.lang.python:
>> 
>> mysql cmd - select * from foo where dog like "%small%";
>> 
>> sql ="""select * from foo where dog like "%%%s%%" """
>> c.execute(sql, (var,))
> 
> The execute statement is responsible quoting your literals, so the
> final statement you end up submitting looks like:
> 
> select * from foo where dog like ""%"xxx"%""
> 
> or some variant thereof.
> 
> Anything parameterized with %s must be a complete term!
> 
> Try:
> 
> sql = "select * from foo where dog like %s"
> c.execute(sql, ("%" + var + "%",) )
> 
> IOW: you need to massage the variable search term to include the
> wildcard % FIRST, then let MySQLdb substitute it (with surrounding
> quotes and escapes) into the SQL.

More general, less pitfall-prone solution:

    select * from foo where dog like %s" %
        SQLString("%" + EscapeSQLWild("small") + "%")

where SQLString and EscapeSQLWild are defined as in
<http://groups.google.co.nz/group/comp.lang.python/msg/ed7e561036895cbe>.