pf » Prepared Statements in PHP and MySQL

I'm working on a web security presentation, and I was curious to know if PHP supported prepared statements. It looks like as of PHP 5 they do support it with the new mysqli object (mysqli replaces the mysql class with support for mysql 4.x features)

Here's how you do a prepared statement with php 5 and mysql (error checking is omitted):

$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare("SELECT thing FROM stuff WHERE id = ?");
$statement->bind_param("i", $id);
$statement->execute();
...

The first argument of bind_param is the type, in this case I used i for integer - you can also use s for string, d for double, and b for blob.

The variables in your query are represented with the ? question marks, just like with JDBC. This makes maintenance kind of a pain, it makes you appreciate CFML's prepared statement implementation with cfqueryparam.

You can also use PEAR:DB to run prepared statements in PHP, since it is a database abstraction layer, it is probably a good way to go.

MySQL supports prepared statements in version 4.1 and above.