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

auth_db update of external database shows no error on failure

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Waiting for peer review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.6.4, 3.7.1
    • Fix Version/s: None
    • Component/s: Authentication
    • Labels:
    • Testing Instructions:
      Hide

      Note: Test steps based on MySQL/MariaDB. Assuming external database on the same server as the Moodle web server.

      Prerequisite steps

      1. Create an external authentication database and table:
        CREATE DATABASE mdl65847;
        CREATE TABLE mdl65847.user (id MEDIUMINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255), email VARCHAR(255));
      2. Populate the user table, e.g.:
        INSERT INTO mdl65847.user (username, password, firstname, lastname, email) VALUES ('fred', 'Password.123', 'Fred', 'Flintstone', 'fred@example.com');
      3. Create a database user with SELECT-only privileges:
        CREATE USER 'mdl65847'@'localhost' IDENTIFIED BY 'yourpassword';
        GRANT SELECT ON mdl65847.user TO 'mdl65847'@'localhost';
      4. In Moodle on the settings page (Site administration → Plugins → Authentication → Manage authentication, click Settings for External database) configure the following:
        1. Host: 127.0.0.1
        2. Database: mysqli
        3. DB name: mdl65847
        4. DB user: mdl65847
        5. Password: yourpassword
        6. Table: user
        7. Username field: username
        8. Password field: password
        9. Password format: Plain text
        10. Data mapping:
          1. Data mapping (First name): firstname
          2. Update external (First name): On update
          3. Data mapping (Surname): lastname
          4. Update external (Surname): On update
          5. Data mapping (Email address): email
          6. Update external (Email address): On update.
        11. Click Save changes.
      5. Enable auth_db authentication (Site administration → Plugins → Authentication → Manage authentication, click enable icon.

      Test

      1. Purge site cache (appears to be needed to ensure language string is picked up).
      2. Log into Moodle as a user from external database.
      3. Click on the user (in the top-right depending on theme) and select Profile.
      4. Click Edit profile.
      5. Change the First name field, e.g. add a '1' to it, and click Update profile.

      Expected results

      • If Debug messages is NONE:
        Error updating external database.
      • If Debug messages is DEVELOPER: As above additionally with:
        Debug info:
        Error code: auth_dbupdateerror!
      • If Debug messages set to DEVELOPER and Debug ADOdb set to Yes: As above additionally with:
        Output buffer: … 1142: UPDATE command denied to user 'mdl65847'@'localhost' for table 'user …
      Show
      Note: Test steps based on MySQL/MariaDB. Assuming external database on the same server as the Moodle web server. Prerequisite steps Create an external authentication database and table: CREATE DATABASE mdl65847; CREATE TABLE mdl65847.user (id MEDIUMINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255), email VARCHAR(255)); Populate the user table, e.g.: INSERT INTO mdl65847.user (username, password, firstname, lastname, email) VALUES ('fred', 'Password.123', 'Fred', 'Flintstone', 'fred@example.com'); Create a database user with SELECT-only privileges: CREATE USER 'mdl65847'@'localhost' IDENTIFIED BY 'yourpassword'; GRANT SELECT ON mdl65847.user TO 'mdl65847'@'localhost'; In Moodle on the settings page ( Site administration  → Plugins  → Authentication  → Manage authentication , click Settings for External database ) configure the following: Host: 127.0.0.1 Database: mysqli DB name: mdl65847 DB user: mdl65847 Password: yourpassword Table: user Username field: username Password field: password Password format: Plain text Data mapping: Data mapping (First name): firstname Update external (First name): On update Data mapping (Surname): lastname Update external (Surname): On update Data mapping (Email address): email Update external (Email address): On update. Click Save changes . Enable auth_db authentication ( Site administration  → Plugins  → Authentication  → Manage authentication , click enable icon. Test Purge site cache (appears to be needed to ensure language string is picked up). Log into Moodle as a user from external database. Click on the user (in the top-right depending on theme) and select Profile . Click Edit profile . Change the First name field, e.g. add a '1' to it, and click Update profile . Expected results If Debug messages is NONE: Error updating external database. If Debug messages is DEVELOPER: As above additionally with: Debug info: Error code: auth_dbupdateerror! If Debug messages set to DEVELOPER and Debug ADOdb set to Yes: As above additionally with: Output buffer: … 1142: UPDATE command denied to user 'mdl65847'@'localhost' for table 'user …
    • Affected Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Pull from Repository:
    • Pull 3.6 Branch:
      MDL-65847-auth_db_error_handling-36
    • Pull 3.7 Branch:
      MDL-65847-auth_db_error_handling-37
    • Pull Master Branch:
      MDL-65847-auth_db_error_handling

      Description

      If you've configured one or more of mapped fields (under Data mapping) with "Update external: On update" but the resulting UPDATE statement fails then this failure is silent, no error is displayed.

      To reproduce this:

      1. Create a database table with columns username, password, firstname, lastname and email. Populate this with some users.
      2. Create a database user account with SELECT-only privileges on this table.
      3. Configure the external database authentication plugin (auth_db) to use the above table and user account with the following settings:
        auth_db | fielduser: username
        auth_db | fieldpass: password
        auth_db | field_map_firstname: firstname
        auth_db | field_updateremote_firstname: On update
        auth_db | field_map_lastname: lastname
        auth_db | field_updateremote_lastname: On update
        auth_db | field_map_email: email
        auth_db | field_updateremote_email: On update
      4. Enable auth_db for authentication.
      5. Log into Moodle as one of the users from the external database.
      6. As that user, edit your profile changing the firstname and click Update profile.

      Actual results: Profile appears to have been saved as there is no error message. The firstname has been updated in mdl_user but not in the external database table.

      Expected results: An error message because Moodle has been configured to update the external database and this has failed.

        Attachments

          Activity

            People

            • Assignee:
              leonstr Leon Stringer
              Reporter:
              leonstr Leon Stringer
              Participants:
              Component watchers:
              Jake Dallimore, Jun Pataleta, Ryan Wyllie
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: