Problem inserting into Postgres (PgSQL) database

[Dave Reed]

On Thursday 19 June 2003 12:23, Gerhard Häring wrote:
> 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.

Ah, thanks. In my situation, I'm already handling the quoting issue
with my own Python code before executing the SQL statements. I guess I
could have saved myself some work if I'd have looked into this first.

Thanks,
Dave