[Python-3000] DB API SQL injection issue (original) (raw)

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