如何将动态构造的 ext/mysql 查询转换为 PDO 准备语句?

• 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.

以下所有示例代码均假设已建立数据库连接，并且相关 PDO 实例存储在变量 $db 中.

// 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);

// 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);

// 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);