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

Using the same parameter more than once in an SQL query causes an exception

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Not a bug
    • 2.0
    • 2.0
    • Database SQL/XMLDB
    • None
    • MOODLE_20_STABLE
    • MOODLE_20_STABLE

    Description

      this section of /lib/dml/moodledatabse.php (around line 658) can't account for queries that are complex and use named parameters more than once:

              if ($count > count($params)) {
                  $a = new stdClass;
                  $a->expected = $count;
                  $a->actual = count($params);
                  throw new dml_exception('invalidqueryparam', $a);
              }

      This query caused a problem:

              list($usql, $params) = $DB->get_in_or_equal($this->mainobject->students->ids->$courseid,
                                                          SQL_PARAMS_NAMED, 'param0000');
              list($usql2, $params2) = $DB->get_in_or_equal($this->mainobject->teachers,
                                                            SQL_PARAMS_NAMED, 'param900000', false);
       
              $sql = "SELECT p.id as post_id, p.userid, d.firstpost, f.course, f.type, f.id, f.name,
                             f.intro as description, c.id as cmid
                        FROM {forum} f
                  INNER JOIN {course_modules} c
                          ON f.id = c.instance
                  INNER JOIN {forum_discussions} d
                          ON d.forum = f.id
                  INNER JOIN {forum_posts} p
                          ON p.discussion = d.id
                   LEFT JOIN {forum_ratings} r
                          ON p.id = r.post
                       WHERE p.userid <> :userid
                         AND p.userid $usql
                         AND (((r.userid <> :userid) AND (r.userid $usql2))
                             OR r.userid IS NULL)
                         AND ((f.type <> 'eachuser') OR (f.type = 'eachuser' AND p.id = d.firstpost))
                         AND c.module = :moduleid
                         AND c.visible = 1
                         AND f.course = :courseid
                         AND f.assessed > 0
                    ORDER BY f.id";
              $params = array_merge($params, $params2);
              $params['userid'] = $USER->id;
              $params['moduleid'] = $this->mainobject->modulesettings['forum']->id;
              $params['courseid'] = $courseid;
       
              $unmarked = $DB->get_records_sql($sql, $params);

      Because of the reuse of :userid, the counts for expected and actual parmeters differ.

      The workaround is to use the same variable with two different names for the two different positions, but a better way would be to alter moodledatabase.php around line 619 to be:

              $count = 0;
       
              if ($named_count) {
                  $type = SQL_PARAMS_NAMED;
                  $removedduplicates = array_unique($named_matches);
                  $count = count($removedduplicates);
       
              }

      However, this still causes an error around line 685

      Attachments

        Issue Links

          Activity

            People

              stronk7 Eloy Lafuente (stronk7)
              mattgibson Matt Gibson
              Nobody Nobody
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                24/Nov/10