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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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