搜索关键字：

• python mysqldb anti sql injection

参考链接：

• http://bobby-tables.com/python.html
• https://wiki.python.org/moin/DbApiFaq
• https://www.python.org/dev/peps/pep-0249/#paramstyle
• https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute
• =
• http://stackoverflow.com/questions/7929364/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries
• http://stackoverflow.com/questions/3617052/escape-string-python-for-mysql
• http://stackoverflow.com/questions/775296/python-mysql-with-variables
• =
• [Python]Python预编译语句防止SQL注入
• =
• SQL Injection Wiki | References
• =
• http://stackoverflow.com/questions/9059027/if-i-receive-post-data-with-flask-put-that-data-into-a-wtforms-form-and-it-succ

参考解答：

Using the Python DB API, don’t do this:

# Do NOT do it this way.
cmd = "update people set name='%s' where id='%s'" % (name, id)
curs.execute(cmd)

Instead, do this:

cmd = "update people set name=%s where id=%s"
curs.execute(cmd, (name, id))

Note that the placeholder syntax depends on the database you are using.

'qmark'     Question mark style,
                e.g. '...WHERE name=?'
'numeric'   Numeric, positional style,
                e.g. '...WHERE name=:1'
'named'     Named style,
                e.g. '...WHERE name=:name'
'format'    ANSI C printf format codes,
                e.g. '...WHERE name=%s'
'pyformat'  Python extended format codes,
                e.g. '...WHERE name=%(name)s'

The values for the most common databases are:

>>> import MySQLdb; print MySQLdb.paramstyle
format
>>> import psycopg2; print psycopg2.paramstyle
pyformat
>>> import sqlite3; print sqlite3.paramstyle
qmark

So if you are using MySQL or PostgreSQL, use %s (even for numbers and other non-string values!) and if you are using SQLite use ?

==

How do I pass parameters to the cursor.execute method?

Don’t use the ‘%’ concatenation operator, pass them as a series of extra parameters. For instance

>>> cursor.execute("SELECT * FROM my_table WHERE my_column = '%s'" % "column_value") 

May do what you want, but more by accident than design. If you change it to;

>>> cursor.execute("SELECT * FROM my_table WHERE my_column = %s", "column_value") 

Then the DB-API module will make sure your value is correctly escaped and turned into an object appropriate for the database.

==

execute()函数本身就有接受SQL语句变量的参数位，只要正确的使用（直白一点就是：使用”逗号”，而不是”百分号”）就可以对传入的值进行correctly转义，从而避免SQL注入的发生。

==

execute(sql[, parameters])

Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).

Here’s an example of both styles:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print cur.fetchone()

execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript() if you want to execute multiple SQL statements with one call.

=EOF=