yii2 quote escape method

Question

I have a blog where I can add posts, so let's say I created a tag called asd'qwe

then I create a post, add a tag via search, enter asd', and when it comes to the quote ' I get an error in the network

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''asd'', name)' at line 1 The SQL being executed was: SELECT id, name FROM blog_tag WHERE name LIKE '%asd\'%' ORDER BY LOCATE('asd'', name)

it's all because of this quote here, here is my function for adding a tag to a post

public function actionGetTags($query = '')
    {
        Yii::$app->response->format = Response::FORMAT_JSON;

        if (!is_string($query) || !$query) {
            return '';
        }

        $tags = BlogTag::find()
            ->select(['id', 'name'])
            ->where(['like', 'name', $query])
            ->orderBy(["LOCATE('$query', `name`)" => SORT_ASC])
            ->asArray()->all();

        return $tags;
    }

I heard in yii2 there are escaping methods to fix a similar error, but I have not yet figured out where and how to use it

I will be grateful for any help or hint.

Answer 1

If you call orderBy like that, the value of $query is inserted into the string before it's passed into orderBy function. The orderBy function treats the array keys as column names and it won't escape them.

That's quite a problem because beside the error you are experiencing it can be abused for SQL Injection attacks.

If you want to use more complex expressions in your order by clause of SQL query you should use yii\db\Expression. That will allow you to pass $query as bind param instead of including it directly.

In your case the orderBy() can look like this:

->orderBy(new \yii\db\Expression(
    'LOCATE(:query, `name`) ASC',
    [':query' => $query]
))

You don't have to worry about the where() call. For simple conditions like ['like', 'name', $query] the query builder will use the bind params automatically.