Server-side programming

[David M. Cooke]

At some point, Alan Kennedy <alanmk at hotmail.com> wrote:

> Timo Virkkala wrote:
>> I'm creating a system with Python CGIs, that connect to a database. I'm
>> wondering about input validation. Of course I will check the length of
>> the passed parameters, to (hopefully) prevent any DOS attacks. What else
>> do I need to check? Do I need to remove any SQL from the inputs?
>> Anything else I might have overlooked?
>
> You might not need to remove SQL from your field values. Doing so
> would probably be a non-trivial string parsing exercise.
>
> Most "SQL injection" attacks would be where a cracker hopes that you
> are going to embed the contents of "username" and "password" fields
> right into a string containing an SQL query, like so
>
> mySQLString = """
> select  *
> from    users
> where   uname = "%s" and password = "%s"
> """ % (username, password)
>
> If the query returns a non-zero number of rows, then that
> username/password combination is deemed to be valid.
>
[snipped useful info on doing SQL injections]
> AFAIK, the most effective way to prevent such attacks is to disable
> any quote characters that may be present in the password, so that they
> are treated as a part of the password string, not as delimiters in the
> SQL query string. For example
>
> import re
> password = re.escape(password)
>
> Which for the values given above would now give an SQL query of
>
> select  *
> from    users
> where   uname = "alan" and password = "\"\ or\ 0\=0\ or\ password\=\""
>
> Does anyone know of a more effective approach to preventing SQL
> injection attacks?

The problem is you're trying to create the entire query string. This
means that *you* must be sure what is valid and what's invalid; what
needs quoting, and what doesn't. Are you sure the above using
re.escape will work properly for SQL queries, since it was designed
for regular expressions? Are all corner cases covered? etc.

The best way to do this is to allow the database module to do the
quoting; this is explicictly supported by Python's DB-API v2
specificiation (available as PEP 246 [1]). Most (if not all that
you'll probably use...) database modules for python conform to this.

Here's a concrete example using PySQLite: [untested code]

import sqlite
db = sqlite.connect('database.db')
cursor = db.cursor()

cursor.execute('''select * from users
                  where uname = %(uname)s and
                  password = %(password)s''',
               {'uname' : uname, 'password' : password})

row = cursor.fetchone()
if row is None:
   print "Access denied"

Note how the parameters are passed as separate arguments to
cursor.execute; the sqlite module takes care of escaping them. Note
that not all database modules support this style of quoting; check out
PEP 249 and the documentation of your specific module.

[1] http://python.org/peps/pep-0249.html

-- 
|>|\/|<
/--------------------------------------------------------------------------\
|David M. Cooke
|cookedm(at)physics(dot)mcmaster(dot)ca