Обсуждение: More PHP DB abstraction layer stuff

Has anyone seen/used this:

http://www.zend.com/codex.php?CID=324

It looks fairly inoccuous. It also claims to not load an entire dataset into
memory, i.e. uses cursors but I don't see where they're used, unless its
inherent in the PHP Pg interface.

One thing that always gets me is why people think quoting the ' in a string is
a security feature when they don't allow for someone giving \' in the
string. On the other hand I'm never sure how to protect against such 'odd
number of escapes' attacks. Anyone got any clues? Does PQescape do it?


--
Nigel Andrews

Nigel J. Andrews wrote:
> Has anyone seen/used this:
>
> http://www.zend.com/codex.php?CID=324
>
> It looks fairly inoccuous. It also claims to not load an entire dataset into
> memory, i.e. uses cursors but I don't see where they're used, unless its
> inherent in the PHP Pg interface.
>
> One thing that always gets me is why people think quoting the ' in a string is
> a security feature when they don't allow for someone giving \' in the
> string. On the other hand I'm never sure how to protect against such 'odd
> number of escapes' attacks. Anyone got any clues? Does PQescape do it?

If it's any help, and approach that I feel is safe is to use the PHP
functions rawurlencode() on all data as soon as it hits the page, then
use that encoded data everywhere in the PHP code (including for storage
in the database), and use rawurldecode() if/when it needs to be spat out
to a browser.

The only real disadvantage is that column widths for data storage need
to be wider, but for databases without huge resource requirements it's
not real noticeable, and the data is pretty safe in encoded form.

:-)

Regards and best wishes,

Justin Clift

> --
> Nigel Andrews
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

> One thing that always gets me is why people think quoting the ' in a string is
> a security feature when they don't allow for someone giving \' in the
> string. On the other hand I'm never sure how to protect against such 'odd
> number of escapes' attacks. Anyone got any clues? Does PQescape do it?

That just means you have to escape \ as well as '.

But the best way to deal with this is to use placeholders and prepared queries
and provide the data out of band. This completely sidesteps the issue and
guarantees you can't get it wrong by mistake ever. Mixing user-provided data
with program code is a recipe for security holes.

--
greg

could you elaborate on:

    Place holders ( those are in prepared queries, yes?)
    out of band?

1/24/2003 9:22:42 AM, Greg Stark <gsstark@mit.edu> wrote:

>
>"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
>
>But the best way to deal with this is to use placeholders and prepared queries
>and provide the data out of band. This completely sidesteps the issue and
>guarantees you can't get it wrong by mistake ever. Mixing user-provided data
>with program code is a recipe for security holes.
>
>--
>greg
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

On Fri, 24 Jan 2003, Dennis Gearon wrote:

> could you elaborate on:
>
>     Place holders ( those are in prepared queries, yes?)
>     out of band?
>
> 1/24/2003 9:22:42 AM, Greg Stark <gsstark@mit.edu> wrote:
>
> >
> >But the best way to deal with this is to use placeholders and prepared queries
> >and provide the data out of band. This completely sidesteps the issue and
> >guarantees you can't get it wrong by mistake ever. Mixing user-provided data
> >with program code is a recipe for security holes.

In perl with DBI:

$sth = $dbh->prepare("SELECT * FROM mytable WHERE id = ?");
$sth->execute($idvalue);

I didn't even know it was possible in PHP. I've never used it before.


--
Nigel J. Andrews

> On Fri, 24 Jan 2003, Dennis Gearon wrote:
>
> In perl with DBI:
>
> $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = ?");
> $sth->execute($idvalue);
>
> I didn't even know it was possible in PHP. I've never used it before.

Indeed the Perl DBI is quite a bit more solid than the PHP "abstractions". The
syntax is there in PEAR::db:

$db->getall("SELECT * FROM mytable WHERE id = ?", array($idvalue));

but there are a few problems compared to the perl DBI:

a) separating the prepare and the execute is possible but doesn't seem to work
   right. If you have two cursors active at the same time it seems to get very
   confused.

b) it seems to actually do the substitution itself of the values into the
   query which is better than doing it myself but still a lot worse than
   giving it to the database out of band. if there's a bug in the PEAR::db
   quoting it could still create a security hole.

c) (b) implies it can't be caching prepared query handles so the database has
   to parse the query each time. This is a huge lose on big queries, and it's
   one of the big advantages to using placeholders other than the security
   issues.

d) having to type array() every time is a bit annoying.



