Escaping characters in MySQLdb query

[Rob Williscroft]

Sean Berry wrote in news:AkEec.271$U83.155 at fed1read03 in
comp.lang.python: 

> I wrote a little script that is inserting thousands of records into a
> mysql database.
> 
> How do I escape characters like ' in my insert statements?
> 
> I have something like the following (much shorter) example:
> 
> c.execute("INSERT INTO records (var1, var2) values ('%s', '%s')"  
> %(value1, value2))
> 
> My problem is when value1 is something like "Tom's auto supply".  The
> ' in Tom's needs to be escaped.  How can I do this?
> 

IIUC this is (mostly) a SQL question. 

c.execute( 
  "INSERT INTO records (var1, var2) values ('%s', '%s')" 
  % (value1.replace( "'", "''" ), value2.replace( "'", "''" )) 
)

I believe the standard way of puting a ' in an SQL ' delemited string is
to double it i.e. '''' is a single char string "'", but check the mysql 
docs.

http://dev.mysql.com/doc/mysql/en/String_syntax.html

Apparently with mysql you can also use '\'' so:

c.execute( 
  "INSERT INTO records (var1, var2) values ('%s', '%s')" 
  % (value1.replace( "'", "\\'" ), value2.replace( "'", "\\'" )) 
)

Should also work.

Rob.
-- 
http://www.victim-prime.dsl.pipex.com/