Re: a stored procedure ..with integer as the parameter - Mailing list pgsql-general

From Harald Fuchs
Subject Re: a stored procedure ..with integer as the parameter
Date
Msg-id 877jc1u5cr.fsf@srv.protecting.net
Whole thread Raw
In response to Re: a stored procedure ..with integer as the parameter  (SCassidy@overlandstorage.com)
List pgsql-general
In article <33c6269f0510241421p2802061dhe79b3d99495b8087@mail.gmail.com>,
Alex Turner <armtuk@gmail.com> writes:

>      delete * from user; > select * from table where my_id=$in_value > Am
>      I just smoking crack here, or does this approach have some merit?
>      The former :-) The correct defense against SQL injection is proper
>      escaping, not quoting.  How about $in_value = '1''; delete from
>      user'?

> This would be escaped by magic_quotes resulting in:
> select * from table where my_id='\'1\'\'; delete from user \'', which would
> result in an error, and a failed attack would it not, which would be a good
> thing?

If your "magic_quotes" are magic enough to not blindly surrounding the
argument in quotes, but also escape dangerous chars like "'" inside
the argument, then you're safe.

> I tried to create this scenario, but in a trasactional environment, it
> executes, but blew the transation so the data never committed as the select
> query generated an error with the insert on the end...

... and that's exactly what it should do.  You just need to catch the error
and generate a meaningful error message.

pgsql-general by date:

Previous
From: "Matthew T. O'Connor"
Date:
Subject: Re: [ANNOUNCE] PostgreSQL 8.1 Beta 4
Next
From: Zoltán Dudás
Date:
Subject: Re: STL problem in stored procedures