SQL syntax error causing search failure

[Aaron Wells]

Opening a new bug for this issue, because (given how much time has passed) it's probably unrelated to the original problem on bug 547776: https://bugs.launchpad.net/mahara/+bug/547776/comments/4

Hi Penny - this seems to be happening in the latest version also. It is specifically with the Portfolio Search.

When you enable this and try a search in the block it returns the below:

'A nonrecoverable error occurred. This probably means you have encountered a bug in the system'

I checked server logs and found this too:

(lib/dml.php:484) Failed to get a recordset: mysql 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 '), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) OR, referer: http://web-address.co.uk/selfsearch.php
[WAR] 63 (lib/dml.php:484) (LOWER(at.tag) = 'test'), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) ) LIMI' at line 5] in EXECUTE(", referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) SELECT, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) a.id, a.artefacttype, a.title, a.description, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) FROM, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) "artefact" a, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) LEFT JOIN "artefact_tag" at ON (at.artefact = a.id), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) WHERE, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) a.owner = '10', referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) AND (, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) (), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) OR, referer: http://web-address.co.uk/selfsearch.php
 63 (lib/dml.php:484) (LOWER(at.tag) = 'test'), referer: http://web-address.co.uk/selfsearch.php
 63 (lib/dml.php:484) ) LIMIT 10"), referer: http://web-address.co.uk/selfsearch.php
 63 (lib/dml.php:484) Command was: , referer: http://web-address.co.uk/selfsearch.php
 63 (lib/dml.php:484) SELECT, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) a.id, a.artefacttype, a.title, a.description, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) FROM, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) LEFT JOIN "artefact_tag" at ON (at.artefact = a.id), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) WHERE, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) a.owner = ?, referer: http://web-address.co.uk/selfsearch.php
[] [WAR] 63 (lib/dml.php:484) AND (, referer: http://web-address.co.uk/selfsearch.php
] [WAR] 63 (lib/dml.php:484) (), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) OR, referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) (LOWER(at.tag) = ?), referer: http://web-address.co.uk/selfsearch.php
 [WAR] 63 (lib/dml.php:484) ) and values was (10,test), referer: http://web-address.co.uk/selfsearch.php
 Call stack (most recent first):, referer: http://web-address.co.uk/selfsearch.php
   * get_recordset_sql(", referer: http://web-address.co.uk/selfsearch.php
            SELECT, referer: http://web-address.co.uk/selfsearch.php
                a.id, a.artefa...", array(size 2), 0, 10) at /u0/webroot/virtual/web-address.co.uk/www/lib/dml.php:667, referer: http://web-address.co.uk/selfsearch.php
   * get_records_sql_array(", referer: http://web-address.co.uk/selfsearch.php
             SELECT, referer: http://web-address.co.uk/selfsearch.php
                a.id, a.artefa...", array(size 2), 0, 10) at /u0/webroot/virtual/web-address.co.uk/www/search/internal/lib.php:819, referer: http://web-address.co.uk/selfsearch.php
   * PluginSearchInternal::self_search("test", 10, 0, "all") at Unknown:0, referer: http://web-address.co.uk/selfsearch.php
   * call_user_func_array(array(size 2), array(size 4)) at /u0/webroot/virtual/web-address.co.uk/www/lib/mahara.php:1359, referer: http://web-address.co.uk/selfsearch.php
   * call_static_method("PluginSearchInternal", "self_search", "test", 10, 0, "all") at /u0/webroot/virtual/web-address.co.uk/www/lib/searchlib.php:509, referer: http://web-address.co.uk/selfsearch.php
   * search_selfsearch("test", 10, 0, "all") at /u0/webroot/virtual/web-address.co.uk/www/json/selfsearch.php:45, referer: http://web-address.co.uk/selfsearch.php
 , referer: http://web-address.co.uk/selfsearch.php

[Aaron Wells (u-aaronw)]

As the error stack says, the underyling cause here is a SQL syntax error. It's running this query, which has an empty () in the middle of the WHERE clause:

 SELECT
    a.id, a.artefacttype, a.title, a.description
 FROM
    "artefact" a
    LEFT JOIN "artefact_tag" at ON (at.artefact = a.id)
 WHERE
    a.owner = '10'
    AND (
        ()
        OR
        (LOWER(at.tag) = 'test')
    )
LIMIT 10

I suspect this'll be a bug in the search code, where it's generating a series of search criteria to join together with ORs, and one of them winds up being an empty string or a boolean false.

[Melissa Draper (melissa)]

Hi Andrea,

We're opening a new bug here because lots has changed in 6 years :)

Can you tell us about your setup? Operating system, database type and version, php version, etc.

Are you using any non-core search plugins?

Thanks,
Melissa.

[Andrea Gordon (andrea-synergy-learning)]

Hi Melissa

Thanks for that.

The site is Mahara version 1.6.2 (2012080608).

Using MySQL 5 with PHP 5.3.21 on Debian.

No non-core search plugins.

Many Thanks

Andrea

[Kristina Hoeppner (kris-hoeppner)]

FYI: The portfolio search functionality has been defunct for quite some time (at least since Mahara 1.3). It probably needs quite a bit of overhaul.

[Kristina Hoeppner (kris-hoeppner)]

Hi Andrea,

Is still still happening?

[Launchpad Janitor (janitor)]

[Expired for Mahara because there has been no activity for 60 days.]