How to handle apostrophes in MySQL-Python?

Question

A Python API is giving back u"'HOPPE'S No. 9'" as a value for a particular product attribute. I'm then looking to insert it into the DB, also using Python (python-mysqldb), with the following query:

INSERT INTO mytable (rating, Name) VALUES('5.0 (7)', 'HOPPE'S No. 9';

MySQL rejects this, and the suggested approach to handling a single quote in MySQL is to escape it first. This I need to do in Python, so I try:

In [5]: u"'HOPPE'S No. 9'".replace("'", "\'")
Out[5]: u"'HOPPE'S No. 9'"

When I incorporate this in my program, MySQL still rejects it. So I double-escape the apostrophe, and then an insert happens successfully. Thing is, it contains the escape character (so what gets written is 'HOPPE\'S No. 9').

If I need the second escape character, but when I add it gets left in, then how can I handle the escaping without having the escape character included in the string that gets inserted?

Edit: Based on theBjorn's suggestion, tried:

actualSQL = "INSERT INTO %s (%s) VALUES(%s);"

#cur.execute(queryString)
cur.execute(actualSQL,
            (configData["table"], sqlFieldMappingString, sqlFieldValuesString))

but it looks like I'm back to where I was when I was trying to escape using the single escape with .replace():

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mytable' ('rating, Name, Image, mfg, price, URL') VALUES('\'5.0 (3)\', \'AR-1' at line 1

In SQL you quote a single quote by doubling it, but why are you not using parameters?! — thebjorn, Sep 07 '15 at 00:41
Parameterization is only used for the values clause. Notice I have one `%s` per value, and there is a comma between the sql and the values tuple (this isn't string interpolation!) — thebjorn, Sep 07 '15 at 01:38
@thebjorn ok so the issue is now that I am string-formatting configData["table"] and sqlFieldMappingString, when I shouldn't be. Problem is: I cannot hardcode the table the column names as you have done below, since they will come from variables *configData["table"]* and *sqlFieldMappingString* respectively. — Pyderman, Sep 07 '15 at 01:50
change this In [5]: u"'HOPPE'S No. 9'".replace("'", "\'") to In [5]: u"'HOPPE'S No. 9'".replace("'", "\\'"), notice the \' with \\' — adesst, Feb 15 '19 at 08:15

thebjorn · Accepted Answer · 2015-09-07T02:02:51.653

You should never construct sql that way. Use parameterized code instead:

cursor.execute(
    "insert into mytable (rating, name) values (%s, %s);",
    ("5.0 (7)", "HOPPE'S No. 9")
)

your latest problem is due to the misconception that this is string interpolation, which it isn't (the use of %s is confusing), thus:

actualSQL = "INSERT INTO %s (%s) VALUES(%s);"

will be wrong. It is possible to construct your sql string, but probably easier to do so in two steps so we don't trip over sql parameter markers looking like string interpolation markers. Assuming you have the values in a tuple named field_values:

params = ["%s"] * len(field_values)         # create a list with the correct number of parameter markers
sql = "insert into %s (%s) values (%s)" % ( # here we're using string interpolation, but not with the values
    configData["table"], 
    sqlFieldMappingString,
    ', '.join(params)
)

if you print sql it should look like my example above. Now you can execute it with:

cursor.execute(sql, field_values)

Thanks, see my edit above, wherI tried a variant of your suggestion - still no luck. What am I doing wrong? — Pyderman, Sep 07 '15 at 01:37
Yes the combination of the string formatting operator and the parameterization had me stumped. Thanks again. — Pyderman, Sep 07 '15 at 19:18

How to handle apostrophes in MySQL-Python?

1 Answers1