Use the placeholder described in Cursor.execute (self, query, args = None) when building queries using Python's MySQL-python (MySQLdb) module.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Create the following table for testing.
mysql> select * from testdb.person;
+------+--------+
| id | name |
+------+--------+
| 1 | foo |
| 2 | bar |
+------+--------+
bad.py
import MySQLdb
def select(name):
connection = MySQLdb.connect(db='testdb', user='testuser')
cursor = connection.cursor()
cursor.execute("select * from person where name='%s'" % name)
print("[query]")
print(cursor._last_executed)
print("[result]")
result = cursor.fetchall()
for rec in result:
print(rec)
If you do select ("foo "), it looks like it's working nicely.
[query]
select * from person where name='foo'
[result]
(1L, 'foo')
But if you do something like select ("foo' or name = name--")
[query]
select * from person where name='foo' or name=name-- '
[result]
(1L, 'foo')
(2L, 'bar')
And SQL injection is done.
good.py
import MySQLdb
def select(name):
connection = MySQLdb.connect(db='testdb', user='testuser')
cursor = connection.cursor()
cursor.execute("select * from person where name=%s", name)
print("[query]")
print(cursor._last_executed)
print("[result]")
result = cursor.fetchall()
for rec in result:
print(rec)
I changed only the argument part of cursor.execute ().
With select ("foo "), it works the same as in the previous example.
[query]
select * from person where name='foo'
[result]
(1L, 'foo')
Even if you do select ("foo' or name = name-- "), it will escape properly.
[query]
select * from person where name='foo\' or name=name-- '
[result]
http://stackoverflow.com/questions/1947750/does-python-support-mysql-prepared-statements
Recommended Posts