Escaping characters in MySQLdb query

[Sean Berry]

I was doing something like this:

for item in values:
item = item.replace("'", "//'")

But I am looking for something a lot nicer.

Sorry about my first post date... 12 hours off.

 

[Michael Walter]

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?

Thanks.

I suppose you do like:

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

and have all magic done for you.

Cheers,
Michael

 

[Sean Berry]

It doesn't work. If I have a value with a ', it creates an error.

 

[Michael Walter]

It doesn't work. If I have a value with a ', it creates an error.

It should obviously be without the quotations marks inside the SQL
string, but besides it does work.

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

Cheers,
Michael

 

[Rob Williscroft]

Sean Berry wrote in 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.

 

[Bryan Castillo]

Python 2.3.3 (#51, Dec 18 2003, 20:22:39) [MSC v.1200 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.escape_string(s) -- quote any SQL-interpreted characters in string s.

Use connection.escape_string(s), if you use it at all.
_mysql.escape_string(s) cannot handle character sets. You are
probably better off using connection.escape(o) instead, since
it will escape entire sequences as well as strings.

 

[richard]

I suppose you do like:

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

Note that the single quotes in the SQL are unnecessary:
1L


Let the library do all the hard work for you - that's what it's there for


Richard

 

[Sean Berry]

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?

Thanks.

 

[Glenn Stauffer]

There is a function in the MySQLdb module, string_literal(), that
returns a properly escaped string.

I do something like this when processing html form data that is being
inserted in mysql:

def dbescape(val):
if val:
return MySQLdb.string_literal(val)
else:
return "NULL"

cursor.execute(insertsql % dbescape(formdict['address'].value)

Until I figured this out, I was using the replace(...) method that
you've been using.

--Glenn

 

[Michael Walter]

Sean Berry wrote in in
comp.lang.python:




IIUC this is (mostly) a SQL question.

No, this is a do-I-know-my-library-well-enough-to-make-my-life-easy
question Note that both richard and me are actually passing the
format string arguments as a *separate* argument to execute(). If you
then have a look at the MySQLdb source code, you will see that those
arguments get quoted automagically using the connections' literal_blabla
method.

Hence, c.execute("SELECT %s", "fooo'bar") will actually execute "SELECT
'fooo\'bar'" in the database.

Cheers,
Michael