[Python-3000] DB API SQL injection issue

[Jason Garber]

Hello,

In PEP 249 (Python Database API Specification v2.0), there is a
paragraph about cursors that reads:

.execute(operation[,parameters]) 
   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). [5]

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
replacements needed.

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
Concatenation).

>From PEP 3126: Rationale for Removing Implicit String Concatenation

    Implicit String concatentation can lead to confusing, or even
    silent, errors.

        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 = [
        'foo.c'
        'bar.c',
        #...many lines omitted...
        'q1000x.c']

    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
behavior
    even if you *are* a Python programmer, and not everyone here is.
[1]


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
arising needlessly.

For your consideration.

Sincerely,

Jason Garber
Senior Systems Engineer
IonZoft, Inc.