MySQL + SQL Statements + Quote escaping
sholden at holdenweb.com
Mon Oct 29 10:04:11 CET 2001
"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
> >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?
> Eg (this works if there's no quotes in the substituted string):
> "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:
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.
More information about the Python-list