mahara-contributors team mailing list archive

[Mahara Bot <1797299@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://reviews.mahara.org/9224
Committed: https://git.mahara.org/mahara/mahara/commit/73fe99d7aaa3c528d0fbc1c7bebd5e9ed74d839d
Submitter: Robert Lyon (robertl@xxxxxxxxxxxxxxx)
Branch:    18.10_STABLE

commit 73fe99d7aaa3c528d0fbc1c7bebd5e9ed74d839d
Author: Cecilia Vela Gurovic <ceciliavg@xxxxxxxxxxxxxxx>
Date:   Fri Oct 12 08:48:42 2018 +1300

Bug 1797299: replace ILIKE in sql query for db_ilike()

And fix for a couple of errors in query

behatnotneeded

Change-Id: I1ba8dd4e517fa738161d636b05e886d4f09757e6
(cherry picked from commit 42847407ab90981baca99dbe5279a0b9f1eababf)

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1797299

Title:
  Custom landing page search broken in MySQL

Status in Mahara:
  Fix Committed

Bug description:
  The search box that allows you to choose the page to make a custom
  landing page doesn't work in MySQL. Note that it works fine in
  Postgresql.

  Steps to reproduce:
  Use MySQL as a db.
  Have at least a site admin and one other user.
  Log in as the site admin.
  Create a site page called "Custom landing page".
  Go to Configure site -> Site options -> Site settings and change the radio button for Custom landing page to yes.
  Type 'Cu' or 'Custom landing page' into the Landing page text field.

  Expected outcome:
  The page you created called "Custom landing page" appears in the box.
  You can save it.
  If you log out and log in as the admin or the other user, you will see that page.

  Actual outcome:
  Nothing appears in the search box.
  If you save the text 'Custom landing page', it is not linked to the page and nobody sees it when they log in.
  If you hit the back button in the browser after attempting to save the landing page, you get a long SQL error: 

  WAR] 4f (lib/dml.php:517) Failed to get a recordset: mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%c%' OR g.name ILIKE '%c%' OR i.name ILIKE '%c%')
              UNION
      ' at line 13] in EXECUTE("SELECT COUNT(*) FROM (SELECT v.id, v.title, v.owner, v.group, v.institution, 'view' AS urltype
              FROM "view" v
              JOIN "view_access" va ON va.view = v.id
              LEFT JOIN "group" g ON g.id = v.group
              LEFT JOIN "institution" i ON i.name = v.institution
              WHERE va.accesstype IN ('public', 'loggedin')
              AND v.type != 'profile'
              AND (
                  (v.owner IS NULL AND v.template != 2)
                    OR
                  (v.owner IN ('1'))
              )
              AND (v.title ILIKE '%c%' OR g.name ILIKE '%c%' OR i.name ILIKE '%c%')
              UNION
              SELECT ii.id, ii.title, NULL AS owner, g.id AS group, g.institution, 'forum' AS urltype
              FROM "interaction_instance" ii
              JOIN "group" g ON g.id = ii.group
              WHERE g.public = 1
              AND ii.deleted = 0
              AND (ii.title ILIKE '%c%')
              ) AS foo OFFSET 0 LIMIT 10")Command was: SELECT COUNT(*) FROM (SELECT v.id, v.title, v.owner, v.group, v.institution, 'view' AS urltype
              FROM "view" v
              JOIN "view_access" va ON va.view = v.id
              LEFT JOIN "group" g ON g.id = v.group
              LEFT JOIN "institution" i ON i.name = v.institution
              WHERE va.accesstype IN ('public', 'loggedin')
              AND v.type != 'profile'
              AND (
                  (v.owner IS NULL AND v.template != 2)
                    OR
                  (v.owner IN ('1'))
              )
              AND (v.title ILIKE ? OR g.name ILIKE ? OR i.name ILIKE ?)
              UNION
              SELECT ii.id, ii.title, NULL AS owner, g.id AS group, g.institution, 'forum' AS urltype
              FROM "interaction_instance" ii
              JOIN "group" g ON g.id = ii.group
              WHERE g.public = 1
              AND ii.deleted = 0
              AND (ii.title ILIKE ?)
              ) AS foo OFFSET ? LIMIT ? and values was (0:%c%,1:%c%,2:%c%,3:%c%,4:0,5:10)

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1797299/+subscriptions