Since a lot of developers of PHP/MySQL based application are not familiar with prepared statements I decided to dedicate my first post to this subject.

For those of you who are still using the good ol’ php mysql extension, please read the following. Even the php developer discourage the use of the functions.

A prepared statement is basically the definition of a database query in template form, which is send (and prepared) to the database server before execution. When executed parameters can be bound to the query to have variables in the query.

In this post i will be showing some examples of how to use the php PDO-mysql extension on how to use prepared statements

Before i jump to some code samples, i hear some of you thinking “why the f%$* would i use those”.
Well the answer to that is pretty simple; the main reason to use prepared statements is safety, because it eliminates the risk of SQL injection in your page. A second advantage is speed, overall one can state that the execution of a prepared statement is generally faster than ‘regular’ queries, since it eliminates the overhead of sending the query text to the database server, and the database server parsing the query. (this is done once when the statement is prepared, after which it will be cached on the server).

In the following example i will show the way a prepared statement can be used to execute an update statements:

First we create a new instance of the PDO object and create a new connection to the database:

$dsn = sprintf('mysql:dbname=%s;host=%s', $cfg['db']['database'], $cfg['db']['hostname']);
try {
$conn = new PDO($dsn, $cfg['db']['username'], $cfg['db']['password']);
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
throw $e;
}

first we define and prepare the query:

$uSQL= 'UPDATE `schedule` SET done = :done, response = :response, timestamp = :timestamp WHERE id = :id';
$update = $conn->prepare($usql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

Then, later in the script (wherever you want to execute the statement), you can bind the variables to the query and execute it:

$update->bindParam(':response', $response, PDO::PARAM_STR);
$update->bindParam(':id', $row->id, PDO::PARAM_INT);
$update->bindValue(':done', 1, PDO::PARAM_INT);
$update->bindValue(':timestamp', date('Y-m-d H:i:s'), PDO::PARAM_INT);
$update->execute();

Besides having, in my humble opinion, cleaner code, the $update statement can be used several times per request and only has to be prepared once.