Re: Protection from SQL injection

------=_Part_3000_22716252.1209577075251
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, Apr 30, 2008 at 10:58 PM, Tom Lane <tgl@[EMAIL PROTECTED]
> wrote:

> "Gurjeet Singh" <singh.gurjeet@[EMAIL PROTECTED]
> writes:
> > Maybe we can extend the SQL's WITH clause do declare the constant
along
> with
> > the query, and not separate from the query.
>
> > WITH CONSTANT c_jobrole = 'clerk', CONSTANT c_dept = 10
> > SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;
>
> [ scratches head... ]  And that will provide SQL injection protection
how?


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)


>
> Anyway, you hardly need new syntax to do that, I'd expect
>
>        WITH SELECT 'clerk' AS c_jobrole ...
>
> to accomplish it just fine.
>

I am not sure I understood this example.

Best regards,

-- 
gurjeet[.singh]@[EMAIL PROTECTED]
 gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

------=_Part_3000_22716252.1209577075251
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, Apr 30, 2008 at 10:58 PM, Tom Lane &lt;<a
href="mailto:tgl@[EMAIL PROTECTED]
">tgl@[EMAIL PROTECTED]
>&gt; wrote:<br><div
class="gmail_quote"><blockquote class="gmail_quote" style="border-left:
1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;">
<div class="Ih2E3d">&quot;Gurjeet Singh&quot; &lt;<a
href="mailto:singh.gurjeet@[EMAIL PROTECTED]
">singh.gurjeet@[EMAIL PROTECTED]
>&gt;
writes:<br>
&gt; Maybe we can extend the SQL&#39;s WITH clause do declare the constant
along with<br>
&gt; the query, and not separate from the query.<br>
<br>
&gt; WITH CONSTANT c_jobrole = &#39;clerk&#39;, CONSTANT c_dept = 10<br>
&gt; SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;<br>
<br>
</div>[ scratches head... ] &nbsp;And that will provide SQL injection
protection how?</blockquote><div><br>Well, if the the query
was:<br><br>WITH CONSTANT c_jobrole = &lt;value from a FORM text
field&gt;, CONSTANT c_dept = 10<br>

SELECT * FROM emp WHERE jobrole = c_jobrole and deptno =
c_dept;<br><br>And if the attack supplied a value &#39;clerk OR 1=1&#39;
the final query (after replacing constants) would look like this:<br><br>
SELECT * FROM emp WHERE jobrole = &#39;clerk OR 1=1&#39; and deptno =
10;<br><br>The attacker was not able to inject any new code
there.<br><br>(reiterates: and let postgres allow literals only in the
WITH clause) <br><br>
</div><blockquote class="gmail_quote" style="border-left: 1px solid
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br>
<br>
Anyway, you hardly need new syntax to do that, I&#39;d expect<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;WITH SELECT &#39;clerk&#39; AS c_jobrole
...<br>
<br>
to accomplish it just fine.<br></blockquote><div><br>I am not sure I
understood this example.<br><br>Best regards,
<br></div><div>&nbsp;<br></div></div>--
<br>gurjeet[.singh]@[EMAIL PROTECTED]
>singh.gurjeet@[EMAIL PROTECTED]
 gmail | hotmail |
indiatimes | yahoo }.com<br>
<br>EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br><br>Mail
sent from my BlackLaptop device

------=_Part_3000_22716252.1209577075251--