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

Escape column name in Replace Tool

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.4
    • Fix Version/s: 3.2.5, 3.3.2
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Testing Instructions:
      Hide

      (difficulty: medium, requires administrator access to the database)

      Manual testing
      1. Add a new column field (XMLDB_TYPE_CHAR or XMLDB_TYPE_TEXT) using a reserved word crossing the DBs like where in a core table e.g. In MySQL, by altering the scorm table w/ a new XMLDB_TYPE_CHAR field:

        ALTER TABLE mdl_scorm ADD `where` VARCHAR(20) COLLATE utf8mb4_bin DEFAULT 'localhost';
        

      2. Try to use the Replace Tool e.g.

        php admin/tool/replace/cli/replace.php --search=//oldsitehost --replace=//newsitehost
        

        • Confirm that the replace tool will completes successfully and that you don't encounter any errors.
      PHPUnit
      1. Run the following unit tests in various DBs and confirm that the tests pass:
        • vendor/bin/phpunit core_ddl_testcase lib/ddl/tests/ddl_test.php
        • vendor/bin/phpunit core_dml_testcase lib/dml/tests/dml_test.php
      Show
      (difficulty: medium, requires administrator access to the database) Manual testing Add a new column field ( XMLDB_TYPE_CHAR or XMLDB_TYPE_TEXT ) using a reserved word crossing the DBs like where in a core table e.g. In MySQL, by altering the scorm table w/ a new XMLDB_TYPE_CHAR field: ALTER TABLE mdl_scorm ADD `where` VARCHAR(20) COLLATE utf8mb4_bin DEFAULT 'localhost'; Try to use the Replace Tool e.g. php admin/tool/replace/cli/replace.php --search=//oldsitehost --replace=//newsitehost Confirm that the replace tool will completes successfully and that you don't encounter any errors. PHPUnit Run the following unit tests in various DBs and confirm that the tests pass: vendor/bin/phpunit core_ddl_testcase lib/ddl/tests/ddl_test.php vendor/bin/phpunit core_dml_testcase lib/dml/tests/dml_test.php
    • Workaround:
      Hide

      Uninstall the "broken" plug-in

      Show
      Uninstall the "broken" plug-in
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_34_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      m34_MDL-59635_Properly_Escape_Column_Names_Reserved_Words_Proof

      Description

      As described in https://moodle.org/mod/forum/discuss.php?d=354485 sometimes plug-in developers doesn't properly care about reserved words when they design their data models: it could happen that their choices conflict for one or more DB Servers among the ones supported by Moodle.

      Unfortunately this local - i.e. the plug-in can't run when Moodle uses a specific DB server - issue can reflect in those Moodle tools that play with the database tables like the Replace Tool.

      To replicate:
      1. Add a new column field (XMLDB_TYPE_CHAR or XMLDB_TYPE_TEXT) using a reserved word crossing the DBs like where in a core table e.g. In MySQL, by altering the scorm table w/ a new XMLDB_TYPE_CHAR field:

        ALTER TABLE mdl_scorm ADD `where` VARCHAR(20) COLLATE utf8mb4_bin DEFAULT 'localhost';
        

      2. Try to use the Replace Tool e.g.

        php admin/tool/replace/cli/replace.php --search=//oldsitehost --replace=//newsitehost
        

      3. Expected:
        • The replace tool will complete successfully.
      4. Actual:
        • An error is encountered.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  11/Sep/17