[Python-3000] DB API SQL injection issue

Jason Garber jgarber at ionzoft.com
Tue May 1 21:14:05 CEST 2007


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.







More information about the Python-3000 mailing list