How to generate ActiveQuery with client defined query string parameters safely in Yii2?

Question

Is there a safe way to generate conditional clause for Yii2 ORM with query string parameters? For example, we require a list of some food products, filtering by their properties:

GET /food/?weight[>]=1000&calories=[<]=200

And there is a plenty of different properties of the products: weight, calories, quantity, price.

I expect that it's possible to write something like (simplified code):

 $query = new \yii\db\Query();
 foreach ($_GET as $parameter => $condition){
    foreach ($condition as $operator => $value){
        $query->where(new SimpleCondition($parameter, $operator, $value));
    }
 }

But I doubt this approach is safe.

So, there are three questions:

How is it possible to define the properties from url safely? Can we sanitize the query string parameter names (not values) before using in ActiveQuery::where clause?
What's the way to properly define operators like IN, AND, OR, >, <, >=, <=, etc.?
Is there any native Yii2 component for filtering or should I use a third-party module?

This is definitely not safe - this code is vulnerable to SQL Injection. — rob006, Oct 08 '18 at 18:53

score 1 · Accepted Answer · answered Oct 08 '18 at 18:28

Finally, I found the solution. It appears Yii2 provides such functionality with DataFilter class. The official documentation of the class and the guide to use it

According to the documentation

Define a model for validation.

class SearchModel extends \yii\base\Model
{
    public $id;
    public $name;

    public function rules()
    {
        return [
            [['id', 'name'], 'trim'],
            ['id', 'integer'],
            ['name', 'string'],
        ];
    }
}

Create the filter:

$filter = new DataFilter(['searchModel' => $searchModel]);

Populate the filter with data, validate

if ($filter->load(\Yii::$app->request->get())) { 
   $filterCondition = $filter->build();
   if ($filterCondition === false) { // if error occure
       // the errors are stored in the filter instance
       return $filter;
   }
}

Use the built condition in a Query filter
```
$query->andWhere($filterCondition);
```

How to generate ActiveQuery with client defined query string parameters safely in Yii2?

1 Answers1