問題描述

我正在將一些使用 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模板網！