Problem inserting into Postgres (PgSQL) database
gh at ghaering.de
Thu Jun 19 18:23:09 CEST 2003
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
A DB-API adapter always does the quoting right, even if there are
'strange' characters in the string. The OP basically always quoted
"'%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.
More information about the Python-list