String quoting (SQL injection) issue.

[Wouter van Marle]

Hi all,

From discussion and advice on the mod_python maillist about sql injection, I'm trying to improve my web script.

Current script uses the vulnerable way:
cursor.execute("""SELECT %s FROM %s WHERE de="%s";"""% (language, base, w))

I understand it's safer to use:
cursor.execute("SELECT %s FROM %s WHERE de=%s;"% (language, base, w))
and let MySQLdb insert the quotes.
Doing so however gives SQL errors!
I'm using 1.2.0final on Mandriva 10.2, and same problem with 1.2.1g2 on Debian Sarge.

The results:
>>> cursor.execute("""SELECT %s FROM %s WHERE de="%s";"""% (language, base, w))
1L
>>> cursor.execute("SELECT %s FROM %s WHERE de=%s;"% (language, base, w)) Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_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 '' at line 1")

Please advice!

Wouter.

[Andy Dustman]

Because you have a mix of names and parameters, you really need something like this:

query = """SELECT %s FROM %s WHERE de=%%s""" % (language, base))
cursor.execute(query, (w,))

I also noticed just now you were quoting string literals in your query with double quote (") instead of single quote ('). While double quotes are legal for MySQL, single quote is correct for ANSI SQL.

[Andy Dustman]

Read PEP-249.

http://www.python.org/dev/peps/pep-0249/

[Wouter van Marle]

> Read PEP-249.

OK just did that.
No word on string replacement (except for a mention that one can enter Python strings) and where it can be used and where not. It does not answer my question at all.

Wouter.

[Andy Dustman]

And... don't put semicolons (;) in your queries.

And... you can't pass parameters to be used as column or table names.

[Wouter van Marle]

> And... don't put semicolons (;) in your queries.

Well that's easy enough.

> And... you can't pass parameters to be used as column or table names.

That part works fine; no problems. I'm doing just that in a few dozen places in my code, and get exactly the expected results.

Wouter.