How do I convert a dynamically constructed ext/mysql query to a PDO prepared statement?

Question

I am converting some of my code that used ext/mysql (mysql_*() functions) to PDO and prepared statements. Previously when I was dynamically constructing queries I simply passed my strings through mysql_real_escape_string() and dropped them straight into my query, but now I find I need to pass the values in as an array when I execute the query, or bind the variables before execution.

How can I convert my old code to use the new database driver?

Answer 1

Migrating your queries from ext/mysql to PDO prepared statements requires a new approach to a number of aspects. Here I will cover a couple of common tasks that regularly need to be performed. This by no means an exhaustive to match every possible situation, it is merely intended to demonstrate some of the techniques that can be employed when dynamically generating queries.

Before we begin, a few things to remember - if something is not work right, check this list before asking questions!

• If you do not explicitly disable emulated prepares, your queries are no safer than using mysql_real_escape_string(). See this for a full explanation.
• It is not possible to mix named placeholders and question-mark placeholders in a single query. Before you begin to construct your query you must decide to use one of the other, you can't switch half way through.
• Placeholders in prepared statements can only be used for values, they cannot be used for object names. In other words, you cannot dynamically specify database, table, column or function names, or any SQL keyword, using a placeholder. In general if you find you need to do this, the design of your application is wrong and you need to re-examine it.
• Any variables used to specify database/table/column identifiers should not come directly from user input. In other words, don't use $_POST, $_GET, $_COOKIE or any other data that comes from an external source to specify your column names. You should pre-process this data before using it to construct a dynamic query.
• PDO named placeholders are specified in the query as :name. When passing the data in for execution, the corresponding array keys can optionally include the leading :, but it is not required. A placeholder name should contain only alpha-numeric characters.
• Named placeholders cannot be used more than once in a query. To use the same value more than once, you must use multiple distinct names. Consider using question mark placeholders instead if you have a query with many repeated values.
• When using question mark placeholders, the order of the values passed is important. It is also important to note that the placeholder positions are 1-indexed, not 0-indexed.

All the example code below assumes that a database connection has been established, and that the relevant PDO instance is stored in the variable $db.

---

Using an associative array as a column/value list

The simplest way to do this is with named placeholders.

With ext/mysql one would escape the values as the query was constructed and place the escaped values directly into the query. When constructing a PDO prepared statement, we use the array keys to specify placeholder names instead, so we can pass the array directly to PDOStatement::execute().

For this example we have an array of three key/value pairs, where the key represents a column name and the value represents the value of the column. We want to select all rows where any of the columns match (the data has an OR relationship).

// The array you want to use for your field list
$data = array (
  'field1' => 'value1',
  'field2' => 'value2',
  'field3' => 'value3'
);

// A temporary array to hold the fields in an intermediate state
$whereClause = array();

// Iterate over the data and convert to individual clause elements
foreach ($data as $key => $value) {
    $whereClause[] = "`$key` = :$key";
}

// Construct the query
$query = '
  SELECT *
  FROM `table_name`
  WHERE '.implode(' OR ', $whereClause).'
';

// Prepare the query
$stmt = $db->prepare($query);

// Execute the query
$stmt->execute($data);

---

Using an array to construct a value list for an IN (<value list>) clause

The simplest way to achieve this is using question mark placeholders.

Here we have an array of 5 strings that we want to match a given column name against, and return all rows where the column value matches at least one of the 5 array values.

// The array of values
$data = array (
  'value1',
  'value2',
  'value3',
  'value4',
  'value5'
);

// Construct an array of question marks of equal length to the value array
$placeHolders = array_fill(0, count($data), '?');

// Normalise the array so it is 1-indexed
array_unshift($data, '');
unset($data[0]);

// Construct the query
$query = '
  SELECT *
  FROM `table_name`
  WHERE `field` IN ('.implode(', ', $placeHolders).')
';

// Prepare the query
$stmt = $db->prepare($query);

// Execute the query
$stmt->execute($data);

If you have already determined that you want to use a query with named placeholders, the technique is a little more complex, but not much. You simply need to loop over the array to convert it to an associative array and construct the named placeholders.

// The array of values
$data = array (
  'value1',
  'value2',
  'value3',
  'value4',
  'value5'
);

// Temporary arrays to hold the data
$placeHolders = $valueList = array();

// Loop the array and construct the named format
for ($i = 0, $count = count($data); $i < $count; $i++) {
  $placeHolders[] = ":list$i";
  $valueList["list$i"] = $data[$i];
}

// Construct the query
$query = '
  SELECT *
  FROM `table_name`
  WHERE `field` IN ('.implode(', ', $placeHolders).')
';

// Prepare the query
$stmt = $db->prepare($query);

// Execute the query
$stmt->execute($valueList);