Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-30370 Meta: Oracle SQL issues
  3. MDL-30051

notify_login_failures in /lib/moodlelib.php crashes when executing from cron.php

    Details

    • Database:
      Oracle
    • Testing Instructions:
      Hide

      This needs testing on all DBs. Note that if you set $CFG->divertallemailsto = 'youremailaddress@foo.com'; can be used to test emails

      Note: need oracle db for testing

      1. Set notifyloginfailures to something other than nobody (in Admin > Security > Notifications)
      2. Attempt to login with incorrect password more than 10 (default) times
      3. run cron (moodle/admin/cron.php)
      4. It should run without any errors
      5. Ensure that login filaures are mailed to the user specified in notifyloginfailures
      Show
      This needs testing on all DBs. Note that if you set $CFG->divertallemailsto = 'youremailaddress@foo.com'; can be used to test emails Note: need oracle db for testing Set notifyloginfailures to something other than nobody (in Admin > Security > Notifications) Attempt to login with incorrect password more than 10 (default) times run cron (moodle/admin/cron.php) It should run without any errors Ensure that login filaures are mailed to the user specified in notifyloginfailures
    • Workaround:
      Hide

      In Oracle there are (at least) two posible fixes of this issue:

      • Change the ORDER BY, and use the column number instead of the column name:

        ...
            $sql = "SELECT l.*, u.firstname, u.lastname
                      FROM {log} l
                      JOIN {cache_flags} cf ON l.ip = cf.name
                 LEFT JOIN {user} u         ON l.userid = u.id
                     WHERE l.module = 'login' AND l.action = 'error'
                           AND l.time > ?
                           AND cf.flagtype = 'login_failure_by_ip'
                UNION ALL
                    SELECT l.*, u.firstname, u.lastname
                      FROM {log} l
                      JOIN {cache_flags} cf ON l.info = cf.name
                 LEFT JOIN {user} u         ON l.userid = u.id
                     WHERE l.module = 'login' AND l.action = 'error'
                           AND l.time > ?
                           AND cf.flagtype = 'login_failure_by_info'
                  ORDER BY 2 DESC";

      • Group the query inside a new select:

        ...
            $sql = "SELECT * FROM (SELECT l.*, u.firstname, u.lastname
                                     FROM m_log l
                                     JOIN m_cache_flags cf ON l.ip = cf.name
                                LEFT JOIN m_user u         ON l.userid = u.id
                                    WHERE l.module = 'login' AND l.action = 'error'
                                          AND l.time > ?
                                          AND cf.flagtype = 'login_failure_by_ip'
                               UNION ALL
                                   SELECT l.*, u.firstname, u.lastname
                                     FROM m_log l
                                     JOIN m_cache_flags cf ON l.info = cf.name
                                LEFT JOIN m_user u         ON l.userid = u.id
                                    WHERE l.module = 'login' AND l.action = 'error'
                                          AND l.time > ?
                                          AND cf.flagtype = 'login_failure_by_info') t
                                 ORDER BY t.time DESC";

      Show
      In Oracle there are (at least) two posible fixes of this issue: Change the ORDER BY, and use the column number instead of the column name: ... $sql = "SELECT l.*, u.firstname, u.lastname FROM {log} l JOIN {cache_flags} cf ON l.ip = cf.name LEFT JOIN {user} u ON l.userid = u.id WHERE l.module = 'login' AND l.action = 'error' AND l.time > ? AND cf.flagtype = 'login_failure_by_ip' UNION ALL SELECT l.*, u.firstname, u.lastname FROM {log} l JOIN {cache_flags} cf ON l.info = cf.name LEFT JOIN {user} u ON l.userid = u.id WHERE l.module = 'login' AND l.action = 'error' AND l.time > ? AND cf.flagtype = 'login_failure_by_info' ORDER BY 2 DESC"; Group the query inside a new select: ... $sql = "SELECT * FROM (SELECT l.*, u.firstname, u.lastname FROM m_log l JOIN m_cache_flags cf ON l.ip = cf.name LEFT JOIN m_user u ON l.userid = u.id WHERE l.module = 'login' AND l.action = 'error' AND l.time > ? AND cf.flagtype = 'login_failure_by_ip' UNION ALL SELECT l.*, u.firstname, u.lastname FROM m_log l JOIN m_cache_flags cf ON l.info = cf.name LEFT JOIN m_user u ON l.userid = u.id WHERE l.module = 'login' AND l.action = 'error' AND l.time > ? AND cf.flagtype = 'login_failure_by_info') t ORDER BY t.time DESC";
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull Master Branch:
      wip-mdl-30051

      Description

      The following sql query used in /lib/moodlelib.php is not valid (in Oracle):

      ...
          $sql = "SELECT l.*, u.firstname, u.lastname
                    FROM {log} l
                    JOIN {cache_flags} cf ON l.ip = cf.name
               LEFT JOIN {user} u         ON l.userid = u.id
                   WHERE l.module = 'login' AND l.action = 'error'
                         AND l.time > ?
                         AND cf.flagtype = 'login_failure_by_ip'
              UNION ALL
                  SELECT l.*, u.firstname, u.lastname
                    FROM {log} l
                    JOIN {cache_flags} cf ON l.info = cf.name
               LEFT JOIN {user} u         ON l.userid = u.id
                   WHERE l.module = 'login' AND l.action = 'error'
                         AND l.time > ?
                         AND cf.flagtype = 'login_failure_by_info'
                ORDER BY time DESC";

      It crashes with ORA-00904

        * line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
        * line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
        * line 1059 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
        * line 8089 of \lib\moodlelib.php: call to oci_native_moodle_database->get_recordset_sql()
        * line 279 of \lib\cronlib.php: call to notify_login_failures()
        * line 61 of \admin\cli\cron.php: call to cron_run()

      The reason of this error is that column name use is not allowed in the ORDER BY when using UNION

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    14/May/12