Skip site navigation (1) Skip section navigation (2)

Re: Protection from SQL injection

From: PFC <lists(at)peufeu(dot)com>
To: "Thomas Mueller" <thomas(dot)tom(dot)mueller(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Protection from SQL injection
Date: 2008-04-29 13:29:44
Message-ID: op.uadjrugpcigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>> zero developer pain
>
> Actually it's not zero pain, but the main problem is: there is no way
> to enforce using it.

	Sure, there is no way to enforce it (apart from grepping the source for  
pg_query() and flogging someone if it is found), but is it really  
necessary when the right solution is easier to use than the wrong solution  
? Capitalizing on developer laziness is a win IMHO, lol.

> The problem is not only quotes. The problem is all kinds of user
> input. For example: sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " +
> orderId; This is not a problem if orderId is a number. But what if
> it's a String? For example "1 AND (SELECT * FROM USERS WHERE
> NAME='admin' AND PASSWORD LIKE 'm%')". An attacker could then retrieve
> the admin password quite quickly.

	IMHO this is an example of what should never be done.

// very bad (especially in PHP where you never know the type of your  
variables)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + orderId;

// slightly better (and safe)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + int( orderId );

// correct (PHP syntax)
pg_query_params( "SELECT * FROM ORDERS WHERE ORDER_ID = $1",  
array( orderId ))
db_query( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", array( orderId ))

// correct (Python syntax)
cursor.execute( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", ( orderId, ))

The last two don't complain if orderId is a string, it will be correctly  
quoted, and then postgres will complain only if it is a string which does  
not contain a number. This is useful in PHP where you never know what type  
you actually have.

The little function in my previous mail is also useful for mysql which has  
no support for parameterized queries.


In response to

pgsql-hackers by date

Next:From: Gregory StarkDate: 2008-04-29 13:36:02
Subject: Re: Protection from SQL injection
Previous:From: Gregory StarkDate: 2008-04-29 13:26:28
Subject: Re: Proposed patch - psql wraps at window width

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group