mysql.connector.errors.ProgrammingError: 1064 (42000) & XML parsing

Question

I am trying to populate a column with an XML string in MYSQL. When I run my python script I am getting the below error. I am using the filename as the id, I believe I am parsing the XML incorrectly somehow.

mysql.connector.errors.ProgrammingError: 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 '

cursor = cnx.cursor(buffered=True)

tree = ET

for a in os.listdir(mydir):
    if a.endswith(".xml"):
        print os.path.basename(a)
        j=ET.parse(mydir+"/"+a)
        a = ''.join(a.split())
        a = a[:-9]
        root = j.getroot()
        xmlstr = ET.tostring(root, encoding='utf8')
        xmlstr
        print a
        query = """UPDATE %s.%s SET col1= (%s) WHERE col2 = (%s)""" % (mysql_db,mysql_table,xmlstr,a)
        cursor.execute(query)

Do you know what is getting an error?

When I print query out prior to executing I get the below

UPDATE triageprediction.triagepredictionsamples SET CtXml= (<?xml version='1.0' encoding='utf8'?>
<CAS version="2">
    <uima.cas.Sofa _id="3" _indexed="0" mimeType="text" sofaID="_InitialView" sofaNum="1" sofaString="The Enmore Practice
&#10;
more xml.....
    </uima.cas.FSArray>
</CAS>) WHERE REFERRALID = (1187116)
Traceback (most recent call last):
  File "C:\XML_Formatter_v2\loadxml.py", line 47, in <module>
    cursor.execute(query)
  File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 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 '2">
    <uima.cas.Sofa _id="3" _indexed="0" mimeType="text" sofaID="_InitialView' at line 1

Print out the query before executing. What do you see printed? — alecxe
– alecxe, Commented Dec 20, 2016 at 18:00
Hi @alecxe, I have updated the post to include it, even if I add ("%s") it fails. I know I am doing something really stupid, just not sure what. — simsinator
– simsinator, Commented Dec 22, 2016 at 9:41

alecxe · Accepted Answer · 2016-12-22 14:57:24Z

1

It looks like your problem is in the way you are putting parameters into the query. Currently, you are using string formatting which is not only dangerous (see SQL injections), but is also a source of problems with putting the quotes correctly, escaping and data type conversions.

You should be using parameterized queries:

query = """
    UPDATE 
        {db}.{table} 
    SET 
        col1 = %s 
    WHERE 
        col2 = %s""".format(db=mysql_db, table=mysql_table)
cursor.execute(query, (xmlstr, a))

Note how the xmlstr and a are passed to execute() separately. Also note that we are not putting anything around the %s placeholders - the MySQL database driver would handle the quoting and escaping automatically.

But, we cannot parameterize database, table and column names, for this, we'll use string formatting. Though, make sure to properly sanitize the mysql_db and mysql_table variable values.

answered Dec 22, 2016 at 14:57

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NI6 Over a year ago

hey @alecxe is parameterizing table names still unsupported?

alecxe Over a year ago

@NI6 yep, from what I know, it is not and likely never be :)

Collectives™ on Stack Overflow

mysql.connector.errors.ProgrammingError: 1064 (42000) & XML parsing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related