使用WHERE子句将数组传递给查询

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 $ gallery = 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一起使用的查询字符串?

19 个解决方案

#1

BEWARE! This answer contains a severe SQL injection vulnerability. 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')";

#2

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.

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 $select will 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

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

我省略了一些简短的错误检查。您需要检查每个数据库方法的常见错误(或设置您的DB驱动程序抛出异常)。

^{[2]: Requires PHP 5.6 or higher. Again I've omitted some error checking for brevity.}

[2]:需要PHP 5.6或更高。同样，我省略了一些简短的错误检查。

#3

ints:

整数:

$query = "SELECT * FROM `$table` WHERE `$column` IN(".implode(',',$array).")";

strings:

字符串:

$query = "SELECT * FROM `$table` WHERE `$column` IN('".implode("','",$array)."')";

#4

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.

根据数据集适当地引用值。

#5

Use:

使用:

select id from galleries where id in (1, 2, 5);

A simple for each loop will work.

每个循环都有一个简单的循环。

Flavius/AvatarKava's way is better, but make sure that none of the array values contain commas.

Flavius/AvatarKava的方法更好，但是要确保数组值中没有包含逗号。

#6

As Flavius Stef's answer, you can use intval() to make sure all id are int values:

作为Flavius Stef的答案，您可以使用intval()确保所有id都是int值:

$ids = join(',', array_map('intval', $galleries));  
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";

#7

For MySQLi with 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);

#8

Safer.

更安全。

$galleries = array(1,2,5);
array_walk($galleries , 'intval');
$ids = implode(',', $galleries);
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";

#9

We can use this "WHERE id IN" clause if we filter the input array properly. Something like this:

如果我们正确地过滤输入数组，我们可以使用“WHERE id IN”子句。是这样的:

$galleries = array();

foreach ($_REQUEST['gallery_id'] as $key => $val) {
    $galleries[$key] = filter_var($val, FILTER_SANITIZE_NUMBER_INT);
}

Like the example below:

像下面的例子:

$galleryIds = implode(',', $galleries);

I.e. now you should safely use $query = "SELECT * FROM galleries WHERE id IN ({$galleryIds})";

例如，现在您应该安全地使用$query = "SELECT * FROM gallery WHERE id IN ({$galleryIds})";

#10

We should take care of SQL injection vulnerabilities and an empty condition. I am going to handle both as below.

我们应该注意SQL注入漏洞和空条件。我将在下面处理这两个。

For a pure numeric array, use the appropriate type conversion viz intval or floatval or doubleval over 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.

对于纯数值数组，在每个元素上使用适当的类型转换viz intval或floatval或doubleval。对于字符串类型mysqli_real_escape_string()，如果您愿意，也可以应用于数值。MySQL允许数字和日期变量作为字符串。

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 intval or floatval or doubleval instead as suitable:

可以使用intval或floatval或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 OR instead, but the problem remains. So the above check, count($values), is to ensure the same.

因为数组有时也是空的，比如$ gallery = array();因此，我们应该注意，在()中不允许出现空列表。人们也可以使用或替代，但问题依然存在。因此，上面的检查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 0 with 1 in the ternary's false part.

提示:如果您想在空数组中显示所有记录(没有过滤)，而不是隐藏所有的行，只需在三元的false部分用1替换0。

#11

Col. Shrapnel's SafeMySQL library for PHP provides type-hinted placeholders in its parametrised queries, and includes a couple of convenient placeholders for working with arrays. The ?a placeholder expands out an array to a comma-separated list of escaped strings*.

Shrapnel的PHP SafeMySQL库在它的参数化查询中提供了类型提示占位符，并包含了几个方便的用于处理数组的占位符。占位符将数组展开为以逗号分隔的转义字符串列表*。

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转换为字符串并不重要——您仍然会得到正确的结果。

#12

You may have table texts (T_ID (int), T_TEXT (text)) and table test (id (int), var (varchar(255)))

您可能有表文本(T_ID (int)、T_TEXT (text))和表测试(id (int)、var (varchar(255))))

In insert into test values (1, '1,2,3') ; the following will output rows from table texts where T_ID IN (1,2,3):

在插入到测试值(1，'1,2,3')中;下面将输出表文本中T_ID为(1,2,3)的行:

SELECT * FROM `texts` WHERE (SELECT FIND_IN_SET( T_ID, ( SELECT var FROM test WHERE id =1 ) ) AS tm) >0

This way you can manage a simple n2m database relation without an extra table and using only SQL without the need to use PHP or some other programming language.

这样，您就可以管理一个简单的n2m数据库关系，而不需要额外的表，并且只使用SQL，而不需要使用PHP或其他编程语言。

#13

Because the original question relates to an array of numbers and I am using an array of strings I couldn't make the given examples work.

因为最初的问题涉及一组数字，而我使用的是字符串数组，我不能让给定的例子起作用。

