NYCPHP Meetup

[Hans Zaunere]

> I'm that familiar with the concept of prepared queries, but I was
> under the impression that one of the main benefits of using them is
> that the values being bound to the statement are automatically typed
> and escaped.

Nope - one of the big advantages is that values don't have to be escaped in the first place.

> Is this a feature limited to Pear DB's prepare() method, and not
> generally applicable to other database interfaces, such as mysqli?

This is sort of a weird comparison.  PEAR::DB's prepare() does automatically escape (AFAIK) but you're not really getting any benefit.  Remember that PEAR::DB is just a wrapper around DB's APIs and as such can only emulate features that aren't available from the native DB API.

For example, when using prepare() with mysql, it still has to escape the parameters and thus take up twice as much memory.  Dan C. can say, however, if PEAR::DB will detect mysqli and use true prepared statements.

> Neither the PHP Manual nor the MySQL C API documentation mentions
> anything about escaping values that are bound to prepared statements.
> Take, for example, the following snippet:
> 
> $stmt = $mysqli->prepare( "INSERT INTO Animals VALUES (?, ?)" );
> $stmt->bind_param( 'ss', $_GET['name'], $_GET['taxonomy'] );
> 
> Is this safe as is, or should the code be converted to:

That's safe from a SQL perspective anyway.

> $name = mysqli->real_escape_string( $_GET['name'] );
> $taxonomy = mysqli->real_escape_string( $_GET['taxonomy'] );
> $stmt = $mysqli->prepare( "INSERT INTO Animals VALUES (?, ?)" );
> $stmt->bind_param( 'ss', $name, $taxonomy );
> 
> Bonus beer question -- if prepared statements don't automatically
> sanitize values being passed to the database, what is the point of
> using them?

Well there are several advantages to them.

But for one, they don't need to sanitize values to begin with.  Prepared statements are binary and thus length based.  For instance, they can say "that value starts at byte 50 and ends 5 bytes later".  The original text based protocol in MySQL used deliminators, namely the single quote.  Thus, it would consider a value to be between two single quotes, and thus any single quote (or other potentially special character) in the middle would need to be escaped.


---
Hans Zaunere
President, Founder

New York PHP
http://www.nyphp.org

AMP Technology
Supporting Apache, MySQL and PHP