MySQL + SQL Statements + Quote escaping

[Steve Holden]

"Chris Stromberger" <bit_bucket5 at hotmail.com> wrote in message
news:fg0mtt43bt0k9n7kp93hk1gdt5tndcq5bu at 4ax.com...
> >Christian Theune wrote:
> >
> >    i just tuned into python and it's really cool. I like it.
> >    Before I came to python i used to write scripts in php.
> >    There was a function called "addslashes" it escaped quotes and
> >    slashes to make strings sql-safe.
> >
> >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)
> >    )
> >
> >The %s placeholders are replaced by correctly quoted values.
> >
> >There are two advantages to this approach.
> >
> >Firstly, database engines differ in how they escape quotes. Many, such
> >as MySQLdb, prefix them with a backslash, but other DB engines escape
> >quotes by doubling them (thus 'O''Reilly'). Using the placeholder
> >mechanism lets the particular database module deal with that for you.
> >
> >The second advantage is that the execute() method can optimise its
> >performance when the same operation is repeatedly executed but with
> >different bound values for the placeholders. But if you build the query
> >string yourself on each iteration, then the execute() method will have
> >to parse it each time for correctness, thus losing the opportunity for
> >optimisation.
> >
> >Hamish Lawson
> >
>
>
> 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?
>
> Thanks,
> Chris
>
> 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)
>     )
>
The DB API actually specifies five different SQL parameterization methods,
and each different module can choose a different method. The DB API says
each module must define a paramstyle global, with the following meaning:

paramstyle
String constant stating the type of parameter marker formatting expected by
the interface. Possible values are:
'qmark' = Question mark style, e.g. '...WHERE name=?'
'numeric' = Numeric, positional style, e.g. '...WHERE name=:1'
'named' = Named style, e.g. '...WHERE name=:name'
'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'
'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'

So take a look at paramstyle for your module, and that wil tell you more.

Some modules allow you to modify paramstyle, others just use a fixed value.

regards
 Steve
--
http://www.holdenweb.com/