問題描述

我正在將一些使用 ext/mysql(mysql_*() 函數(shù))的代碼轉(zhuǎn)換為 PDO 和準(zhǔn)備好的語句.以前，當(dāng)我動態(tài)構(gòu)建查詢時(shí)，我只是通過 mysql_real_escape_string() 傳遞我的字符串并將它們直接放入我的查詢中，但現(xiàn)在我發(fā)現(xiàn)我需要在執(zhí)行查詢時(shí)將值作為數(shù)組傳遞, 或者在執(zhí)行前綁定變量.

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.

如何將舊代碼轉(zhuǎn)換為使用新數(shù)據(jù)庫驅(qū)動程序?

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

推薦答案

將您的查詢從 ext/mysql 遷移到 PDO 準(zhǔn)備好的語句需要在許多方面采用新方法.在這里，我將介紹一些經(jīng)常需要執(zhí)行的常見任務(wù).這絕不是詳盡無遺地匹配所有可能的情況，它只是為了演示動態(tài)生成查詢時(shí)可以采用的一些技術(shù).

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.

在我們開始之前，需要記住一些事情 - 如果有問題，請?jiān)谔釂柷皺z查此列表！

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

• 如果您沒有明確禁用模擬準(zhǔn)備，您的查詢并不比使用 mysql_real_escape_string() 安全.查看此內(nèi)容以獲得完整說明.
• 不能在單個(gè)查詢中混合命名占位符和問號占位符.在開始構(gòu)建查詢之前，您必須決定使用其中一個(gè)，不能中途切換.
• 預(yù)處理語句中的占位符只能用于值，不能用于對象名稱.換句話說，您不能使用占位符動態(tài)指定數(shù)據(jù)庫、表、列或函數(shù)名，或任何 SQL 關(guān)鍵字.一般來說，如果您發(fā)現(xiàn)需要這樣做，則說明您的應(yīng)用程序設(shè)計(jì)有誤，需要重新檢查.
• 用于指定數(shù)據(jù)庫/表/列標(biāo)識符的任何變量不應(yīng)直接來自用戶輸入.換句話說，不要使用 $_POST、$_GET、$_COOKIE 或任何其他來自外部來源的數(shù)據(jù)來指定您的列名.在使用這些數(shù)據(jù)構(gòu)建動態(tài)查詢之前，您應(yīng)該對其進(jìn)行預(yù)處理.
• PDO 命名占位符在查詢中指定為 :name.傳入數(shù)據(jù)執(zhí)行時(shí)，對應(yīng)的數(shù)組鍵可以選擇包含前導(dǎo):，但這不是必需的.占位符名稱應(yīng)僅包含字母數(shù)字字符.
• 命名占位符不能在查詢中多次使用.要多次使用相同的值，您必須使用多個(gè)不同的名稱.如果您的查詢包含許多重復(fù)值，請考慮改用問號占位符.
• 使用問號占位符時(shí)，傳遞值的順序很重要.同樣重要的是要注意占位符位置是 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.

以下所有示例代碼均假設(shè)已建立數(shù)據(jù)庫連接，并且相關(guān) PDO 實(shí)例存儲在變量 $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 可以在構(gòu)造查詢時(shí)對值進(jìn)行轉(zhuǎn)義，并將轉(zhuǎn)義的值直接放入查詢中.在構(gòu)造 PDO 準(zhǔn)備好的語句時(shí)，我們使用數(shù)組鍵來指定占位符名稱，因此我們可以將數(shù)組直接傳遞給 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().

對于這個(gè)例子，我們有一個(gè)包含三個(gè)鍵/值對的數(shù)組，其中鍵代表列名，值代表列的值.我們想要選擇任何列匹配的所有行(數(shù)據(jù)具有 OR 關(guān)系).

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

<小時(shí)>

使用數(shù)組為IN () 子句構(gòu)建值列表

實(shí)現(xiàn)此目的的最簡單方法是使用問號占位符.

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.

這里我們有一個(gè)包含 5 個(gè)字符串的數(shù)組，我們希望將這些字符串與給定的列名匹配，并返回列值至少與 5 個(gè)數(shù)組值中的一個(gè)匹配的所有行.

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

如果您已經(jīng)確定要使用帶有命名占位符的查詢，則該技術(shù)會稍微復(fù)雜一些，但并不復(fù)雜.您只需要遍歷數(shù)組即可將其轉(zhuǎn)換為關(guān)聯(lián)數(shù)組并構(gòu)造命名占位符.

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

這篇關(guān)于如何將動態(tài)構(gòu)造的 ext/mysql 查詢轉(zhuǎn)換為 PDO 準(zhǔn)備語句?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！