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

Escape column name in Replace Tool

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 3.4
    • 3.2.5, 3.3.2
    • Database SQL/XMLDB
    • MOODLE_34_STABLE
    • MOODLE_32_STABLE, MOODLE_33_STABLE
    • m34_MDL-59635_Properly_Escape_Column_Names_Reserved_Words_Proof
    • Hide

      Uninstall the "broken" plug-in

      Show
      Uninstall the "broken" plug-in
    • Easy
    • 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

    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

              matteo Matteo Scaramuccia
              matteo Matteo Scaramuccia
              Damyon Wiese Damyon Wiese
              Jun Pataleta Jun Pataleta
              Ankit Agarwal Ankit Agarwal
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/Sep/17