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

Kill all the REGEXP SQL sentences under Oracle and MSSQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7
    • Fix Version/s: 1.7, 1.8.4, 1.9, 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      MSSQL and Oracle
    • Database:
      Microsoft SQL, Oracle
    • Affected Branches:
      MOODLE_17_STABLE
    • Fixed Branches:
      MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      Such DBs doen't support REGEXP properly (Oracle only under 10gR2 and MSSQL via external package).

      If possible, convert them to standard LIKE expressions. This will change some day. I hope.

        Gliffy Diagrams

          Activity

          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -
          • Prevented +/- operators (REGEXP) on course searches.
          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operators (REGEXP) on course searches.
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -
          • Transformed TOKEN searches into STRING searches in searchlib.
          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Transformed TOKEN searches into STRING searches in searchlib.
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -
          • Prevented +/- operators (REGEXP) on message searches
          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operators (REGEXP) on message searches
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -
          • Hidden the searchfullwords under mssql and oracle
          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Hidden the searchfullwords under mssql and oracle
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -
          • Prevented +/- operations in glossary too.

          Marking this as resolved now! B-)

          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operations in glossary too. Marking this as resolved now! B-)
          Hide
          donal@catalyst.net.nz Donal McMullan added a comment -

          Hi Eloy

          I was looking at the "get_courses_search" function, and it looks like there might be a problem with the fix for this bug.

          If an Oracle or MSSql user tries to exclude a phrase from the search with a minus-prefix like so:
          -badword

          ...Oracle and MSSQL users will find that terms that they try to Exclude are actually Included, so instead of filtering out results that contain 'badword', Moodle actively seeks them out and adds them to the result set.

          I might not be understanding this properly, so I thought I'd bat it back to you for review...

          Cheers Eloy - Donal.

          Show
          donal@catalyst.net.nz Donal McMullan added a comment - Hi Eloy I was looking at the "get_courses_search" function, and it looks like there might be a problem with the fix for this bug. If an Oracle or MSSql user tries to exclude a phrase from the search with a minus-prefix like so: -badword ...Oracle and MSSQL users will find that terms that they try to Exclude are actually Included, so instead of filtering out results that contain 'badword', Moodle actively seeks them out and adds them to the result set. I might not be understanding this properly, so I thought I'd bat it back to you for review... Cheers Eloy - Donal.
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -

          Done.

          Now under oracle and mssql the "-" performs a "NOT LIKE" statement. Not perfect, but better than previous approach. Well spotted!

          Ciao

          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Done. Now under oracle and mssql the "-" performs a "NOT LIKE" statement. Not perfect, but better than previous approach. Well spotted! Ciao

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                7/Nov/06