MySQL + SQL Statements + Quote escaping

[Paul Boddie]

Chris Stromberger <bit_bucket5 at hotmail.com> wrote in message news:<fg0mtt43bt0k9n7kp93hk1gdt5tndcq5bu at 4ax.com>...
>
> Hamish Lawson wrote:
> >
> >Database modules that conform to the DB-API specfication, such as
> >MySQLdb, provide a placeholder mechanism that will take care of the
> >quoting automatically. For example:
> >
> >    cursor.execute(
> >        "select * from customers where surname = %s and age < %s", 
> >        ("O'Hara", 40)
> >    )
> 
> This doesn't work for me.  I have to add single quotes around the %s
> and then it still doesn't escape the single quote in the substituted
> string.  What am I missing?
> 
> Eg (this works if there's no quotes in the substituted string):
> 
>     cursor.execute(
>         "select * from customers where surname = '%s' and age < %s", 
>         ("O'Hara", 40)
>     )

I know this will sound somewhat unhelpful, but any serious combination
of Python database module and database system will provide proper
support for placeholders as described above. If you aren't getting
"justice" from the above example, then it may be appropriate to
consider which module you're using and whether there's one which
implements placeholders properly.

I actually thought that MySQLdb [1], for example, had support for
placeholders in the fashion illustrated above, but then I've never
used MySQL or MySQLdb due to a number of reasons including...

  * When I tried to install MySQL, there were loads of different
    packages of different releases, some of which were supposed
    to work with other things but didn't, and some of which had
    things missing. It was easier to download an evaluation
    version of a commercial database system for my purposes at
    that time.

  * At the time in question, MySQL lacked features that I
    consider essential in a relational database system.

I'm sure MySQLdb is one of the better supported Python database
modules out there, however.

Paul

[1] http://dustman.net/andy/python/MySQLdb/