I found that each string needed to be encapsulated in single quotes to work with the IN() function.

我发现每个字符串都需要封装在单引号中，以便与in()函数一起工作。

Here is my solution

这是我的解决方案

foreach($status as $status_a) {
        $status_sql[] = '\''.$status_a.'\'';
    }
    $status = implode(',',$status_sql);

$sql = mysql_query("SELECT * FROM table WHERE id IN ($status)");

As you can see the first function wraps each array variable in single quotes (\') and then implodes the array.

如您所见，第一个函数将每个数组变量包装成单引号(\')，然后内爆数组。

NOTE: $status does not have single quotes in the SQL statement.

注意:$status在SQL语句中没有单引号。

There is probably a nicer way to add the quotes but this works.

可能有一种更好的方法来添加引号，但这是有效的。

#14

Besides using the IN query, you have two options to do so as in an IN query there is a risk of an SQL injection vulnerability. You can use looping to get the exact data you want or you can use the query with OR case

除了使用IN查询之外，您还有两个选择，比如在IN查询中存在SQL注入漏洞的风险。您可以使用循环来获得您想要的确切数据，也可以使用带有或大小写的查询

1. SELECT *
      FROM galleries WHERE id=1 or id=2 or id=5;


2. $ids = array(1, 2, 5);
   foreach ($ids as $id) {
      $data[] = SELECT *
                    FROM galleries WHERE id= $id;
   }

#15

More an example:

更多的一个例子:

$galleryIds = [1, '2', 'Vitruvian Man'];
$ids = array_filter($galleryIds, function($n){return (is_numeric($n));});
$ids = implode(', ', $ids);

$sql = "SELECT * FROM galleries WHERE id IN ({$ids})";
// output: 'SELECT * FROM galleries WHERE id IN (1, 2)'

$statement = $pdo->prepare($sql);
$statement->execute();

#16

Simply using IN clause will work.

简单地使用IN子句就可以了。

SELECT * FROM galleries WHERE id IN(1,2,3,4);

在id为(1,2,3,4)的画廊中选择*;

#17

Below is the method I have used, using PDO with named placeholders for other data. To overcome SQL injection I am filtering the array to accept only the values that are integers and rejecting all others.

下面是我使用的方法，使用PDO和其他数据的命名占位符。为了克服SQL注入，我过滤了数组，只接受整数的值，拒绝所有其他值。

$owner_id = 123;
$galleries = array(1,2,5,'abc');

$good_galleries = array_filter($chapter_arr, 'is_numeric');

$sql = "SELECT * FROM galleries WHERE owner=:OWNER_ID AND id IN ($good_galleries)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    "OWNER_ID" => $owner_id,
));

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

#18

Safe way without PDO:

安全的方式没有PDO:

$ids = array_filter(array_unique(array_map('intval', (array)$ids)));

if ($ids) {
    $query = 'SELECT * FROM `galleries` WHERE `id` IN ('.implode(',', $ids).');';
}

• (array)$ids Cast $ids variable to array
• (数组)$id将$id变量转换为数组。
• array_map Transform all array values into integers
• array_map将所有数组值转换为整数。
• array_unique Remove repeated values
• array_unique去除重复值
• array_filter Remove zero values
• array_filter删除零值
• implode Join all values to IN selection
• 内爆将所有值加入到选择中

#19

Basic methods to prevent SQL injection are:

防止SQL注入的基本方法有:

• Use prepared statements and parameterized queries
• 使用准备好的语句和参数化查询
• Escaping the special characters in your unsafe variable
• 转义不安全变量中的特殊字符

Using prepared statements and parameterized queries query is considered the better practice, but if you choose the escaping characters method then you can try my example below.

使用准备语句和参数化查询被认为是更好的实践，但是如果您选择转义字符方法，那么您可以尝试下面的示例。

You can generate the queries by using array_map to add a single quote to each of elements in the $galleries:

您可以通过使用array_map向$ gallery中的每个元素添加单引号来生成查询:

$galleries = array(1,2,5);

$galleries_str = implode(', ',
                     array_map(function(&$item){
                                   return "'" .mysql_real_escape_string($item) . "'";
                               }, $galleries));

$sql = "SELECT * FROM gallery WHERE id IN (" . $galleries_str . ");";

The generated $sql var will be:

生成的$sql var将是:

SELECT * FROM gallery WHERE id IN ('1', '2', '5');

Note: mysql_real_escape_string, as described in its documentation here, was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

注意:mysql_real_escape_string(如本文文档中所述)在PHP 5.5.0中被弃用，在PHP 7.0.0中被删除。相反，应该使用sqmyli或PDO_MySQL扩展。更多信息请参见MySQL:选择API指南和相关FAQ。替代这一职能的办法包括:

• mysqli_real_escape_string()

  mysqli_real_escape_string()

• PDO::quote()

  PDO:报价()