Re: Protection from SQL injection

* Gurjeet Singh <singh.gurjeet@gmail.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; WITHc_jobrole = '1' SELECT * FROM emp
WHEREjobrole = 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@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.