格式化SQL“IN”的PHP数组条款 [英] Formatting a PHP array for an SQL "IN" clause

问题描述

我正在尝试查询数据库中的product_id包含在产品ID数组中的记录。

该数组是多个选择输入（< select> ），如下所示：

  $ clients = 
 Array（
 [0] => 80000016-1302638679 
 [1] => 8000003B-1329924004 
）
  

我想将该数组传递给SQL语句的IN子句，例如：

  $ sql =SELECT * FROM sales WHERE product_id IN（。$ clients。）; 
  

...但这不起作用（错误： Message：Array到字符串转换）。

几个帖子建议使用此函数以适合SQL的方式格式化数组：

  function format_array（$ array）{
 return implode（'，'，$ array）; 
} 
} 
  

例如...

  $ sql =SELECT * FROM sales WHERE product_id IN（.format_array（$ clients）。 
  

导致此查询：


SELECT * FROM sales WHERE product_id IN（80000016-1302638679，8000003B-132992400）


...和此错误：

在'where子句'中未知列'8000003B'

我做错了什么？任何帮助，非常感谢！如果需要，我可以澄清这个问题：）有更好的方法

解决方案 b

你在评论中提到你正在使用CodeIgniter。除非你使一些非常复杂的东西，没有什么实际的原因，你应该建立自己的自制的查询，当你有

如果这不起作用， =nofollow> where_in ，则有良好的 转义 。

---

好的，你大多数人都说你需要报价并给你这个：

  function createInClause（$ arr）
 {
 return'\' '。 implode（'\'，\''，$ arr）。 '\''; 
} 
  

但这真的是不够的，如果你有可能输入可疑例如 '）; DROP表学生; - 。为了防止这种情况，您需要确保检查 SQL注入：

 函数createInClause（$ arr）
 {
 $ tmp = array（）; 
 foreach（$ arr as $ item）
 {
 //这一行确保你没有风险的sql注入攻击
 // $ connection是你当前的连接
 $ tmp [] = mysqli_escape_string（$ connection，$ item）; 
} 
返回'\''。 implode（'\'，\''，$ tmp）。 '\''; 
} 
  

I'm attempting to query a database for records where the "product_id" is included in an array of products IDs.

The array is the post result of a multiple select input (<select>) and looks like:

$clients = 
  Array ( 
    [0] => 80000016-1302638679
    [1] => 8000003B-1329924004
  )

I would like to pass that array to the "IN" clause of an SQL statement such as:

$sql = "SELECT * FROM sales WHERE product_id IN (".$clients.")";

...but this doesn't work (Error: Message: Array to string conversion).

Several posts suggest using this function to format the array in a way suitable for SQL:

function format_array($array){
    return implode(', ', $array);
  }
}

Such as ...

$sql = "SELECT * FROM sales WHERE product_id IN (".format_array($clients).")";

That results in this query:

SELECT * FROM sales WHERE product_id IN (80000016-1302638679, 8000003B-132992400)

...and this error:

Unknown column '8000003B' in 'where clause'

What am I doing wrong? Any help is greatly appreciated! I can clarify the question if needed :)

解决方案

There is a better way

You mention in the comments that you are using CodeIgniter. Unless you are making something extraordinarily complicated, there is no practical reason you should be building your own home-baked queries when you have where_in built in.

And if that doesn't work, then there is good ol' fashioned escape.

---

OK, so, you have most people saying that you need to quote the items and are giving you this:

function createInClause($arr)
{
    return '\'' . implode( '\', \'', $arr ) . '\'';
}

but that really isn't sufficient if you have the possibility for questionable input (such as '); DROP TABLE STUDENTS; --. To protect against that, you need to make sure you check for SQL injection:

function createInClause($arr)
{
    $tmp = array();
    foreach($arr as $item)
    {
        // this line makes sure you don't risk a sql injection attack
        // $connection is your current connection
        $tmp[] = mysqli_escape_string($connection, $item);
    }
    return '\'' . implode( '\', \'', $tmp ) . '\'';
}

这篇关于格式化SQL“IN”的PHP数组条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！