Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-15196

external db authentication compares user names case-sensitive

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 1.8.5, 2.6.4, 2.7.1
    • Fix Version/s: BACKEND
    • Component/s: Authentication
    • Labels:
    • Environment:
      Mysql 5.XX
      php 4.XX
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE

      Description

      external db authentication is not case sensitive username field. So if external database auth we have an username that appears like username "11M" but his first log was like "11m",then internal account will be "11m". and "11M" always fails.
      Moreover db auth sync users fails too because usernames dont match.

        Gliffy Diagrams

          Issue Links

            Activity

            kravitx Michael Perez created issue -
            kravitx Michael Perez made changes -
            Field Original Value New Value
            Component/s Accessibility [ 10083 ]
            Component/s Authentication [ 10067 ]
            Description Hi. Using external db authentication, when an user is logged first time his account is created but if in the database appears like username "248S" and the user logged like "248s", his username definitely will be "248s" so next time authentication with uppercase fails and script db sync users too. I suppose username shoud be case sensitive. Closed.
            Priority Major [ 3 ] Trivial [ 5 ]
            kravitx Michael Perez made changes -
            Security Minor security issue [ 10001 ]
            kravitx Michael Perez made changes -
            Issue Type Bug [ 1 ] Improvement [ 4 ]
            Component/s Authentication [ 10067 ]
            Component/s Accessibility [ 10083 ]
            kravitx Michael Perez made changes -
            Summary external db authentication authentication
            Database [MySQL]
            kravitx Michael Perez made changes -
            Summary authentication external db authentication
            Issue Type Improvement [ 4 ] Bug [ 1 ]
            Database [MySQL]
            Description Closed. Using first time external db authentication is not case sensitive username field. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            Security Possible security issue [ 10002 ]
            Priority Trivial [ 5 ] Minor [ 4 ]
            kravitx Michael Perez made changes -
            Description Using first time external db authentication is not case sensitive username field. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            Using first time external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            kravitx Michael Perez made changes -
            Description Using first time external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            Hide
            skodak Petr Skoda added a comment -

            I ma afraid only lowercase usernames are supported by moodle, the usernames in external db will have to be created in lowercase

            Show
            skodak Petr Skoda added a comment - I ma afraid only lowercase usernames are supported by moodle, the usernames in external db will have to be created in lowercase
            kravitx Michael Perez made changes -
            Description external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like "11M" but his first log was like "11m", internal account will be "11m".
            Moreover if db auth sync users runs after, fails because usernames dont match.
            external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like username "11M" but his first log was like "11m",then internal account will be "11m". and "11M" always fails.
            Moreover db auth sync users fails too because usernames dont match.
            kravitx Michael Perez made changes -
            Issue Type Bug [ 1 ] Improvement [ 4 ]
            Security Possible security issue [ 10002 ]
            kravitx Michael Perez made changes -
            Description external db authentication is not case sensitive username field on first loggin. So if external database auth we have an username that appears like username "11M" but his first log was like "11m",then internal account will be "11m". and "11M" always fails.
            Moreover db auth sync users fails too because usernames dont match.
            external db authentication is not case sensitive username field. So if external database auth we have an username that appears like username "11M" but his first log was like "11m",then internal account will be "11m". and "11M" always fails.
            Moreover db auth sync users fails too because usernames dont match.
            Hide
            kravitx Michael Perez added a comment -

            Ok Petr, thanks. It change issue to improvement.

            Show
            kravitx Michael Perez added a comment - Ok Petr, thanks. It change issue to improvement.
            Hide
            jsilve1 Jeffrey Silverman added a comment -

            Why? Why does Moodle only accept lowercase usernames? How about numbers? How about underscores or other non-alphanumeric characters?

            Thank you!

            Show
            jsilve1 Jeffrey Silverman added a comment - Why? Why does Moodle only accept lowercase usernames? How about numbers? How about underscores or other non-alphanumeric characters? Thank you!
            dougiamas Martin Dougiamas made changes -
            Workflow jira [ 26883 ] MDL Workflow [ 43499 ]
            skodak Petr Skoda made changes -
            Assignee Petr Škoda (skodak) [ skodak ] moodle.com [ moodle.com ]
            Fix Version/s DEV backlog [ 10464 ]
            dougiamas Martin Dougiamas made changes -
            Workflow MDL Workflow [ 43499 ] MDL Full Workflow [ 71893 ]
            marina Marina Glancy made changes -
            Link This issue is duplicated by MDL-46642 [ MDL-46642 ]
            marina Marina Glancy made changes -
            Affects Version/s 2.7.1 [ 13550 ]
            Affects Version/s 2.6.4 [ 13551 ]
            marina Marina Glancy made changes -
            Summary external db authentication external db authentication compares user names case-sensitive
            marina Marina Glancy made changes -
            Assignee moodle.com [ moodle.com ] Marina Glancy [ marina ]
            marina Marina Glancy made changes -
            Assignee Marina Glancy [ marina ]
            marina Marina Glancy made changes -
            Labels triaged
            marina Marina Glancy made changes -
            Fix Version/s BACKEND [ 12582 ]
            Fix Version/s DEV backlog [ 10464 ]
            Hide
            marina Marina Glancy added a comment -

            as far as I understand the problem happens on moodle side and should be addressed. I've put that on the backlog.

            In the meantime feel free to help us work on this issue. If you are able to provide a patch or links to your Git repository branch, please add a patch label so we will spot it.

            Show
            marina Marina Glancy added a comment - as far as I understand the problem happens on moodle side and should be addressed. I've put that on the backlog. In the meantime feel free to help us work on this issue. If you are able to provide a patch or links to your Git repository branch, please add a patch label so we will spot it.
            Hide
            marina Marina Glancy added a comment -

            copied from duplicate issue MDL-46642 by Mathew Gancarz:
            -------

            This may not be a real bug if case-sensitivity is on purpose but we've recently started running the /auth/db/cli/sync_users.php script.

            With it we get a whole series of
            "Error inserting user"

            I was able to track the problem down to this. Existing users in Moodle that were created when they first logged in, had entries in mdl_user as such:

            username: samplename@hotmail.com
            email: samplename@Hotmail.com

            Note the different casing of hotmail vs Hotmail in the username vs email.

            In our external db, the email and username are both: samplename@Hotmail.com

            It looks like the problem is the sync script considers the non-matching casing as a different user, then tries to create the user again and fails.

            We are on 2.5.6, but I think it also affects 2.7, judging by the code on github. We are able to work around this for now by changing the casing of the email field in our external db to be all lower case, but it would be better if it the sync_users script was not-case sensitive when the username in moodle appears to always be stored lower case and the login form is also case-insensitive.

            The piece of code that would need to be tweaked for 2.7, I think is line 376 of /auth/db/auth.php, where the array_diff does a case sensitive comparison.
            https://github.com/moodle/moodle/blob/7784c3ad18371607780f2845d44db291112b816e/auth/db/auth.php#L376
            $add_users = array_diff($userlist, $usernames);

            For 2.5 it is line 370 of the same file.
            https://github.com/moodle/moodle/blob/MOODLE_25_STABLE/auth/db/auth.php#L370

            Show
            marina Marina Glancy added a comment - copied from duplicate issue MDL-46642 by Mathew Gancarz : ------- This may not be a real bug if case-sensitivity is on purpose but we've recently started running the /auth/db/cli/sync_users.php script. With it we get a whole series of "Error inserting user" I was able to track the problem down to this. Existing users in Moodle that were created when they first logged in, had entries in mdl_user as such: username: samplename@hotmail.com email: samplename@Hotmail.com Note the different casing of hotmail vs Hotmail in the username vs email. In our external db, the email and username are both: samplename@Hotmail.com It looks like the problem is the sync script considers the non-matching casing as a different user, then tries to create the user again and fails. We are on 2.5.6, but I think it also affects 2.7, judging by the code on github. We are able to work around this for now by changing the casing of the email field in our external db to be all lower case, but it would be better if it the sync_users script was not-case sensitive when the username in moodle appears to always be stored lower case and the login form is also case-insensitive. The piece of code that would need to be tweaked for 2.7, I think is line 376 of /auth/db/auth.php, where the array_diff does a case sensitive comparison. https://github.com/moodle/moodle/blob/7784c3ad18371607780f2845d44db291112b816e/auth/db/auth.php#L376 $add_users = array_diff($userlist, $usernames); For 2.5 it is line 370 of the same file. https://github.com/moodle/moodle/blob/MOODLE_25_STABLE/auth/db/auth.php#L370
            Hide
            mgancarzdsi Mathew Gancarz added a comment - - edited

            Hello all, sorry for posting a duplicate issue, had not found this one when I did a search.

            If the case is that moodle only supports lower case user names, I think it would make sense for the auth sync script to either ignore case for comparisons, or lower case the external db usernames before performing the comparison.

            I can provide a patch, as the fix does look to be trivial but I'm not sure if the case sensitivity was a deliberate choice?

            Show
            mgancarzdsi Mathew Gancarz added a comment - - edited Hello all, sorry for posting a duplicate issue, had not found this one when I did a search. If the case is that moodle only supports lower case user names, I think it would make sense for the auth sync script to either ignore case for comparisons, or lower case the external db usernames before performing the comparison. I can provide a patch, as the fix does look to be trivial but I'm not sure if the case sensitivity was a deliberate choice?
            marina Marina Glancy made changes -
            Link This issue has a non-specific relationship to MDL-45936 [ MDL-45936 ]
            Hide
            marina Marina Glancy added a comment -

            Mathew, you have brought the attention to this issue JUST IN TIME! I can now see the increased impact of issue MDL-45936 that was recently integrated in 2.8. We need to bring more attention to it in release notes.

            Prior to MDL-45936 when creating the user using external db we inserted the record in DB directly. Now we use function user_create_user() that requires username to be lowercase and not contain spaces and comply with $CFG->extendedusernamechars policy.

            This means that in 2.8 usernames coming from external db must be in lowercase (otherwise an exception will be thrown when registering).

            Show
            marina Marina Glancy added a comment - Mathew, you have brought the attention to this issue JUST IN TIME! I can now see the increased impact of issue MDL-45936 that was recently integrated in 2.8. We need to bring more attention to it in release notes. Prior to MDL-45936 when creating the user using external db we inserted the record in DB directly. Now we use function user_create_user() that requires username to be lowercase and not contain spaces and comply with $CFG->extendedusernamechars policy. This means that in 2.8 usernames coming from external db must be in lowercase (otherwise an exception will be thrown when registering).
            Hide
            marina Marina Glancy added a comment -

            As for this particular issue, we might think about making sync_users() compare user names like everywhere else.

            Authentication of one user searches in DB: https://github.com/moodle/moodle/blob/master/auth/db/auth.php#L71 , for example, I use postgres and comparison is case-insensitive (it would be case-sensitive in some other db, like mssql). And this is also how other auth methods do.
            sync_users() compares names using PHP method, which is always case-sensitive. And this might lead to unexpected behaviour.

            Show
            marina Marina Glancy added a comment - As for this particular issue, we might think about making sync_users() compare user names like everywhere else. Authentication of one user searches in DB: https://github.com/moodle/moodle/blob/master/auth/db/auth.php#L71 , for example, I use postgres and comparison is case-insensitive (it would be case-sensitive in some other db, like mssql). And this is also how other auth methods do. sync_users() compares names using PHP method, which is always case-sensitive. And this might lead to unexpected behaviour.
            marina Marina Glancy made changes -
            Link This issue has been marked as being related by MDL-46682 [ MDL-46682 ]
            Hide
            mgancarzdsi Mathew Gancarz added a comment -

            Hi Marina, I want to confirm what you had mentioned about 2.8, it literally means we will have to reformat our external db username fields to make sure they are all recorded as lowercase in our external db?

            Show
            mgancarzdsi Mathew Gancarz added a comment - Hi Marina, I want to confirm what you had mentioned about 2.8, it literally means we will have to reformat our external db username fields to make sure they are all recorded as lowercase in our external db?
            Hide
            marina Marina Glancy added a comment - - edited

            there are two options:
            1. yes, you have to reformat your external db username fields to make sure they are all recorded as lowercase in external db
            2. before the release of 2.8 (wchih means ASAP) introduce new setting for auth_db "Automatically convert usernames to lowercase" and make sure that all usernames coming from external db are converted to lowercase before creating and/or authorising user. We might need an additional script to lowercase existing user names.

            I think it's a good time to raise a topic on forum

            Show
            marina Marina Glancy added a comment - - edited there are two options: 1. yes, you have to reformat your external db username fields to make sure they are all recorded as lowercase in external db 2. before the release of 2.8 (wchih means ASAP) introduce new setting for auth_db "Automatically convert usernames to lowercase" and make sure that all usernames coming from external db are converted to lowercase before creating and/or authorising user. We might need an additional script to lowercase existing user names. I think it's a good time to raise a topic on forum
            marina Marina Glancy made changes -
            Link This issue has been marked as being related by MDL-42993 [ MDL-42993 ]
            Hide
            mgancarzdsi Mathew Gancarz added a comment - - edited

            Hi Marina, just updating you that we were able to fix our issue by lower casing the username fields in the view that we pointed the external db authentication at. I also did have to run an sql query to force a lower case on all existing usernames in our mdl_user db.
            It was a simple
            ```
            UPDATE mdl_user SET username = LOWER( username )
            ```

            Show
            mgancarzdsi Mathew Gancarz added a comment - - edited Hi Marina, just updating you that we were able to fix our issue by lower casing the username fields in the view that we pointed the external db authentication at. I also did have to run an sql query to force a lower case on all existing usernames in our mdl_user db. It was a simple ``` UPDATE mdl_user SET username = LOWER( username ) ```
            Hide
            marina Marina Glancy added a comment -

            Thanks for update Mathew, I'm glad you resolved it for yourself and ready for 2.8 release

            I still think there is an issue here because it's not always that easy. One of Moodle HQ developers is working currently on MDL-42993, there will be hopefully a good core solution

            Show
            marina Marina Glancy added a comment - Thanks for update Mathew, I'm glad you resolved it for yourself and ready for 2.8 release I still think there is an issue here because it's not always that easy. One of Moodle HQ developers is working currently on MDL-42993 , there will be hopefully a good core solution

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: