在Yii中使用没有activeRecord的mysql IN子句时如何防止mysql注入? [英] How to prevent mysql injection when using mysql IN clause without activeRecord in Yii?

问题描述

我有一个从客户端获取的 ID 数组.我想在带有 IN 子句的 sql 查询中使用这些 ID.但是这个查询在一个没有模型的表上进行.所以不可能有活动记录(标准)查询.

I have an array with ids that I get from client. And I want use those ids in my sql query with IN clause. But this query goes on a table that has no model. So there is no active record (criteria) query possible.

** 表 userTasks **

** Table userTasks **

--------------------
| idUser | idTasks |
---------+----------
|    1   |    1    |
---------+----------
|    1   |    2    |
---------+----------
|    1   |    3    |
---------+----------

第一种方法不起作用，因为参数始终被视为字符串.所以 :tasks 是一个字符串 '1,2,3' 而不是逗号分隔的 id 列表:

First approach does not work because params are always considered as strings. So :tasks is a string '1,2,3' instead of a comma separated list of ids:

$sql = 'SELECT COUNT(*) AS matches 
        FROM userTasks 
        WHERE idUser = :idUser 
          AND idTask IN (:tasks)';
$result = Yii::app()->db->createCommand($sql)
  ->queryRow(true,[
    ':idUser' => $idUser,
    ':tasks' => implode(',', $tasks)]); //$tasks is a simple array of ids [1,2,3]

所以我的解决方法:

foreach($tasks as $task) //$tasks is a simple array of ids [1,2,3]
{
  $inTasks[] = (int) $task;
}

$sql = 'SELECT COUNT(*) AS matches 
        FROM userTasks 
        WHERE idUser = :idUser 
        AND idTask IN (' . implode(',', $inTasks . ')';
$result = Yii::app()->db->createCommand($sql)
  ->queryRow(true,[':idUser' => $idUser]);

推荐答案

在我的项目中遇到过几次这个问题，我想出了以下使用 CDbCriteria 的 Yii 解决方案，这有点棘手，但给出了参数计数匹配的安全性.

Having come across this problem a few times in my projects I have come-up with the following Yii work-around using CDbCriteria which is a little hacky, but gives the security of param count matching.

在这种情况下，我也会使用 queryScalar() 直接获取结果.

I would also use queryScalar() in this instance to get the result directly.

当应用于您的示例时，我的代码将是:

When applied to your example my code would be:

$idUser = 1;
$tasks = array(1,2,3);
$criteria = new CDbCriteria();
$criteria->addInCondition('idTask',$tasks);

$sql = '
  SELECT COUNT(*) matches
    FROM userTasks
   WHERE idUser = :idUser
     AND '.$criteria->condition;

$command = Yii::app()->db->createCommand($sql);
$command->bindValue('idUser',$idUser);
$command->bindValues($criteria->params);
$result = $command->queryScalar();

这篇关于在Yii中使用没有activeRecord的mysql IN子句时如何防止mysql注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！