Is there a switch (php side or pg side) to avoid things like:

pg_query("select id from table1 where a=$i");

into becoming

pg_query("select id from table1 where a=1 and 1=1; do something
nasty; -- ");

So that every
pg_query(...) can contain no more than one statement?

thanks

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

mail@webthatworks.it (Ivan Sergio Borgonovo) writes:
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- ");
>
> So that every
> pg_query(...) can contain no more than one statement?

The conventional approach to this sort of thing is to use prepared
statements:

http://ca3.php.net/manual/en/function.pg-prepare.php

In effect, you set up the query beforehand, pre-parameterizing.

<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");

// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');

// Execute the prepared query. Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

// Execute the same prepared query, this time with a different parameter
$result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));

?>

Assuming that PHP is actually using PostgreSQL prepared statements
(and not just faking things behind your back), this should nicely
address the problem of injection attacks.
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://linuxfinances.info/info/linuxdistributions.html
The average woman would rather have beauty than brains because the
average man can see better than he can think.

On Fri, 11 Apr 2008 14:27:09 -0500
"Adam Rich" <adam.r@sbcglobal.net> wrote:

> > Is there a switch (php side or pg side) to avoid things like:
> >
> > pg_query("select id from table1 where a=$i");
> >
> > into becoming
> >
> > pg_query("select id from table1 where a=1 and 1=1; do something
> > nasty; -- ");
>
> Ideally, you'd use this:
>
> pg_query_params('select id from table1 where a=$1', array($i));
>
> http://us2.php.net/manual/en/functio...ery-params.php
>
> Alternately, you can do this:
>
> $i = pg_escape_string($i);
> pg_query(" select id from table1 where a='$i' ");

I'd try to be clearer.
The purpose of my question was not how to avoid sql injection... but
how to make it harder to exploit it.
My premise is that someone will do mistakes in the php code and I'd
like to mitigate the effect of these mistakes.

I know that even if you just permit one statement for each pg_query
you can still use blind sql injection to download whole tables etc...
but permitting more than one statement make things MUCH easier.

Up to my knowledge blind sql injection requires a lot of statement
and a lot of errors that will end up in my logs so I'll have a chance
to fix the error etc...

Prepared statements does not fit with part of the framework I'm
working with. And still I'm looking for a security net even in the
case someone is not respecting the policies.

thx

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"My premise is that someone will do mistakes in the php code and I'd like to
mitigate the effect of these mistakes."

- Prepared statements is the only "bulletproof" technique

- You can use a database abstraction layer (there are more than many
libraries for PHP) Fast to implement, all queries goes through some form of
filter

- Recommended solution - use database abstraction & AUDIT your code / grabs
all SQL statements / create a summary and make sure then each statement is
safe

$db->Execute("select id from table1 where integer =" (int)$i);
$db->Execute("select * from table1 where string =" $db->quote($i));

Database abstractions alone often give you a false sense of security
especially if you use third part / open source solutions

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of Ivan Sergio
Borgonovo
Sent: April 11, 2008 5:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL injection, php and queueing multiple statement

On Fri, 11 Apr 2008 14:27:09 -0500
"Adam Rich" <adam.r@sbcglobal.net> wrote:

> > Is there a switch (php side or pg side) to avoid things like:
> >
> > pg_query("select id from table1 where a=$i");
> >
> > into becoming
> >
> > pg_query("select id from table1 where a=1 and 1=1; do something
> > nasty; -- ");
>
> Ideally, you'd use this:
>
> pg_query_params('select id from table1 where a=$1', array($i));
>
> http://us2.php.net/manual/en/functio...ery-params.php
>
> Alternately, you can do this:
>
> $i = pg_escape_string($i);
> pg_query(" select id from table1 where a='$i' ");

I'd try to be clearer.
The purpose of my question was not how to avoid sql injection... but
how to make it harder to exploit it.
My premise is that someone will do mistakes in the php code and I'd
like to mitigate the effect of these mistakes.

I know that even if you just permit one statement for each pg_query
you can still use blind sql injection to download whole tables etc...
but permitting more than one statement make things MUCH easier.

Up to my knowledge blind sql injection requires a lot of statement
and a lot of errors that will end up in my logs so I'll have a chance
to fix the error etc...

Prepared statements does not fit with part of the framework I'm
working with. And still I'm looking for a security net even in the
case someone is not respecting the policies.

thx

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 12 Apr 2008 11:11:48 -0400
"Jonathan Bond-Caron" <jbondc@gmail.com> wrote:

> "My premise is that someone will do mistakes in the php code and
> I'd like to mitigate the effect of these mistakes."
>
> - Prepared statements is the only "bulletproof" technique

I'm not looking for something bullet proof, I'm looking for one more
mitigating factor.
I already use an "fprint" technique that cast or escape sql input
before passing it to pg_query... but being able to queue 2 statements
make it much easier to reach whatever target the attacker may have.

I hate to make comparisons but up to my knowledge:
- MySQL drivers for php don't let you queue more than one statement
in a query (that's not the right solution)
- MS SQL has a server switch that let you chose if you'd like to have
more than one statement or not

As said... there are blind sql injection techniques that once the
door is open will let the attacker download the whole content of a DB.
But generally that requires time and it is achieved causing sql
errors. Errors get logged. Log analysis may spot the problem before
it is too late.
I know that other techniques rely on measuring execution time... but
well you've already put the bar much higher than letting slip in

select id, name from table1 where id=7 and 1=1; drop table table2; --

Somehow the sql statement is parsed along the way and you can't just
skip everything that is past a ; since

select id, name from table1 where name ilike '%;';

is a legit statement.

And it should be a switch... since you may need to load functions
and functions generally contain multiple statement separated by ; or
you may have to execute more than one statement in one transaction...
Isolating the code that can issue multiple statement from the one
that can't would come handy.

Is there such a switch in the php driver for pg? or is it in the
server cfg? If there is not such a thing it would be nice to have it.

I may sound naive but having a way to protect the DB from this kind
of injections looks as a common problem, I'd thought there was
already a common solution.
If there is not such a switch is there a reason?

> - Recommended solution - use database abstraction & AUDIT your
> code / grabs all SQL statements / create a summary and make sure
> then each statement is safe

While this are other barriers to fight coding errors they have a much
higher cost than just being able to block queued statement.

> Database abstractions alone often give you a false sense of security
> especially if you use third part / open source solutions

It is exactly that false sense of security I'm trying to fight,
placing several barriers on the way of a potential attacker.

Being able to stop queued statements seemed a cheap barrier but with a
reasonably good ROI.

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I may sound naive but having a way to protect the DB from this kind
> of injections looks as a common problem, I'd thought there was
> already a common solution.

Use prepared statements.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 12 Apr 2008 12:39:38 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > I may sound naive but having a way to protect the DB from this
> > kind of injections looks as a common problem, I'd thought there
> > was already a common solution.
>
> Use prepared statements.

Yeah... but how can I effectively enforce the policy that ALL input
will be passed through prepared statements?

If I can't, and I doubt there is a system that will let me enforce
that policy at a reasonable cost, why not providing a safety net that
will at least raise the bar for the attacker at a very cheap cost?

If programmers didn't make errors or errors where cheap to find there
wouldn't be any sql injection problem.

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Apr 11, 2008 at 9:21 PM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- ");
>
> So that every
> pg_query(...) can contain no more than one statement?

Well, use prepared statements.

Apart from that, make it impossible to "do something nasty". Your
php_db_user should be
allowed as little as possible. Specifically:
* she should not be owner of the tables/other objects -- this way you are safe
from nasty "DROP TABLE"s and the like.
* you should decide where she is allowed to INSERT/UPDATE/DELETE, the latter
two are the most dangerous ones.
* you should make use of referential integrity constraints -- so evil
DELETE or UPDATE
will probably fail on these.
* you should provide PL/pgSQL stored procedures to update your vital
data. So evil
bulk delete/update will be harder to accomplish (if your evildoer can
craft exploit to
do it, he probably already has a lot of access to your system ).

....oh and think about isolating read-only acces (read only user) from
rw-user -- if
that sounds reasonable to do so.

Regards,
Dawid

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ivan Sergio Borgonovo wrote:
> Yeah... but how can I effectively enforce the policy that ALL input
> will be passed through prepared statements?
>

Code reviews are about the only way to enforce this.


> If I can't, and I doubt there is a system that will let me enforce
> that policy at a reasonable cost, why not providing a safety net that
> will at least raise the bar for the attacker at a very cheap cost?
>

How do you do this? Disallow string concatenation and/or variable
interpolation for any string that's going to be shipped off to the
database? Do you parse the SQL string according to the rules of any
backend database you might be talking to, to see if you have a where
clause not using a prepared statement? i.e. - Nothing is going to work here.

You're stuck with making sure developers know the most rudimentary
things about talking to a database.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

paul rivers wrote:
> Ivan Sergio Borgonovo wrote:
>> Yeah... but how can I effectively enforce the policy that ALL input
>> will be passed through prepared statements?
>>
>
> Code reviews are about the only way to enforce this.

That's not entirely true - if you have a policy that says thou-shalt-not-use
non-prepared statements then an automated (nightly) grep of the source tree is
going to find calls that don't follow that policy - these can be automatically
emailed to you/your project enforcer. Couple that with a process step that
enforces a grep for offending statements as part of transferring files to the
live servers and you're about done.

I'm assuming that the current policy would not allow just anyone to upload any
code to critical live systems anyway.

>
>
>> If I can't, and I doubt there is a system that will let me enforce
>> that policy at a reasonable cost, why not providing a safety net that
>> will at least raise the bar for the attacker at a very cheap cost?
>>
>
> How do you do this? Disallow string concatenation and/or variable
> interpolation for any string that's going to be shipped off to the
> database? Do you parse the SQL string according to the rules of any
> backend database you might be talking to, to see if you have a where
> clause not using a prepared statement? i.e. - Nothing is going to work
> here.
>
> You're stuck with making sure developers know the most rudimentary
> things about talking to a database.
>
>
>