Yii2: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (MSSQL)

[nohnaimer]

What steps will reproduce the problem?

After update yii2. Error after commit https://github.com/yiisoft/yii2/commit/9a915ba10c34171d1b8a51ab97fd2eda4b90ef64

What is the expected result?

Working SQL as before

What do you get instead?

Additional info

| Q | A
| ----------------- | ---
| Yii version | 2.0.12-dev
| PHP version | 7.1.2
| Operating system | macOS 10.12.3 / CentOS 7.3 x64 / Oracle Linux 7.3 x64

I use search model with this code:

public function search($params)
    {
        $query = PersonnelEmployee::find()
            ->where(['or',
                ['FireDate' => null],
                ['>', 'FireDate', $this->getFilterDate()],
            ])
            ->with([
                'company',
                'mailboxType',
                'hardware',
                'personnelPhoneNumbers',
            ]);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $this->load($params, '');

        if (!empty($this->text)) {

            $textRoll = $this->changeSearchString($this->text);

            $query
                ->joinWith(['domainUser', 'personnelPhoneNumbers'], false)
                ->andFilterWhere(['or',
                    ['like', 'FullName', $this->text],
                    ['like', 'MaidenName', $this->text],
                    ['like', 'sAMAccountName', $this->text],
                    ['like', 'mail', $this->text],
                    ['like', 'PhoneNumber', $this->text],
                    ['like', 'FullName', $textRoll],
                    ['like', 'MaidenName', $textRoll],
                    ['like', 'sAMAccountName', $textRoll],
                    ['like', 'mail', $textRoll],
                ]);

        } else {
            return $dataProvider = null;
        }

        return $dataProvider;
    }

After update yii2 and apply commit https://github.com/yiisoft/yii2/commit/9a915ba10c34171d1b8a51ab97fd2eda4b90ef64 broken search SQL.

If use commit https://github.com/yiisoft/yii2-framework/commit/d17aadf1eb1b9c436998c64046f54c8e91c8df20 everything is working.

Any help?

[samdark]

@ergeymakinen would you please check it?

[lav45]

Саш, ты имел в виду @sergeymakinen ))

[sergeymakinen]

@samdark Sure. I will take care of this. :)

[sergeymakinen]

Reproduced, I can confirm. I will look further.

[sergeymakinen]

At this time, sqlsrv seems to be buggy when using prepared statements and multiple ESCAPE. I have fixed this and other bugs in #13752 and added live tests for LIKE statements.

[nohnaimer]

Now work on sqlsrv and dblib? Or only sqlsrv?

[sergeymakinen]

I can’t test it with dblib right now but it should support both as it uses MS recommended escape sequences.

[nohnaimer]

👌Thanks! On Monday I update yii and test it on both drivers and unsubscribe!

[nohnaimer]

Monday happened on Saturday 😀 Tested dblib on macOS and sqlsrv on Linux everything works fine!
Generate SQL:
SELECT COUNT(*) FROM [personnelEmployee] LEFT JOIN [personnelDomainUsers] ON [personnelEmployee].[DomainUserID] = [personnelDomainUsers].[DomainUserID] LEFT JOIN [personnelPhoneNumbers] ON [personnelEmployee].[EmployeeID] = [personnelPhoneNumbers].[EmployeeID] WHERE (([FireDate] IS NULL) OR ([FireDate] > '2017-02-25')) AND (([FullName] LIKE '%Fcnf[%]%') OR ([MaidenName] LIKE '%Fcnf[%]%') OR ([sAMAccountName] LIKE '%Fcnf[%]%') OR ([mail] LIKE '%Fcnf[%]%') OR ([PhoneNumber] LIKE '%Fcnf[%]%') OR ([FullName] LIKE '%Аста[%]%') OR ([MaidenName] LIKE '%Аста[%]%') OR ([sAMAccountName] LIKE '%Аста[%]%') OR ([mail] LIKE '%Аста[%]%'))