Thread: Re: [GENERAL] More PHP DB abstraction layer stuff

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