Escaping characters for MySQL for UPDATE operation using Python/MySQLdb

Question

Note: I have discovered that re.escape(string) will do escaping but man, does it escape a lot. I can use that if I have to, but I am leaving the question open for any other ideas, also in case this is stumbled upon in the future.

I found a great S.O. answer here regarding INSERTing data into MySQL with Python ( I'm using 2.7 ) where the data contains apostrophes : Python mySQL - escaping quotes

In a nutshell, the S.O. answer was to do this.....

sql = "INSERT INTO TABLE_A(COL_A,COL_B) VALUES(%s, %s)"
a_cursor.execute(sql, (val1, val2))

I need to do this for an UPDATE though. My current method is ....

sql = "UPDATE table SET COL_A='R', COL_B='%s', COL_C='%s', COL_D='%s', COL_E='%s',COL_F='%s' WHERE COL_G='%s'" % (val1, val2, val3, val4, val5, val6)
cur.execute(sql)

Error I get is: 1064 error in syntax.

The problem is val5, it's a blob of text that contains apostrophes ( and who knows what else ) and I need a way to escape special characters before UPDATE. I have sneaking suspicion that I'll just have to do this massive routine where I have to specifically spell out characters as I tend to find them but I'd like to see if anyone else has had this issue.

I appreciate any pointers in the right direction.

I ended up using a function instead to just escape the single apostrophe. I am not going to pick my own as the answer though. — Jane Wilkie
– Jane Wilkie, Commented May 22, 2017 at 19:24
Are all those fancy quotes in your actual code or did you use an editor somewhere that inserted them? ‘R’ is not valid because of those quotes, use 'R'.. — Martijn Pieters
– Martijn Pieters, Commented May 22, 2017 at 19:26

Martijn Pieters · Accepted Answer · 2017-05-22 19:31:15Z

Use SQL parameters; these are escaped and quoted for you; note that the INSERT query you found uses this technique. SQL parameter escaping handle apostrophes properly, and makes sure you don't fall victim to a SQL injection attack, among other advantages.

From your sql value, remove the quoting around your parameters, and pass in the values as parameters, do not use interpolation:

sql = "UPDATE table SET COL_A='R', COL_B=%s, COL_C=%s, COL_D=%s, COL_E=%s, COL_F=%s WHERE COL_G=%s"
cur.execute(sql, (val1, val2, val3, val4, val5, val6))

or using a multiline string for better readability:

sql = """
    UPDATE table
    SET
        COL_A='R',
        COL_B=%s,
        COL_C=%s,
        COL_D=%s,
        COL_E=%s,
        COL_F=%s
    WHERE COL_G=%s"
cur.execute(sql, (val1, val2, val3, val4, val5, val6))

As an aside, re.escape() is a function to handle escaping of strings you want to treat as literal values in a regular expression. The function is entirely unsuited for escaping values in a SQL setting.

Collectives™ on Stack Overflow

Escaping characters for MySQL for UPDATE operation using Python/MySQLdb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related