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

Re: Protection from SQL injection

From: Aidan Van Dyk <aidan(at)highrise(dot)ca>
To: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,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-30 17:50:25
Message-ID: 20080430175025.GC6337@yugib.highrise.ca (view raw or flat)
Thread:
Lists: pgsql-hackers
* Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> [080430 13:38]:
 
> Well, if the the query was:
> 
> WITH CONSTANT c_jobrole = <value from a FORM text field>, CONSTANT c_dept =
> 10
> SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;
> 
> And if the attack supplied a value 'clerk OR 1=1' the final query (after
> replacing constants) would look like this:
> 
> SELECT * FROM emp WHERE jobrole = 'clerk OR 1=1' and deptno = 10;
> 
> The attacker was not able to inject any new code there.
> 
> (reiterates: and let postgres allow literals only in the WITH clause)

Sure, but you've only changed the attack vector:

set <value from a form text field> to be:
	something' SELECT 1; DELETE FROM users; WITH c_jobrole = '1

And you get:
	WITH CONSTANT cjobrole = 'someting' SELECT 1; DELETE FROM users; WITH
	c_jobrole = '1' SELECT * FROM emp WHERE jobrole = c_jobrole;

Sure, if the <value from form> is properly escaped and sanitized, it
wouldn't be a problem.  But if you are sure that all data is properly
escaped and sanitized, then the whole "don't allow literals" extra
protection isn't needed either, and you don't need to go looking for
ways to avoid the literals in queries.


-- 
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

In response to

pgsql-hackers by date

Next:From: Gurjeet SinghDate: 2008-04-30 17:54:21
Subject: TidScan needs handling of a corner cases
Previous:From: Gurjeet SinghDate: 2008-04-30 17:37:55
Subject: Re: Protection from SQL injection

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