MySQL Bugs: #73411: Multiple issues caused by trailing and leading white space character in params

Bug #73411	Multiple issues caused by trailing and leading white space character in params
Submitted:	28 Jul 2014 12:17	Modified:	7 Nov 2014 1:44
Reporter:	Velja Radenkovic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.8.3.0	OS:	Windows (7)
Assigned to:	Francisco Alberto Tirado Zavala	CPU Architecture:	Any
Tags:	.net Connector, blank space

Description:
I experienced this while ago in a project, just checked last version and it is still there. In few words: mysql membership provider doesn't handle white space character in leading and trailing positions correctly. RolesProvider becomes completely unusable with user registered with username ending with white space.

CreateUser function doesn't trim white spaces from username and other parameters. It stores the leading and trailing whitespace in database but it returns instance of MembershipUser having these white spaces trimmed. This is most important part because because it breaks much of Membership and Roles functionality.

How to repeat:
Test project that should help is on below location.

https://dl.dropboxusercontent.com/u/81180836/MySqlMemHandlingOfBlankspaces.zip

public void TestSimplified() {
            CleanupDB();
            MySQLMembershipProvider provider = System.Web.Security.Membership.Provider as MySQLMembershipProvider;
            MembershipCreateStatus status;

            //Creating user:
            // Problem 1: Username is not trimmed it is stored to database with trailing space 
            // Problem 2: Returned muser1 instance has trimmed trailing blankspace which makes weird since it is not matching database.
            MembershipUser muser1 = provider.CreateUser("with trailing space ", "dummypassword1!", "w@w1.w", null, null, true, null, out status);

            //Lets just get this user from db back using trimed username. 
            //User which acctualy doesnt exist returned.
            MembershipUser muser2 = provider.GetUser("with trailing space", false);

            Roles.CreateRole("SomeRole");

            //Check is user in role returns false
            bool isInRole = Roles.IsUserInRole(muser2.UserName, "SomeRole");

            // You add user to role SomeRole it doesnt complain
            Roles.AddUserToRole(muser2.UserName, "SomeRole");

            //Check is user in role returns false
            isInRole = Roles.IsUserInRole(muser2.UserName, "SomeRole");

            /** Crash **/
            // So if he isnt in role lets add it second time. PUF FK violation!
            Roles.AddUserToRole(muser2.UserName, "SomeRole");
        }

Suggested fix:
This can give you a clue. It says  "Leading and trailing spaces are trimmed from all parameter values.".

http://msdn.microsoft.com/en-us/library/d8t4h2es(v=vs.110).aspx

Hello Velja.

Sorry for the delay in this comment.
Just to let you know that this bug is fixed and the fix will be released soon.

Thanks for your time.

Fixed as of the upcoming Connector/Net 6.9.5 releases, and here's the changelog entry:

The "CreateUser" function did not trim whitespace before storing values
into the database, while "MembershipUser" did return trimmed values. The
trimming is now performed before the data is stored.

Thank you for the bug report.

Posted by developer:
 
Fixed as of Connector/Net 6.7.7 / 6.8.5 / 6.9.5 -- Thank you for the bug report.