Python3: Escape MySQL values in programmatically built query

Question

I am building a Python script to store information received from various sources in JSON format into a MariaDB database. Not all sources will be fully trusted and some fields may legitimately contain quotes and other special characters (thus input must be escaped) and not all database fields are required. I need to programmatically build the SQL INSERT query, only adding the fields that are present. (I'm doing conformity checking before this to make sure processed objects are valid before putting them into the database).

I've seen this answer on how to correctly escape using pymysql, however, this assumes you know at the time of writing the script which fields will be present.

insertKeys = 'INSERT INTO TableName('
insertValues = ') VALUES ('
for key, value in Values:
    insertKeys = insertKeys + key + ','
    insertValues = insertValues + "'" + value + "',"
insertKeys = insertKeys[:-1]
insertValues = inserValues[:-1] + ')'
sqlCommand = insertKeys + insertValues

What is the safe way to build this query, and/or what characters do I need to escape to what in my conformity checking functions to then safely build the query in this way?

https://bobby-tables.com/ anyone ?-) – bruno desthuilliers Dec 18 '19 at 12:08 — bruno desthuilliers, Dec 18 '19 at 12:08

score 1 · Accepted Answer · answered Dec 18 '19 at 12:15

The proper way is not to try and sanitize / escape the values yourself but to properly use your db connector

fields = []
vals = []
for key, val in values:
    fields.append(key)
    vals.append(val)

fields = ", ".join(fields)
placeholders = ", ".join(["%s"] * len(fields))
sql = "insert into tablename({}) values({})".format(fields, placeholders)
cursor.execute(sql, values)

Python3: Escape MySQL values in programmatically built query

1 Answers1