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

Auth DB: mapping the same field multiple times could generate inconsistencies

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.7.18, 3.2.4, 3.3.1
    • Fix Version/s: 3.2.5, 3.3.2
    • Component/s: Authentication
    • Labels:
    • Testing Instructions:
      Hide
      SETUP
      1. For this test you need to simulate an external database users sync, to do that you can simply create another users table, can be on the same database you are running your moodle.
      2. Just run the attached script on your moodle instance database, it is the SQL dump of the mdl_users, but naming it mdl_external_users instead.
      3. Add a row at least, filling username, password, firstname, lastname, email.
      SYNC TEST
      1. Log in as admin.
      2. Go to Site administration > Plugins > Authentication and enable External database.
      3. Once enabled, click Settings.
      4. Fill the form fields accordingly to your database setting and set the following fields to:
        • Table: mdl_external_users
        • Username field: username
        • Password field: password
        • Debug ADOdb: Yes
        • Set Data mapping (First name - phonetic), Data mapping (Surname - phonetic), Data mapping (Middle name), Data mapping (Alternate name) fields to firstname.
      5. Run php auth/db/cli/sync_users.php
      6. On the debug, make sure each field has a respective alias:
        {{ SELECT firstname AS F0, firstname AS F1, firstname AS F2, firstname AS F3...}
      7. Make sure you don't get notices like this one:
        Notice: Undefined offset: 2 in /home/simey/moodles/stable_master/moodle/auth/db/auth.php on line 232
      8. Look your "internal" mdl_user table.
      9. Make sure the user data got sync correctly and the fields lastnamephonetic, firstnamephonetic, middlename, alternatename are filled with the external user firstname.
      Show
      SETUP For this test you need to simulate an external database users sync, to do that you can simply create another users table, can be on the same database you are running your moodle. Just run the attached script on your moodle instance database, it is the SQL dump of the mdl_users, but naming it mdl_external_users instead. Add a row at least, filling username, password, firstname, lastname, email. SYNC TEST Log in as admin. Go to Site administration > Plugins > Authentication and enable External database. Once enabled, click Settings. Fill the form fields accordingly to your database setting and set the following fields to: Table : mdl_external_users Username field : username Password field : password Debug ADOdb : Yes Set Data mapping (First name - phonetic) , Data mapping (Surname - phonetic) , Data mapping (Middle name) , Data mapping (Alternate name) fields to firstname . Run php auth/db/cli/sync_users.php On the debug, make sure each field has a respective alias: {{ SELECT firstname AS F0, firstname AS F1, firstname AS F2, firstname AS F3...} Make sure you don't get notices like this one: Notice: Undefined offset: 2 in /home/simey/moodles/stable_master/moodle/auth/db/auth.php on line 232 Look your "internal" mdl_user table. Make sure the user data got sync correctly and the fields lastnamephonetic, firstnamephonetic, middlename, alternatename are filled with the external user firstname .
    • Affected Branches:
      MOODLE_27_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-59432-master

      Description

      How to reproduce:
      Map the same column in the external database to two different profile fields (e.g. Lastaname and Lastnamephonetic) and map some other columns before and after the two.
      After user authentication, profile data values are not as expected.

      Cause:
      The DB fields mapping generates a query such as:
      SELECT <mapped field 1>, <mapped field 2>, ... FROM...
      When a DB column is mapped twice, the query results such as:
      SELECT lastname, ..., lastname, ... FROM...
      Unfortunately ADOdb connection is set to return an associative array, thus same-name columns colaesce in only one, returning fields are not alligned as expected in the query and the behaviour is impredicible.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/Sep/17