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

问题描述

我正在将一些使用 ext/mysql(mysql_*() 函数)的代码转换为 PDO 和准备好的语句.以前，当我动态构建查询时，我只是通过 mysql_real_escape_string() 传递我的字符串并将它们直接放入我的查询中，但现在我发现我需要在执行查询时将值作为数组传递, 或者在执行前绑定变量.

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?

推荐答案

将您的查询从 ext/mysql 迁移到 PDO 准备好的语句需要在许多方面采用新方法.在这里，我将介绍一些经常需要执行的常见任务.这绝不是详尽无遗地匹配所有可能的情况，它只是为了演示动态生成查询时可以采用的一些技术.

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!

• 如果您没有明确禁用模拟准备，您的查询并不比使用 mysql_real_escape_string() 安全.查看此内容以获得完整说明.
• 不能在单个查询中混合命名占位符和问号占位符.在开始构建查询之前，您必须决定使用其中一个，不能中途切换.
• 预处理语句中的占位符只能用于值，不能用于对象名称.换句话说，您不能使用占位符动态指定数据库、表、列或函数名，或任何 SQL 关键字.一般来说，如果您发现需要这样做，则说明您的应用程序设计有误，需要重新检查.
• 用于指定数据库/表/列标识符的任何变量不应直接来自用户输入.换句话说，不要使用 $_POST、$_GET、$_COOKIE 或任何其他来自外部来源的数据来指定您的列名.在使用这些数据构建动态查询之前，您应该对其进行预处理.
• PDO 命名占位符在查询中指定为 :name.传入数据执行时，对应的数组键可以选择包含前导:，但这不是必需的.占位符名称应仅包含字母数字字符.
• 命名占位符不能在查询中多次使用.要多次使用相同的值，您必须使用多个不同的名称.如果您的查询包含许多重复值，请考虑改用问号占位符.
• 使用问号占位符时，传递值的顺序很重要.同样重要的是要注意占位符位置是 1 索引的，而不是 0 索引的.

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

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.

最简单的方法是使用命名占位符.

The simplest way to do this is with named placeholders.

使用 ext/mysql 可以在构造查询时对值进行转义，并将转义的值直接放入查询中.在构造 PDO 准备好的语句时，我们使用数组键来指定占位符名称，因此我们可以将数组直接传递给 PDOStatement::execute().

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().

对于这个例子，我们有一个包含三个键/值对的数组，其中键代表列名，值代表列的值.我们想要选择任何列匹配的所有行(数据具有 OR 关系).

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

<小时>

使用数组为IN () 子句构建值列表

实现此目的的最简单方法是使用问号占位符.

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.

这里我们有一个包含 5 个字符串的数组，我们希望将这些字符串与给定的列名匹配，并返回列值至少与 5 个数组值中的一个匹配的所有行.

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

这篇关于如何将动态构造的 ext/mysql 查询转换为 PDO 准备语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！