Yii2: accent insensitive filter

Question

I want to search words using accent insensitive. For case insensitive I use ilike:

$query->andFilterWhere(['ilike', 'name', $this->name]);

But for accent insensitive I don't know a Yii2 solution (else I can use this PHP solution).

In the next example I search the word "camara" but it doesn't find the word "cámara" (means camera in Spanish):

umm.. well change the collation on runtime in your sql query, see this answer https://stackoverflow.com/questions/8647080/accent-insensitive-search-query-in-mysql , you just need to tell MySQL to use a different collation. — Muhammad Omer Aslam, Dec 12 '18 at 14:58
Thanks @MuhammadOmerAslam, but I prefer not to touch the PostgreSQL database. — Roby Sottini, Dec 12 '18 at 15:03

score 0 · Accepted Answer · answered Dec 12 '18 at 17:58

0

I resolved it using the lower_unaccent PostgreSQL function and the ILIKE PostgreSQL operator:

$query->andWhere(new Expression(
    'lower_unaccent(name) ILIKE \'%\' || lower_unaccent(\'' . $this->name . '\') || \'%\''
));

answered Dec 12 '18 at 17:58

Roby Sottini

2,117
6
48
88

1

It looks like you have SQLi vulnerability in your query. You should use prepared statement and params instead of concatenation at PHP level. – rob006 Dec 12 '18 at 18:29

score 0 · Answer 2 · answered Oct 20 '20 at 19:26

took me a while to make this work with @rob006 comment about sql injection:

private static function ExpressionLikeAccentInsensitive($col, $field){
        $bind = md5($col);
        return [
            'ilike',
            new Expression("lower(unaccent({$col}))"),
            new Expression("'%' || lower(unaccent(:q{$bind})) || '%'", [
                ":q{$bind}"=>$field
            ]),
        ];
    }

then you call it like this (example using Postgres ILKE):

$query->andFilterWhere(self::ExpressionLikeAccentInsensitive('"DB_COLUMN"', $this->DB_COLUMN));

Yii2: accent insensitive filter

2 Answers2