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

Origin of SQL calls 'Mixed types of sql query parameters' error

    XMLWordPrintable

Details

    Description

      php admin/cli/cfg.php --name=debugsqltrace --set=100

      By navigating to Site administration >  Debugging > Set debugsqltrace to "Show full stack trace"  > Save changes, method fix_sql_params in lib/dml/moodle_database triggers the following error "ERROR: Mixed types of sql parameters".

      One of the preg match matches line numbers from that SQL trace when no match should be found

       

      Solution would be to only execute the preg_match on sql without sql trace and then adding trace to sql / or having two variables.

       

      ie, here is a stack strace added : 

      Error code: mixedtypesqlparam

      • line 923 of /lib/dml/moodle_database.php: dml_exception thrown
      • line 1024 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      • line 17 of /testanonclass.php: call to pgsql_native_moodle_database->get_records_sql()
      • line 22 of /testanonclass.php: call to class@anonymous
        !!! ERROR: Mixed types of sql query parameters!! !!!
        !! 
        Error code: mixedtypesqlparam !!
        !! Stack trace: * line 923 of /lib/dml/moodle_database.php: dml_exception thrown
      • line 1024 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      • line 17 of /testanonclass.php: call to pgsql_native_moodle_database->get_records_sql()
      • line 22 of /testanonclass.php: call to class@anonymous/var/www/html/testanonclass.php:14$1->log()
         !!

       
      $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches); this line is matching $1 when it should not. 

       

      Error also happens with number of parameters (invalidqueryparam), by replacing this line

       
      $DB->get_records_sql('SELECT firstname FROM {user} WHERE firstname = :firstname', ['firstname' => 'JohnDoe']);
       
      with the following
       
      $DB->get_records_sql('SELECT firstname FROM {user}');
       

      ie:

      Default exception handler: ERROR: Incorrect number of query parameters. Expected 1, got 0. Debug: 
      Error code: invalidqueryparam

      • line 953 of /lib/dml/moodle_database.php: dml_exception thrown
      • line 1024 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      • line 17 of /testanonclass.php: call to pgsql_native_moodle_database->get_records_sql()
      • line 22 of /testanonclass.php: call to class@anonymous
        !!! ERROR: Incorrect number of query parameters. Expected 1, got 0. !!!
        !! 
        Error code: invalidqueryparam !!
        !! Stack trace: * line 953 of /lib/dml/moodle_database.php: dml_exception thrown
      • line 1024 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      • line 17 of /testanonclass.php: call to pgsql_native_moodle_database->get_records_sql()
      • line 22 of /testanonclass.php: call to class@anonymous/var/www/html/testanonclass.php:14$1->log()
         !!

       

      Disabling the stack trace will not throw any error and hide the issue.

      Attachments

        Issue Links

          Activity

            People

              brendanheywood Brendan Heywood
              marcalexandreghaly Marc-Alexandre GHALY
              Peter Sistrom Peter Sistrom
              Andrew Lyons Andrew Lyons
              CiBoT CiBoT
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                14/Nov/22

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour
                  1h