MSSQL rbac/init rule INSERT Query Generator error

Schwoebel commented 7 years ago

What steps will reproduce the problem?

I am using SQL Server 13.0.4001. I am writing in some rules and roles to be added into the database. When I try rbac/init I run into several problems. None of which I can conclude whether or not they are my databases settings problems OR a MSSQL Yii2 DB migration problem. I do know that it isn't code related because if I run the code on a MySql database it works 100%.

What is the expected result?

When I write a rule and insert it into my RbacController it looks like this: $rule = new \app\rbac\EditProfileRule; $auth->add($rule); I expect the result to be a rule inserted into the Database auth_rule table

What do you get instead?

I get this error: The SQL being executed was: INSERT INTO [auth_rule] ([name], [data], [created_at ], [updated_at]) VALUES ('O:24:"app\rbac\EditProfileRule":3:{s:4:"name";s:14:"ca nEditProfile";s:9:"createdAt";i:1488200387;s:9:"updatedAt";i:1488200387;}', CONV ERT(VARBINARY, 'O:24:"app\rbac\EditProfileRule":3:{s:4:"name";s:14:"canEditProfi le";s:9:"createdAt";i:1488200387;s:9:"updatedAt";i:1488200387;}'), 1488200387, 1 488200387)'

Additional info

It appears that your MSSQL Query generator is trying to insert the 'data' field into the 'name' field instead of using the rule name. (the bolded text above should be 'editProfileRule')

If I copy that query and put the right value in the name field the query goes through.

doing this manually mangles the converted data, result in a unserialize(): Error at offset 0 of 60 bytes error for the data field, but that is an entirely other ticket.	Q	A
Yii version	2.0.11.2
PHP version	5.5
Operating system	MS Server 2013 MSSQL Driver 11

samdark commented 7 years ago

Could you post error title as well?

samdark commented 7 years ago

Seem you're right about wrong columns order. At least in your output.

Schwoebel commented 7 years ago

This is the error output in its entirety

Exception 'yii\db\Exception' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for
SQLServer]COUNT field incorrect or syntax error The SQL being executed was: INSERT INTO [auth_rule] ([name], [data], [created_at], [updated_at]) VALUES ('O:25:"app\rbac\IsYourSchoolRule":3
{s:4:"name";s:26:"performActionsInThisSchool";s:9:"createdAt";i:1488271665;s:9:"updatedAt";i:1488271665;}', CONVERT(VARBINARY, 'O:25:"app\rbac\IsYourSchoolRule":3:
    {s:4:"name";s:26:"performActionsInThisSchool";s:9:"createdAt";i:1488271665;s:9:"updatedAt";i:148
8271665;}'), 1488271665, 1488271665)' 
in C:\inetpub\wwwroot\folder\vendor\yiisoft\yii2\db\Schema.php:636
Error Info:
Array
(
    [0] => 07002
    [1] => 0
    [2] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error
)

Schwoebel commented 7 years ago

If I run that query manually, with the correct fields in the correct places the data comes out mangled and I get a unserialize(): Error at offset 0 of 60 bytes When it tries to decode the rule data so it can look up and access the rule class file.

yii\base\ErrorHandler::handleError(8, 'unserialize(): Error at offset 0...', 'C:\inetpub\wwwroot\kungalvs\vend...', 641, ...)
3. in C:\inetpub\wwwroot\kungalvs\vendor\yiisoft\yii2\rbac\DbManager.php at line 641 – unserialize('4F3A32343A226170705C726261635C45...')
4. in C:\inetpub\wwwroot\kungalvs\vendor\yiisoft\yii2\rbac\BaseManager.php at line 218 – yii\rbac\DbManager::getRule('canEditProfile')
5. in C:\inetpub\wwwroot\kungalvs\vendor\yiisoft\yii2\rbac\DbManager.php at line 199 – yii\rbac\BaseManager::executeRule(1, yii\rbac\Permission, ['id' => 1])
6. in C:\inetpub\wwwroot\kungalvs\vendor\yiisoft\yii2\rbac\DbManager.php at line 133 – yii\rbac\DbManager::checkAccessRecursive(1, 'editOwnProfile', ['id' => 1], ['admin' => yii\rbac\Assignment])
7. in C:\inetpub\wwwroot\kungalvs\vendor\yiisoft\yii2\web\User.php at line 706 – yii\rbac\DbManager::checkAccess(1, 'editOwnProfile', ['id' => 1])
8. in C:\inetpub\wwwroot\kungalvs\themes\ius\layouts\manage.php at line 36 – yii\web\User::can('editOwnProfile', ['id' => 1])

samdark commented 7 years ago

Yeah. Looks like a bug and needs digging into it. Can do that after 2.0.12 release. Feel free to fixing it yourself and sending a pull request with tests. It could be merged into 2.0.12 then.

Schwoebel commented 7 years ago

Is there any way we could broadcast this bug to some developers who might know how to fix it? I don't have the slightest clue on even where to begin.

samdark commented 7 years ago

It's already tagged with MSSQL.

tsdogs commented 7 years ago

IMHO the problem is the use of varbinary in the data field. Changing varbinary(MAX) to nvarchar(MAX) for the data field seems to be fixing the problem.

dabkhazi commented 7 years ago

I confirm this problem. I fixed it in my project, later i will post the reason of this problem.

samdark commented 4 years ago

@darkdef helped by checking it. Seems to be working fine with current master. See also #12599

yiisoft / yii2