Re: More PHP DB abstraction layer stuff

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