Re: Best practices for protect applications agains Sql injection.

On Jan 23, 2008 3:34 PM, Gregory Stark <stark@enterprisedb.com> wrote:
> "pepone.onrez" <pepone.onrez@gmail.com> writes:
>
> > Hi all
> >
> > I interesting in the protect my applications that use postgresql as is
> > database backend from Sql Injections attacks, can any recommend me best
> > pratices or references to protected postgres from this kind of malicious
> > users.
>
> I strongly urge people to adopt a policy of using prepared queries except when
> absolutely necessary. If all user-provided data is passed to the database as
> parameters to a prepared query then you should never need to worry about SQL
> injection.
>
> It's possible to always quote your parameters before inserting them into the
> query but it's much more error-prone. It's also much harder to look at a piece
> of code and be sure it's correct. If you religiously use prepared queries then
> any variables interpolated directly into the query stand out like sore thumbs.

Two points.  1: Only grant the access needed to the user.  i.e. if
it's only going to be reading from the, then don't use an account that
anything other than select privaleges. 2: I don't find use of
pg_escape_string() to be all that error prone.