如何将动态构造的ext/mysql查询转换为PDO准备的语句? [英] How do I convert a dynamically constructed ext/mysql query to a PDO prepared statement?

问题描述

我正在将使用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命名占位符指定为<​​c6>.将数据传递执行时，相应的数组键可以选择包含前导:，但这不是必需的.占位符名称应仅包含字母数字字符.
• 命名占位符不能在查询中多次使用.要多次使用相同的值，必须使用多个不同的名称.如果您的查询包含许多重复的值，请考虑改用问号占位符.
• 使用问号占位符时，传递值的顺序很重要.还需要注意的是，占位符位置是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 (<value list>)子句构造值列表

最简单的方法是使用问号占位符.

---

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准备的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！