NYCPHP Meetup

[Eddie Drapkin]

On Fri, Jun 26, 2009 at 2:13 AM, Hans Zaunere<lists at zaunere.com> wrote:
>> >> Wait, are you advocating //against// prepared statements?
>> >
>> > Not at all, but when using mysql, you should emulate them.  I am
>> > actually all for "prepared" style queries, if I ever see
>> > "mysqli_real_escape_string" in someone's code, I immediately write the
>> > person off as clueless.
>>
>> What's so clueless about using mysql_real_escape_string()?  I would be
>> interested to find out.
>
> Nothing in my opinion.  Prepared statements can be a benefit in various
> situations, with those benefits being largely dictated by the database in
> use.  With MySQL, prepared statements often are an actual drag, rather than
> benefit.
>
> Consider:
>
> http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statements.html
>
> A key statement being:
>
> "Prepared execution is an efficient way to execute a statement more than
> once."
>
> Most statements aren't executed more than once in a typical web
> application/request/response cycle.
>
> Cases where I would use prepared statements:
>
> -- batch processes where the same queries are executed numerous times with
> varying data
>
> -- dealing with large data storage (blobs, etc.)
>
> H
>
>
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php
>

If, and this is an enormous if, you properly build your application
around persistent connections, you ought to be able to take advantage
of the erm, advantages of preparing your statements.  AFAIK, the cache
for prepared statements (which I'm not entirely sure MySQL even
bothers to build and use, I've heard that it doesn't and after several
hours of searching have given up looking for a conclusive answer)
exists on a per connection basis, so the persistency of your
connection ought to preserve that gain.  As a general rule, I use
parameterized queries with named parameters (a huge advantage PDO has
over MySQLi) in all my queries, because I can afford the extra wait if
the query only runs once on that connection and it's far too easy to
rely on mysqli_real_escape_string and forget to use it once and have
everything come crashing down around you.  As far as portability is
concerned, one thing I've had some level of success with is storing my
queries in XML and fetching them to be prepared, then binding and
executing, which makes it a whole hell of a lot easier to write
queries for two different databases (in my case, PostgreSQL and MySQL)
than try to implement some sort of rewriter, generator, or some other
language that gets compiled into SQL (think Doctrine).

Regarding have another layer between your code and your database
(driver), I would have to disagree (and Hans and I have had a back and
forth off list about this very thing).  Consider PDO:
PDO call -> PDO -> driver -> database
Now consider a userland implemented DBAL:
DBAL call  -> DBAL -> driver -> database

They look exactly the same! The primary difference being whether the
extra layer of abstraction is implemented in C or in the userland and
Common Wisdom has always (as far as I know, once again) suggested that
it's very rare that userland code will outperform something with the
same functionality written in C.  I think the OP's benchmarks agree,
but once again, do a few milliseconds really matter that much when the
actual query is going to steal most of the time? Probably not, unless
you're choking on CPU time, rather than I/O queue, which is very
rarely the case.  And as far as the native driver thing goes, the
difference is how the driver is implemented, but in the end it's the
same driver being used.  The main argument against PDO that I've heard
is that some of the quirks and non-standard behaviors that the various
RDBMS's offer isn't handled, but in writing portable apps, consider
whether or not using database-specific behavior is really a good idea.
 I personally don't think it is.

--Eddie

tl;dr: I'm a PDO fanboy