Bad escaping caracter for SQL server when use compare

[Shnoulle]

What steps will reproduce the problem?

Use an (MS) SQL server (tested with 15) Get a table with some value with underscore _or bracket ([ ]) Search with compare for this value using _ or [

What is the expected result?

return the values

What do you get instead?

Nothing return

Additional info

Q	A
Yii version	1.1.26
PHP version	7.4
Operating system	IIS

Yii2 use https://github.com/yiisoft/yii2/blob/3ad01aa0f04b26313c28811b9e1f052cbb19b6f7/framework/db/mssql/conditions/LikeConditionBuilder.php#L18 for escaping Yii1 use \ https://github.com/yiisoft/yii/blob/8530c30959d5f68c0d1b0d611e3ce2bf32a8371a/framework/db/schema/CDbCriteria.php#L259

See https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15#using-wildcard-characters-as-literals

Real issue : https://bugs.limesurvey.org/view.php?id=18550

[marcovtwout]

@Shnoulle CDbCriteria addSearchCondition only escapes the special characters mensioned here: https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like

A workaround is to apply your own escape function and call CDbCriteria->compare() with the last argument escape: false (https://www.yiiframework.com/doc/api/1.1/CDbCriteria#compare-detail)

This will be hard to fix on the framework level in Yii 1, since there is no MSSQL specific override available here. Even then, fixing it in a backward compatible way will be hard since people might have applied workarounds even with $escape=true. I'm afraid this will stay a known issue.

[Shnoulle]

This will be hard to fix on the framework level in Yii 1, since there is no MSSQL specific override available here. Even then, fixing it in a backward compatible way will be hard since people might have applied workarounds even with $escape=true. I'm afraid this will stay a known issue.

Yes i try to extend CDbCriteria too but must update all new CDbCriteria to new ownCDbCriteria …

I hate MS ;)