Moodle

Kill all the REGEXP SQL sentences under Oracle and MSSQL

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major 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.

Activity

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

Marking this as resolved now! B-)

Show
Eloy Lafuente (stronk7) added a comment -
  • Prevented +/- operations in glossary too.
Marking this as resolved now! B-)
Hide
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 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
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
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

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: