[COLUG] Postgres / Python

[richard hornsby]

On Mon, Jun 23, 2008 at 7:14 PM, Mark Erbaugh <mark at microenh.com> wrote:
> On Mon, 2008-06-23 at 18:54 -0400, Aaron Howard wrote:
>> No matter what db when doing web work that uses a db make sure to
>> scrub all user input to avoid SQL injection woes.
>
> Aaron,
>
> Thanks.  Is there a good reference to the various SQL injection
> techniques and how to avoid them?

Specifically how to avoid them is language dependent.  Set aside
stored procedures for a minute, assuming your SQL is in your code,
generally it involves parameterizing your queries anytime you accept
input from an uncontrolled source.  You can do some limited input
parsing yourself, ie wrapping variables in quotes - but this is
generally error prone and time consuming to get exactly right.

For background, basically SQL injection involves a visitor tricking
your SQL server into executing statements you never intended.  This is
generally done by manipulating the query string in the URL or a POST
variable.  For a super-primitive example, assume that you take the URL
variable "id" literally with no quoting and use it in a query:

http://www.mysite.com/foo?id=1

Your query: "SELECT username FROM users WHERE id=1"

with a malicious URL

http://www.mysite.com/foo?id=1;DROP%20table%20users;

becomes: "SELECT username FROM users WHERE id=1;DROP table users;"

As for mitigating, coldfusion looks something like so

<cfquery name="foo" datasource="somedsn">
SELECT uid,name
FROM users
WHERE phonenumber=<cfqueryparam value='#usersuppliedvalue#'/>
</cfquery>

It has been a while since I've done much PHP, but you have several
options for access to a persistence layer.  With mysql, you might end
up using something like mysqli to prepare your statements

http://us.php.net/manual/en/mysqli.prepare.php suggests

$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?"));
$stmt->bind_param("s", $city);
$stmt->execute();

Also, http://us.php.net/security.database.sql-injection

This is pretty similar to perl's prepared statements using DBI.

I'm not as familiar with python, but a quick search digs up

http://www.python.org/dev/peps/pep-0249/

Which looks to apply the same basic techniques as PHP/Perl, using
prepared statements.  My assumption is that with all of these things
that the parsing engine disallows any execution of anything in a
parameter.

A couple of OSU CSE faculty have an interesting (and readable) paper
on the topic and one generalized approach, "Using Parse Tree
Validation to Prevent SQL Injection Attacks"

http://www.cse.ohio-state.edu/~paolo/research/
http://www.cse.ohio-state.edu/~paolo/research/publications/sem05.pdf (paper)

-rj