Best practices for protect applications agains Sql injection.

pepone.onrez wrote:

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.

Thanks in advanced
José Manuel, Gutíerrez de la Concha Martínez.

SQL injection vulnerabilities are a product of the coding, not the
database. In a typical sql injection vulnerability, the code (typically
PHP or ASP, hopefully PHP) fails to sanitize the input of a parameter to
a query (removing ; among other things), but the db is acting properly
in such a situation.

For example the query "SELECT * FROM users WHERE username =
'$username';" is a pretty typical PHP generated query. if $username is
input as foobar then the query "SELECT * FROM users WHERE username =
'foobar';" would work as intended. However if the username was "foobar';
DELETE FROM users;" then the query would become
"SELECT * FROM users WHERE username = 'foobar'; DELETE FROM users;'"
which is a perfectly legal query (except the last ' but it won't make
much of a difference) and the db is acting as designed. It is the
responsibility of the code to sanitize the input to keep this from
happening by removing special characters such as ; and ' so there is no
way (AFAIK) to utilize postgresql settings to protect against SQL injection.

Check out this page:
http://www.acunetix.com/websitesecurity/sql-injection.htm
and this page: http://www.acunetix.com/websitesecurity/sql-injection2.htm
for more information.

--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)

In response to pepone.onrez <pepone.onrez@gmail.com>:

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.

http://www.potentialtech.com/cms/node/49

--
Bill Moran
http://www.potentialtech.com

pepone.onrez wrote:

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.

What are you using on the application side? For instance, with PHP, you
might want to look into the PEAR MDB2 package (specifically, the
prepared statements).

brian

"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.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Thanks all you, i will use prepared queries for all my functions after now.

BTW i using Qt-4 postgres drivers from c++ not php. I launch this question
because i read that each day more are more applications are compromised with
this class of attacks.

Thanks again.

On Jan 23, 2008 9:45 PM, brian <brian@zijn-digital.com> wrote:

On Wed, 23 Jan 2008 21:34:31 +0000
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.

Once you've to build up prepared queries dynamically is there any
tool, framework, practice that can help you to stay away from sql
injection?

I'd say that queries can still be built with prepackaged static parts
and that real external input should just come in in forms of
parameters... so a DB abstraction layer or an ORM should help too...
maybe at the cost of some performance.
Otherwise you build up your specialised DB AL that assemble queries
from prepackaged static parts.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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.