--
greg

Dennis Gearon <gearond@cvc.net> writes:

> could you elaborate on:
>
>     Place holders ( those are in prepared queries, yes?)
>     out of band?

I think by "out of band" Greg just means substituting values into a
prepared query rather than glomming everything into an SQL string by
yourself.  For example, in Perl DBI you'd do something like:

$stmt = $dbh->prepare("select * from mytable where first_name = ?");
$ret_val = $sth->execute("Fred");   # might come from a web form instead
@row = $sth->fetchrow_array();

The database driver is responsible for turning the '?' in the query
into a properly-quoted and escaped value, or otherwise supplying it to
the database.  The '?' is a placeholder.

-Doug

Doug McNaught <doug@mcnaught.org> writes:

> $stmt = $dbh->prepare("select * from mytable where first_name = ?");
> $ret_val = $sth->execute("Fred");   # might come from a web form instead
> @row = $sth->fetchrow_array();
>
> The database driver is responsible for turning the '?' in the query
> into a properly-quoted and escaped value, or otherwise supplying it to
> the database.  The '?' is a placeholder.

Except that that's not what the driver does, at least not for databases that
are capable of doing better. It sends the placeholders to the database as they
are. (Or in a different syntax like :1 :2 :3 for Oracle for example.)

The database constructs a plan to run the query for _any_ value of the
placeholders. Then when you call execute the driver sends the arguments and
the database uses them to execute the plan.

There is no chance at all at mixing up the data and the code with this
approach. This is a better security technique than escaping the data because
there is always the chance that an unknown or newly introduced syntax fails to
be escaped properly. By passing the user supplied data in a separate channel
(that's what out of band means) you avoid any possibility of mixing the two.

With drivers like PEAR::db that don't seem to actually support this at least
you're not trusting yourself to get the escaping right, the driver is
responsible for it and it's more likely to get it right. But that's still
nowhere near as good from a security standpoint as passing them in a separate
channel completely.

There is a downside to this approach on the performance front. If the query
takes a long time to execute and involves data that doesn't change much or has
peculiar distributions, the database might have been able to make use of the
particular values to optimize the query better. This is rare in practice for
OLTP applications, which includes virtually all web sites. And for short
queries prepared queries run faster because they don't need to be parsed for
every execution.

In any case, in my book the security factor far outweighs the optimization
issue.

--
greg

Greg Stark <gsstark@mit.edu> writes:

> Doug McNaught <doug@mcnaught.org> writes:
>
> > $stmt = $dbh->prepare("select * from mytable where first_name = ?");
> > $ret_val = $sth->execute("Fred");   # might come from a web form instead
> > @row = $sth->fetchrow_array();
> >
> > The database driver is responsible for turning the '?' in the query
> > into a properly-quoted and escaped value, or otherwise supplying it to
> > the database.  The '?' is a placeholder.
>
> Except that that's not what the driver does, at least not for databases that
> are capable of doing better. It sends the placeholders to the database as they
> are. (Or in a different syntax like :1 :2 :3 for Oracle for example.)

Right, but currently for PG (this being a PG list :) it just does the
quoting/escaping and builds the query itself.  It's still safer than
doing it by hand, as long as the database driver is trustworthy and
knows the databases escaping conventions.

I think there has been discussion about extending the protocol to
allow Oracle-style prepared statement execution, but right now it
isn't supported.  Prepared queries only went in in 7.3, after all...

-Doug

At 02:44 AM 1/25/03 +1030, Justin Clift wrote:

>If it's any help, and approach that I feel is safe is to use the PHP
>functions rawurlencode() on all data as soon as it hits the page, then use
>that encoded data everywhere in the PHP code (including for storage in the
>database), and use rawurldecode() if/when it needs to be spat out to a browser.
>
>The only real disadvantage is that column widths for data storage need to
>be wider, but for databases without huge resource requirements it's not
>real noticeable, and the data is pretty safe in encoded form.

I prefer an approach where filters are kept separate. You have different
input filters so that your program can deal with each different input properly.

I doubt your program can do much with rawurlencoded cgi parameters without
decoding them.

You then have different output filters so the different programs (and
contexts) your program sends output to can deal with the output.

Using the same filter for everything seems to be a popular habit in the PHP
community. Magic quotes etc. That sort of thing tends to produce the
"backslash everywhere" syndrome, corrupting data needlessly. Personally it
gives me a bad impression of the thought that went into the design of many
PHP "features".

Cheerio,
Link.