MySQL + SQL Statements + Quote escaping

[Hamish Lawson]

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


____________________________________________________________
Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie