[Python-3000] DB API SQL injection issue
jgarber at ionzoft.com
Tue May 1 21:14:05 CEST 2007
In PEP 249 (Python Database API Specification v2.0), there is a
paragraph about cursors that reads:
Prepare and execute a database operation (query or
command). Parameters may be provided as sequence or
mapping and will be bound to variables in the operation.
Variables are specified in a database-specific notation
(see the module's paramstyle attribute for details). 
I propose that the second parameter to execute() is changed to be a
required parameter to prevent accidental SQL injection vulnerabilities.
Why? Consider the following two lines of code
cur.execute("SELECT * FROM t WHERE a=%s", (avalue))
cur.execute("SELECT * FROM t WHERE a=%s" % (avalue))
It is easy for a developer to inadvertently place a "%" operator instead
of a "," between the two parameters. In this case, python string
formatting rules take over, and un-escaped values get inserted directly
into the SQL - silently.
After using standard string formatting characters like "%s" in the
string, and it is quite natural to place a % at the end.
The requirement of the second parameter would eliminate this
possibility. None would be passed (explicitly) if there are no
My rational for this is based:
1. partly on observation of code with this problem.
2. partly on the rationale for PEP 3126 (Remove Implicit String
>From PEP 3126: Rationale for Removing Implicit String Concatenation
Implicit String concatentation can lead to confusing, or even
def f(arg1, arg2=None): pass
f("abc" "def") # forgot the comma, no warning ...
# silently becomes f("abcdef", None)
or, using the scons build framework,
sourceFiles = [
#...many lines omitted...
It's a common mistake to leave off a comma, and then scons complains
that it can't find 'foo.cbar.c'. This is pretty bewildering
even if you *are* a Python programmer, and not everyone here is.
I know that this is not a functional problem, but perhaps a safeguard
can be put in place to prevent disastrous SQL injection issues from
For your consideration.
Senior Systems Engineer
More information about the Python-3000