如何绑定原始查询参数mysql yii

Question

我使用原始文本查询生成结果。但是为了避免sql注入，它想对变量进行参数化查询，即$from &$to，在userStat()函数中传递。

puclic function userStat($from, $to){
     $sql = "select u.user_id as ID, u.email as Email
            from User u
            where u.type = 'x'
            and u.join_date BETWEEN '$from'  AND '$to'";
    $rawData = Yii::app()->db->createCommand($sql);
    return $userData  = new CSqlDataProvider($rawData, array(
                'keyField'=>'ID',
            ));
}

现在我想绑定$from和$to与文本查询($sql)。

请帮我想一下。

Answer 1

您可以先使用bindParam，然后使用queryAll()。如下所示

$sql = "select u.user_id as ID, u.email as Email
        from User u
        where u.type = 'x'
        and u.join_date BETWEEN :start  AND :end";
$rawData = Yii::app()->db->createCommand($sql);
$rawData->bindParam(":start", $from, PDO::PARAM_STR);
$rawData->bindParam(":end", $to, PDO::PARAM_STR);
$data = $rawData->queryAll();

详细信息为bindParam。我想这会解决你的问题。

Answer 2

要绑定数据，可以这样做:

pubic function userStat($from, $to){
 $sql = "select u.user_id as ID, u.email as Email
        from User u
        where u.type = 'x'
        and u.join_date BETWEEN :from  AND :to";
$rawData = Yii::app()->db->createCommand($sql)->bindValues(array(':from'=>$from, ':to'=>$to));
return $userData  = new CSqlDataProvider($rawData, array(
            'keyField'=>'ID',
        )); }