Escape String Python for MySQL

Python MySQL escape special characters

This is one of the reasons you're supposed to use parameter binding instead of formatting the parameters in Python.

Just do this:

sql = 'UPGRADE inventory_server set server_mac = %s where server_name = %s'

Then:

cur.execute(sql, macs, host)

That way, you can just deal with the string as a string, and let the MySQL library figure out how to quote and escape it for you.

On top of that, you generally get better performance (because MySQL can compile and cache one query and reuse it for different parameter values) and avoid SQL injection attacks (one of the most common ways to get yourself hacked).

Escaping strings with python mysql.connector

Since mysql.connector is DB API v2.0 compliant, you do not need to escape the data yourself, it does it automatically for you.

How to Mysql escape in PYTHON

By using the execute() call properly:

data = []
for key, value in result_array.items():
    # only proper if key is NOT user defined!
    insert_array.append("%s = %%s" % key) # results in key = %s
    data.append(value)
insert_values = " , ".join(insert_array)
query_insert = "INSERT into `%s` SET %s ON DUPLICATE KEY UPDATE %s" %(insert_table, insert_values, insert_values)
cursor.execute(query_insert, data * 2)

Didn't test it, and hopfilly understood your code right.

The point is to pass the real data to the execute() call which is supposed to escape it for you. It does so by means of the database functions and thus does it better than you ever could.

Escaping single and double quotes for mysql in python

MySQLdb officially declares to use the format paramstyle, but it also supports the pyformat style*, so if you want to use parameters from a dict, you can use:

db_dict = {'msg_id': "1'2'3", ...}
cursor.execute("SELECT * FROM messages WHERE msg_id = %(msg_id)s", db_dict)

Using string manipulation to create sql queries only leads to sql injection vulnerabilities, so you should never do it.

---

*... most db connectors that use python string formatting behind the screen do the same, they specify one of format or pyformat as paramstyle but actually support both. The dbapi2 doesnt't allow to specify two values here, but it doesn't forbid to support multiple parmstyles either. If you write code that potentially uses an unknowon dbapi2 connector it's enough that you can query a supported paramstyle, being able to know all would be nice but it's not necessary.

How do I escape % from python mysql query

Literal escaping is recommended by the docs:

Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%.

Escaping characters for MySQL for UPDATE operation using Python/MySQLdb

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.

---

Related Topics