CODEWITHSUNDEEP
pythonmysql
user611105
user611105

Reputation:

Escaping a whole string in mysql/python

So I'm writing a python script that is updating values in a mysql table. I've run into a small problem where I've discovered that one of my values that I'm updating into the table has a single quote "'"

Here is the piece of python code I am using,

    cur3.execute("UPDATE recon SET Name = '%s' WHERE id = '%s'" % (row[1], row[0]))

Works great until I get to a Name that is like this,

John's Computer

I tried doing 

   cur3.execute("UPDATE recon SET Name = \'%s\' WHERE id = '%s'" % (row[1], row[0]))

But that didn't seem to work either. Any idea how to escape whatever is in row[1] completely?

Upvotes: 0

Views: 609

Answers (2)

reech
reech

Reputation: 340

You should really be doing: 

cur3.execute("UPDATE recon SET Name = %s WHERE id = %s", (row[1], row[0]))

Which is very different from: 

cur3.execute("UPDATE recon SET Name = '%s' WHERE id = '%s'" % (row[1], row[0]))

The top example will be escaped, the other not.

Upvotes: 1

Wooble
Wooble

Reputation: 90037

Don't construct SQL queries with string formatting. Assuming you're using the mysqldb library; the syntax:

cur3.execute("UPDATE recon SET Name = '%s' WHERE id = '%s'", (row[1], row[0]))

is what you're looking for. Note that you're passing a tuple as the second parameter to .execute(), rather than using the % operator to do string formatting.

Upvotes: 3

Related Questions