Problem inserting into Postgres (PgSQL) database

[Gerhard Häring]

Dave Reed wrote:
> On Thursday 19 June 2003 06:52, Gerhard Häring wrote:
>>mupeso at arc.sn wrote:
>>>[...]
>>>sql= "insert into radcheck2 (uid,login,passwd,shell,homedir,domain_name,acc_expired) values ("+str(uid) +",'"+login+"','"+passwd+"','"+shell+"','"+homedir+"','"+dom_name+"','n')"
>>[...]

>>Second, your style of constructing SQL is a bad one. Use the DB-API way 
>>of quoting the various datatypes instead:
>>[...]
>>The way you're constructing your SQL statement now is a security 
>>vulnerability if any of the fields can come from an untrusted source.
> 
> Could you please elaborate on how your method is more secure? Isn't it
> the same result since the %s are replaced with the values from the
> variable.

A DB-API adapter always does the quoting right, even if there are 
'strange' characters in the string. The OP basically always quoted 
strings using

	"'%s'" % value

Now if value comes from an untrusted source, this opens up the 
possibility for arbitrary SQL injection.

Let's suppose the code in question is this one:

sql = "INSERT INTO TEST(FOO) VALUES ('%s')" % value

Now an untrusted partie could make value look like this:

	value = "'); DELETE FROM TEST; --"

Which results in this SQL to be executed:

INSERT INTO TEST(FOO) VALUES (''); DELETE FROM TEST; --')

The comment "--" will make the illegal characters disappear, and 
appriate use of single quotes and semicolon make it possible to insert 
an additional SQL statement to be executed.

To safely insert strings in SQL statements, you need to at least escape 
single quotes. pyPgSQL does this already for you. This is PostgreSQL, 
where this is necessary - other databases, like Oracle can transmit SQL 
with placeholders and actual values to be inserted seperately and that's 
what their Python adapters do if you use the DB-API way of quoting.

-- Gerhard