Python MySQL escape special characters

Question

I am using python to insert a string into MySQL with special characters.

The string to insert looks like so:

macaddress_eth0;00:1E:68:C6:09:A0;macaddress_eth1;00:1E:68:C6:09:A1

Here is the SQL:

UPGRADE inventory_server 
set server_mac = macaddress\_eth0\;00\:1E\:68\:C6\:09\:A0\;macaddress\_eth1\;00\:1E\:68\:C6\:09\:A1' 
where server_name = 'myhost.fqdn.com

When I execute the update, I get this error:

ERROR 1064 (42000): 
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 'UPGRADE inventory_server 
set server_mac = 'macaddress\_eth0\;00\:1E\:68\:C6\:09\' at line 1

The python code:

sql = 'UPGRADE inventory_server set server_mac = \'%s\' where server_name = \'%s\'' % (str(mydb.escape_string(macs)),host)
print sql

try:
    con = mydb.connect(DBHOST,DBUSER,DBPASS,DB);
    with con:
       cur = con.cursor(mydb.cursors.DictCursor)
       cur.execute(sql)
   con.commit()
except:
return False

How can I insert this text raw?

Answer 1

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).

Answer 2

Welcome to the world of string encoding formats!

tl;dr - The preferred method for handling quotes and escape characters when storing data in MySQL columns is to use parameterized queries and let the MySQLDatabase driver handle it. Alternatively, you can escape quotes and slashes by doubling them up prior to insertion.

Full example at bottom of link

standard SQL update

# as_json must have escape slashes and quotes doubled
query = """\
        UPDATE json_sandbox
        SET data = '{}'
        WHERE id = 1;
    """.format(as_json)

with DBConn(*client.conn_args) as c:
    c.cursor.execute(query)
    c.connection.commit()

parameterized SQL update

# SQL Driver will do the escaping for you
query = """\
        UPDATE json_sandbox
        SET data = %s
        WHERE id = %s;
    """

with DBConn(*client.conn_args) as c:
    c.cursor.execute(query, (as_json, 1))
    c.connection.commit()

Invalid JSON SQL

{
  "abc": 123,
  "quotes": "ain't it great",
  "multiLine1": "hello\nworld",
  "multiLine3": "hello\r\nuniverse\r\n"
}

Valid JSON SQL

{
  "abc": 123,
  "quotes": "ain''t it great",
  "multiLine1": "hello\\nworld",
  "multiLine3": "hello\\r\\nuniverse\\r\\n"
}

Python transform:

# must escape the escape characters, so each slash is doubled
# Some MySQL Python libraries also have an escape() or escape_string() method.
as_json = json.dumps(payload) \
    .replace("'", "''") \
    .replace('\\', '\\\\')

Full example

import json
import yaml

from DataAccessLayer.mysql_va import get_sql_client, DBConn

client = get_sql_client()

def encode_and_store(payload):
    as_json = json.dumps(payload) \
        .replace("'", "''") \
        .replace('\\', '\\\\')

    query = """\
            UPDATE json_sandbox
            SET data = '{}'
            WHERE id = 1;
        """.format(as_json)

    with DBConn(*client.conn_args) as c:
        c.cursor.execute(query)
        c.connection.commit()

    return

def encode_and_store_2(payload):
    as_json = json.dumps(payload)

    query = """\
            UPDATE json_sandbox
            SET data = %s
            WHERE id = %s;
        """

    with DBConn(*client.conn_args) as c:
        c.cursor.execute(query, (as_json, 1))
        c.connection.commit()

    return


def retrieve_and_decode():
    query = """
        SELECT * FROM json_sandbox
        WHERE id = 1
    """

    with DBConn(*client.conn_args) as cnx:
        cursor = cnx.dict_cursor
        cursor.execute(query)

        rows = cursor.fetchall()


    as_json = rows[0].get('data')

    payload = yaml.safe_load(as_json)
    return payload



if __name__ == '__main__':

    payload = {
        "abc": 123,
        "quotes": "ain't it great",
        "multiLine1": "hello\nworld",
        "multiLine2": """
            hello
            world
        """,
        "multiLine3": "hello\r\nuniverse\r\n"
    }


    encode_and_store(payload)
    output_a = retrieve_and_decode()

    encode_and_store_2(payload)
    output_b = retrieve_and_decode()

    print("original: {}".format(payload))
    print("method_a: {}".format(output_a))
    print("method_b: {}".format(output_b))

    print('')
    print(output_a['multiLine1'])

    print('')
    print(output_b['multiLine2'])

    print('\nAll Equal?: {}'.format(payload == output_a == output_b))