Parameterized queries using MySQLdb in Python 3

Question

I'm attempting to insert data into a MySQL database through a Python script using parameterized queries rather than formatting the parameters into a string and opening the application up to SQL injection.

Here is the Python code:

#!/usr/bin/python3

import MySQLdb as mdb

conn = mdb.connect("localhost", "fakeuser", "fakepassword", "testdb")
c = conn.cursor()
c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
conn.commit()
conn.close()

When I run the script, I get the following stack trace:

Traceback (most recent call last):
  File "./load.py", line 7, in <module>
    c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 184, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/connections.py", line 37, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 171, in execute
    r = self._query(query)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 330, in _query
    rowcount = self._do_query(q)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 294, in _do_query
    db.query(q)
_mysql_exceptions.ProgrammingError: (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 '%s)' at line 1")

However, every bit of documentation I've read suggests %s is the correct placeholder. I've tried to enclose the %s in single quotes, but that causes the statement to insert that string literally rather than substitute the given value, which is what I'd expect.

Here are the relevant software versions:

Python 3.2.3
MySQL_python-1.2.3-py3.2
MySQL 5.5.40-36.1

Note: I'm using a shared host and cannot upgrade the software.

score 0 · Accepted Answer · edited May 23 '17 at 11:50

The MySQL-Python project does not support Python 3—see the project web page, which has said "Python 3 support coming soon" for years now, and the cheese shop entry, which offers only a Python 2.7 version and says:

Python-3.0 will be supported in a future release.

There's no "-py3.2" distribution available that I've heard of (or could find by searching); your host may have something silly (like this, for example) to get the library to install on Python 3, but being able to install a library does not guarantee it will actually work.

Here's an example of someone running into the same problem on a shared host:

Today i switched to Python v3.2.3 on my remote web server at HostGator. My cgi-script which it works at python v2.6.6 now produces theses errors:

--> --> 
Traceback (most recent call last): 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 171, in execute 
r = self._query(query) 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 330, in _query 
rowcount = self._do_query(q) 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 294, in _do_query 
db.query(q) 
_mysql_exceptions.ProgrammingError: (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 '%s' at line 1")

You're right about the %s syntax being correct for the library; unfortunately, something about it isn't playing nice with Python 3. You could try using the named parameter style instead, and see if that gets you anywhere, but the best thing would be to stop using an unsupported library altogether. If your host also provides an old version of Connector/Python, for example.

Parameterized queries using MySQLdb in Python 3

1 Answers1