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

Can't have numeric userids with MSSQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      Moodle 2, IIS, MSSQL 2008, Windows Server 2003
    • Database:
      Microsoft SQL
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      We're trying to integrate Moodle into our organisations IT systems – which means using MS-SQL.
      I'm using the new SQL Server driver for PHP version 2.

      We use student id numbers as usernames. They are of the form YYnumber
      Currently, unless a student id begins with 0, there is an error as follows (developer level error reporting):

      Debug info: SQLState: 22018<br>
      Error Code: 245<br>
      Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the nvarchar value 'guest' to data type int.<br>

      SELECT * FROM bhi_user WHERE username = 9841370 AND deleted <> 1 AND mnethostid = 1
      [array (
      0 => '9841370',
      1 => '1',
      )]
      Stack trace:
      line 380 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 261 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      line 371 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      line 786 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      line 864 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      line 1218 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql()
      line 1190 of \lib\dml\moodle_database.php: call to moodle_database->get_record_sql()
      line 3812 of \lib\moodlelib.php: call to moodle_database->get_record_select()
      line 3549 of \lib\moodlelib.php: call to get_complete_user_data()
      line 122 of \login\index.php: call to authenticate_user_login()

      I've spent some time looking into this. Originally I was confused, because the parameter array holds username as a string value.

      It turns out that in lib/dlm/sqlsrv_native_moodle_database there's a call in
      do_query to emulate_bound_params (line 362)
      The function (line 695) seems to insert the parameters into the sql, using php type-testing functions to determine the syntax as follows
      ....
      if ($this->is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
      $return .= $param;
      } else if (is_float($param)) {
      $return .= $param;
      } else {
      $param = str_replace("'", "''", $param);
      $return .= "N'$param'";

      So if a parameter can be cast to a number it will be.
      There is a note above do_query

      /***

      • Bound variables are supported. Until I can get it to work, emulate the bindings
      • The challenge/problem/bug is that although they work, doing a SELECT SCOPE_IDENTITY()
      • doesn't return a value (no result set)
        */

      but I don't really know what it means

      I did try changing the driver to free_tds, but couldn't get free_tds to work. Is that the direction I should be moving?

        Attachments

          Issue Links

            Activity

              People

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

                Dates

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