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

Using a parameter in the $start argument in sql_substr() breaks in MSSQL

    Details

    • Testing Instructions:
      Hide
      1. Run dml unit tests on all db engines
      2. Manual test using mssql:
        1. Upload the attached test script to the webroot on a server running MSSQL.
        2. Visit the page via a web browser

      What should happen:

      You should see a list of the firstnames of all the users on the site (without the first two characters)

      What actually happens:

      ERROR: Incorrect number of query parameters. Expected 2, got 1.

      More information about this error

      Stack trace: •line 736 of \lib\dml\moodle_database.php: dml_exception thrown
      •line 706 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      •line 740 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      •line 16 of \test.php: call to mssql_native_moodle_database->get_records_sql()

      Show
      Run dml unit tests on all db engines Manual test using mssql: 1. Upload the attached test script to the webroot on a server running MSSQL. 2. Visit the page via a web browser What should happen: You should see a list of the firstnames of all the users on the site (without the first two characters) What actually happens: ERROR: Incorrect number of query parameters. Expected 2, got 1. More information about this error Stack trace: •line 736 of \lib\dml\moodle_database.php: dml_exception thrown •line 706 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->fix_sql_params() •line 740 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql() •line 16 of \test.php: call to mssql_native_moodle_database->get_records_sql()
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_27_STABLE, MOODLE_28_STABLE, MOODLE_29_STABLE
    • Fixed Branches:
      MOODLE_27_STABLE, MOODLE_28_STABLE
    • Pull Master Branch:
      MDL-35155-master

      Description

      We've run into an interesting if slightly obscure issue with the sql_substr() function on MSSQL when used in a very specific way.

      The following code will work in postgresql/mysql but fails in MSSQL:

      $length_sql = $DB->sql_length(':myparam');
      $substr_sql = $DB->sql_substr('myfield', "$length_sql + 1");
      
      $sql = "UPDATE {table} SET myfield = $substr_sql WHERE ...";
      $params = array('myparam' => $myparam);
      
      $DB->execute($sql, $params);
      

      The problem is that in the code above sql_substr() in postgres/mysql returns something like this:

      SUBSTR(myfield, LENGTH(:myparam) )
      

      whereas MSSQL returns:

      SUBSTRING(myfield, :myparam, (LEN(myfield) - :myparam + 1 ))
      

      MSSQL is repeating the $start argument so when that contains a parameter it repeats it - making the parameter array too short for the query.

      Based on the MSSQL documentation:

      If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned
      http://msdn.microsoft.com/en-us/library/ms187748.aspx

      It should be possible to change:

      return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))";
      

      to:

      return "SUBSTRING($expr, $start, LEN($expr))";
      

      in sql_substr() in mssql_native_moodle_database.php which will fix this issue while maintain the same behaviour.

        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:
                    2/Feb/15