如何在Yii 1.1中保护原始sql CDbCriteria条件（反sql注入）？

Question

I'm dealing with an Yii 1.1 app.

Part of the search method use CDbCriteria and raw sql.

I was wondering how can I still use the raw sql code and make it more secure from sql injections?

Here is a code example:

if (!empty($this->textToSearch)) {
    $text_condition = <<<EOC
(
    topic LIKE "%{$this->textToSearch}%" OR
    main LIKE "%{$this->textToSearch}%" OR  
)
EOC;
    $criteria->addCondition($text_condition);
}

Any suggestions?

Answer 1

You should use params to pass untrusted data to query. Note that %, _ and \ chars has special meaning in SQL query, so you need to escape it too.

$criteria = new CDbCriteria();
if (!empty($this->textToSearch)) {
    $text_condition = <<<EOC
(
    topic LIKE :text_to_search OR
    main LIKE :text_to_search  
)
EOC;
    $criteria->addCondition($text_condition);
    $textToSearch = strtr($this->textToSearch, [
        '%' => '\%',
        '_' => '\_',
        '\\' => '\\\\',
    ]);
    $criteria->params[':text_to_search'] = "%{$textToSearch}%";
}