Doing user search with 'email' option on breaks search

[Robert Lyon]

The fix for https://bugs.launchpad.net/mahara/+bug/1507777

broke the user search when a site has 'Email address' ticked for searchable fields under
Administration -> Extensions -> Plugin type: artefact -> Internal -> configuration form.

The problem being that there are now 2 extra values for the prepare string that are not being added to the $values array

I'll fix up

[Mahara Bot (dev-mahara)]

Patch for "15.10_STABLE" branch: https://reviews.mahara.org/5675

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/5676

[Aaron Wells (u-aaronw)]

To replicate:

0. Have your search plugin set to "Internal" (not Elasticsearch)

1. Go to "Administration -> Extensions -> Plugin administration -> artefact:internal"

2. In the "Searchable fields" section, tick "Email address". Save.

3. Type some search text into the "Search users" box that appears at the top of every Mahara page.

Expected result: You should see search results that indicate whether or not your search string matched any users

Actual result: It doesn't work. Depending on the page you're on, you may simply see a status message that says "A nonrecoverable error occurred", or you may get a big ugly error stack.

[WAR] e6 (lib/errors.php:747) Failed to get a recordset: mysqli error: [-9999: Input Array does not match ?:
            SELECT
                COUNT(u.id)
            FROM "usr" u
                LEFT OUTER JOIN "usr_account_preference" h ON (u.id = h.usr AND h.field = 'hiderealname')
            WHERE
                u.id != 0 AND u.active = 1 AND u.deleted = 0
                AND (
                    LOWER(u.preferredname) = 'snth'
                    OR (
                        (u.preferredname IS NULL OR u.preferredname = '' OR NOT FALSE OR h.value != '1' OR h.value IS NULL)
                        AND (LOWER(u.firstname) = 'snth' OR LOWER(u.lastname) = 'snth')
                    )
                    OR (TRUE AND LOWER(u.username) = 'snth')
                    OR LOWER(u.studentid) = 'snth'
                    OR (LOWER(u.email) = 'snth' OR (SELECT email FROM "artefact_internal_profile_email" ai WHERE ai.email = 1 AND ai.owner = u.id AND ai.verified = 1) = ] in Execute(, )
Command was:
            SELECT
                COUNT(u.id)
            FROM "usr" u
                LEFT OUTER JOIN "usr_account_preference" h ON (u.id = h.usr AND h.field = 'hiderealname')
            WHERE
                u.id != 0 AND u.active = 1 AND u.deleted = 0
                AND (
                    LOWER(u.preferredname) = ?
                    OR (
                        (u.preferredname IS NULL OR u.preferredname = '' OR NOT FALSE OR h.value != '1' OR h.value IS NULL)
                        AND (LOWER(u.firstname) = ? OR LOWER(u.lastname) = ?)
                    )
                    OR (TRUE AND LOWER(u.username) = ?)
                    OR LOWER(u.studentid) = ?
                    OR (LOWER(u.email) = ? OR (SELECT email FROM "artefact_internal_profile_email" ai WHERE ai.email = ? AND ai.owner = u.id AND ai.verified = 1) = ?)
                )
                AND u.id != ? and values was (snth,snth,snth,snth,snth,snth,1)
Call stack (most recent first):

    log_message("Failed to get a recordset: mysqli error: [-9999: I...", 8, true, true) at /home/aaronw/www/mahara/htdocs/lib/errors.php:97
    log_warn("Failed to get a recordset: mysqli error: [-9999: I...") at /home/aaronw/www/mahara/htdocs/lib/errors.php:747
    SQLException->__construct("Failed to get a recordset: mysqli error: [-9999: I...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:480
    get_recordset_sql(" SELECT COUNT(u.id) ...", array(size 7)) at /home/aaronw/www/mahara/htdocs/lib/dml.php:269
    count_records_sql(" SELECT COUNT...

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5675
Committed: https://git.mahara.org/mahara/mahara/commit/1140016609e2be2df3a315578fbf2b6b4c87aa78
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE

commit 1140016609e2be2df3a315578fbf2b6b4c87aa78
Author: Robert Lyon <email address hidden>
Date: Mon Nov 9 13:01:29 2015 +1300

Bug 1514272: User search is failing when 'email address' option is on

Need to pass the correct number of prepare statement values for the
sql string.

Change-Id: Ib3071b420e739292f089cd029fb9c7219a83bb3e
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5676
Committed: https://git.mahara.org/mahara/mahara/commit/58e3fd381621119c381f10de3b43c1ed265f9582
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 58e3fd381621119c381f10de3b43c1ed265f9582
Author: Robert Lyon <email address hidden>
Date: Mon Nov 9 13:01:29 2015 +1300

Bug 1514272: User search is failing when 'email address' option is on

Need to pass the correct number of prepare statement values for the
sql string.

Change-Id: Ib3071b420e739292f089cd029fb9c7219a83bb3e
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/5693

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5693
Committed: https://git.mahara.org/mahara/mahara/commit/7d687f9ab64edad06b86a2dace7b6b5894d52ecf
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 7d687f9ab64edad06b86a2dace7b6b5894d52ecf
Author: Robert Lyon <email address hidden>
Date: Thu Nov 12 09:28:13 2015 +1300

Bug 1514272: User search is now failing when 'exact search' option is off

I thought it was only for the combination 'exact search' + 'search email'
that was broken before but it turns out it was just for 'email' option
regardless of 'exact search' or not.

behatnotneeded

Change-Id: I44a2fcd032a3a00deffd2959a01e727c3e396c8a
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "15.10_STABLE" branch: https://reviews.mahara.org/5718

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5718
Committed: https://git.mahara.org/mahara/mahara/commit/711e65cefc7aa140727137e9dfca327c19f270a9
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE

commit 711e65cefc7aa140727137e9dfca327c19f270a9
Author: Robert Lyon <email address hidden>
Date: Thu Nov 12 09:28:13 2015 +1300

Bug 1514272: User search is now failing when 'exact search' option is off

I thought it was only for the combination 'exact search' + 'search email'
that was broken before but it turns out it was just for 'email' option
regardless of 'exact search' or not.

behatnotneeded

Change-Id: I44a2fcd032a3a00deffd2959a01e727c3e396c8a
Signed-off-by: Robert Lyon <email address hidden>
(cherry picked from commit 7d687f9ab64edad06b86a2dace7b6b5894d52ecf)