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

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Not a bug
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      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

              Assignee:
              stronk7 Eloy Lafuente (stronk7)
              Reporter:
              mattgibson Matt Gibson
              Tester:
              Nobody
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                24/Nov/10