php 使用 WHERE 子句将数组传递给查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/907806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing an array to a query using a WHERE clause
提问by Flavius Stef
Given an array of ids $galleries = array(1,2,5)I want to have a SQL query that uses the values of the array in its WHERE clause like:
给定一个 id 数组，$galleries = array(1,2,5)我想要一个 SQL 查询，该查询在其 WHERE 子句中使用该数组的值，例如：
SELECT *
FROM galleries
WHERE id = /* values of array $galleries... eg. (1 || 2 || 5) */
How can I generate this query string to use with MySQL?
如何生成此查询字符串以与 MySQL 一起使用？
回答by Flavius Stef
BEWARE!This answer contains a severe SQL injectionvulnerability. Do NOT use the code samples as presented here, without making sure that any external input is sanitized.
谨防！这个答案包含一个严重的SQL 注入漏洞。不要使用此处提供的代码示例，而不确保任何外部输入都经过消毒。
$ids = join("','",$galleries);
$sql = "SELECT * FROM galleries WHERE id IN ('$ids')";
回答by Levi Morrison
Using PDO:[1]
使用 PDO：[1]
$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
SELECT *
FROM galleries
WHERE id IN ($in);
SQL;
$statement = $pdo->prepare($select);
$statement->execute($ids);
Using MySQLi[2]
使用 MySQLi [2]
$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
SELECT *
FROM galleries
WHERE id IN ($in);
SQL;
$statement = $mysqli->prepare($select);
$statement->bind_param(str_repeat('i', count($ids)), ...$ids);
$statement->execute();
$result = $statement->get_result();
Explanation:
解释：
Use the SQL IN()operator to check if a value exists in a given list.
使用 SQLIN()运算符检查给定列表中是否存在值。
In general it looks like this:
一般来说，它看起来像这样：
expr IN (value,...)
We can build an expression to place inside the ()from our array. Note that there must be at least one value inside the parenthesis or MySQL will return an error; this equates to making sure that our input array has at least one value. To help prevent against SQL injection attacks, first generate a ?for each input item to create a parameterized query. Here I assume that the array containing your ids is called $ids:
我们可以构建一个表达式来放置在()我们的数组中。注意括号内必须至少有一个值，否则MySQL会返回错误；这等同于确保我们的输入数组至少有一个值。为了帮助防止 SQL 注入攻击，首先?为每个输入项生成一个参数化查询。在这里，我假设包含您的 id 的数组被称为$ids：
$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
SELECT *
FROM galleries
WHERE id IN ($in);
SQL;
Given an input array of three items $selectwill look like:
给定三个项目的输入数组$select将如下所示：
SELECT *
FROM galleries
WHERE id IN (?, ?, ?)
Again note that there is a ?for each item in the input array. Then we'll use PDO or MySQLi to prepare and execute the query as noted above.
再次注意?输入数组中的每个项目都有一个。然后我们将使用 PDO 或 MySQLi 来准备和执行上面提到的查询。
Using the IN()operator with strings
将IN()运算符与字符串一起使用
It is easy to change between strings and integers because of the bound parameters. For PDO there is no change required; for MySQLi change str_repeat('i',to str_repeat('s',if you need to check strings.
由于绑定参数，很容易在字符串和整数之间进行更改。对于 PDO，无需更改；对于 MySQLi str_repeat('i',，str_repeat('s',如果您需要检查字符串，请更改为。
[1]:I've omitted some error checking for brevity. You need to check for the usual errors for each database method (or set your DB driver to throw exceptions).
[1]：为简洁起见，我省略了一些错误检查。您需要检查每个数据库方法的常见错误（或将您的数据库驱动程序设置为抛出异常）。
[2]:Requires PHP 5.6 or higher. Again I've omitted some error checking for brevity.
[2]：需要 PHP 5.6 或更高版本。为简洁起见，我再次省略了一些错误检查。
回答by user542568
ints:
整数：
$query = "SELECT * FROM `$table` WHERE `$column` IN(".implode(',',$array).")";
strings:
字符串：
$query = "SELECT * FROM `$table` WHERE `$column` IN('".implode("','",$array)."')";
回答by AvatarKava
Assuming you properly sanitize your inputs beforehand...
假设你事先对你的输入进行了适当的消毒......
$matches = implode(',', $galleries);
Then just adjust your query:
然后只需调整您的查询：
SELECT *
FROM galleries
WHERE id IN ( $matches )
Quote values appropriately depending on your dataset.
根据您的数据集适当引用值。
回答by Matthew Flaschen
Use:
用：
select id from galleries where id in (1, 2, 5);
A simple for eachloop will work.
一个简单的for each循环将起作用。
Flavius/AvatarKava's wayis better, but make sure that none of the array values contain commas.
Flavius/AvatarKava 的方法更好，但要确保所有数组值都不包含逗号。
回答by Van-Duyet Le
As Flavius Stef's answer, you can use intval()to make sure all idare int values:
作为Flavius Stef 的回答，您可以使用intval()来确保所有id都是 int 值：
$ids = join(',', array_map('intval', $galleries));
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";
回答by artoodetoo
For MySQLiwith an escape function:
对于带有转义函数的MySQLi：
$ids = array_map(function($a) use($mysqli) {
return is_string($a) ? "'".$mysqli->real_escape_string($a)."'" : $a;
}, $ids);
$ids = join(',', $ids);
$result = $mysqli->query("SELECT * FROM galleries WHERE id IN ($ids)");
For PDO with prepared statement:
对于带有准备好的语句的 PDO：
$qmarks = implode(',', array_fill(0, count($ids), '?'));
$sth = $dbh->prepare("SELECT * FROM galleries WHERE id IN ($qmarks)");
$sth->execute($ids);
回答by Izhar Aazmi
We should take care of SQL injectionvulnerabilities and an empty condition. I am going to handle both as below.
我们应该注意SQL 注入漏洞和空条件。我将按照以下方式处理两者。
For a pure numeric array, use the appropriate type conversion viz intvalor floatvalor doublevalover each element. For string types mysqli_real_escape_string()which may also be applied to numeric values if you wish. MySQL allows numbers as well as date variants as string.
对于纯数值数组，请使用适当的类型转换即intvalorfloatval或doublevalover 每个元素。对于字符串类型mysqli_real_escape_string()，如果您愿意，也可以应用于数值。MySQL 允许数字和日期变体作为 string。
To appropriately escape the values before passing to the query, create a function similar to:
要在传递给查询之前适当地转义值，请创建一个类似于以下内容的函数：
function escape($string)
{
// Assuming $db is a link identifier returned by mysqli_connect() or mysqli_init()
return mysqli_real_escape_string($db, $string);
}
Such a function would most likely be already available to you in your application, or maybe you've already created one.
这样的函数很可能已经在您的应用程序中提供给您，或者您可能已经创建了一个。
Sanitize the string array like:
清理字符串数组，如：
$values = array_map('escape', $gallaries);
A numeric array can be sanitized using intvalor floatvalor doublevalinstead as suitable:
可以根据需要使用intvalorfloatval或doubleval代替来清理数字数组：
$values = array_map('intval', $gallaries);
Then finally build the query condition
然后最后构建查询条件
$where = count($values) ? "`id` = '" . implode("' OR `id` = '", $values) . "'" : 0;
or
或者
$where = count($values) ? "`id` IN ('" . implode("', '", $values) . "')" : 0;
Since the array can also be empty sometimes, like $galleries = array();we should therefore note that IN ()does not allow for an empty list. One can also use ORinstead, but the problem remains. So the above check, count($values), is to ensure the same.
由于数组有时也可以为空，$galleries = array();因此我们应该注意IN ()不允许空列表。也可以OR改用，但问题依然存在。所以上面的检查, count($values), 是为了确保相同。
And add it to the final query:
并将其添加到最终查询中：
$query = 'SELECT * FROM `galleries` WHERE ' . $where;
TIP: If you want to show all records (no filtering) in case of an empty array instead of hiding all rows, simply replace 0with 1in the ternary's false part.
提示：如果您想在空数组的情况下显示所有记录（无过滤）而不是隐藏所有行，只需将三元假部分中的0替换为1。
回答by Mark Amery
Col. Shrapnel's SafeMySQLlibrary for PHP provides type-hinted placeholders in its parametrised queries, and includes a couple of convenient placeholders for working with arrays. The ?aplaceholder expands out an array to a comma-separated list of escaped strings*.
Col. Shrapnel 的用于 PHP的SafeMySQL库在其参数化查询中提供了类型提示占位符，并包括几个方便的占位符用于处理数组。该?a占位符膨胀出的阵列，以逗号分隔的转义字符串*的列表。
For example:
例如：
$someArray = [1, 2, 5];
$galleries = $db->getAll("SELECT * FROM galleries WHERE id IN (?a)", $someArray);
* Note that since MySQL performs automatic type coercion, it doesn't matter that SafeMySQL will convert the ids above to strings - you'll still get the correct result.
* 请注意，由于 MySQL 执行自动类型强制，因此 SafeMySQL 将上面的 id 转换为字符串并不重要 - 您仍然会得到正确的结果。
回答by Filipe
Safer.
更安全。
$galleries = array(1,2,5);
array_walk($galleries , 'intval');
$ids = implode(',', $galleries);
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";
