Moodle
  1. Moodle
  2. MDL-15196

external db authentication compares user names case-sensitive

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor 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

            Hide
            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
            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
            Hide
            Michael Perez added a comment -

            Ok Petr, thanks. It change issue to improvement.

            Show
            Michael Perez added a comment - Ok Petr, thanks. It change issue to improvement.
            Hide
            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
            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!
            Hide
            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 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 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 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
            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
            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?
            Hide
            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 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 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 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.
            Hide
            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
            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 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 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
            Hide
            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
            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 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 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: