error in SQL syntax on search
| Project: | Fuzzy Search |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Hi, this module works great when i am logged in as admin, but when i enable it for anonymous users i get the following error:
user warning: 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 ') GROUP BY n.nid ORDER BY completeness DESC, score DESC LIMIT 0, 10' at line 1 query: SELECT DISTINCT(n.nid), SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM search_fuzzy_index AS s LEFT JOIN node AS n ON (n.nid = s.nid) INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'content_access_rid'))) AND ( (( (ngram = 'dag' AND completeness <50.1) OR (ngram = 'ago' AND completeness <50.1)) AND n.status = 1) ) GROUP BY word_id HAVING percent > 40) AS n ) GROUP BY n.nid ORDER BY completeness DESC, score DESC LIMIT 0, 10 in /var/www/iz3/20070915_getcars/includes/database.mysql.inc on line 172.
(searched for 'dago')
I dont have the time now to check it thoroughly, but i thought i may as well let u know. I thought it may have something to do with node permission module...
#1
I just recently checked this out on a new project I'm working on and yes I do receive the same errors when I have my users doing searches. This definitely has to do with the url re-writing that takes place when the node access information is inserted when db_rewrite_sql is called. This is a high priority for me and will work hard to get this fixed asap.
#2
Hiya - just wondering if there was any movement on this - I'm launching a site on monday so at the moment I've just got the block switched off for anonymous users. If you have any pointers as to where to look, I can attempt to dive into the code.
#3
OK, ignore that, I've uninstalled it - didn't realise it errored for authenticated users too, only works for webmaster. Plus I'm trying to hide searching of particular content types, specifically issues as I'm using the case tracker module for internal bug reports. So I'm using the search block module, but I am getting the feeling that fuzzy search doesn't take blocked types into account because I'm still getting the result I don't want to see.
#4
I've been having the same problem, it seems that this is only a problem when users do not have node administrative access. Don't have a fix for this but hopefully this helps.
#5
Hi,
i also have this problem.
Yesterday i played around a little with the problem, and i found out the following:
A part of the SQL-Query looks like this:
... GROUP BY word_id HAVING percent > 40) AS n ) GROUP BY n.nid ...
and the ")" between "AS n" and "GROUP BY" ist wrong and results in an SQL-Error.
This ")" and some other things are made by the "db_rewrite_sql".
Here is an very dirty workaround i made to get it working. I am not shure of any security vulnerability!
File: fuzzysearch.module
Line: 580
Original:
$query = pager_query(db_rewrite_sql("SELECT n.nid, SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM {search_fuzzy_index} AS s LEFT JOIN {node} AS n ON (n.nid = s.nid) WHERE (($clause) AND n.status = 1) GROUP BY word_id HAVING percent > $min_completeness) AS n GROUP BY n.nid ORDER BY completeness DESC, score DESC"), 10, 0, "SELECT COUNT(DISTINCT(n.nid)) FROM (SELECT n.nid, CEILING(SUM(completeness)) completeness, SUM(score) score FROM {search_fuzzy_index} AS n WHERE $clause GROUP BY word_id HAVING SUM(completeness) > $min_completeness ORDER BY completeness DESC, score DESC) AS n");Workaround:
$q = db_rewrite_sql("SELECT n.nid, SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM {search_fuzzy_index} AS s LEFT JOIN {node} AS n ON (n.nid = s.nid) WHERE (($clause) AND n.status = 1) GROUP BY word_id HAVING percent > $min_completeness) AS n GROUP BY n.nid ORDER BY completeness DESC, score DESC");$q = str_replace("AS n ) GROUP BY", "AS n GROUP BY", $q);
$query = pager_query($q, 10, 0, "SELECT COUNT(DISTINCT(n.nid)) FROM (SELECT n.nid, CEILING(SUM(completeness)) completeness, SUM(score) score FROM {search_fuzzy_index} AS n WHERE $clause GROUP BY word_id HAVING SUM(completeness) > $min_completeness ORDER BY completeness DESC, score DESC) AS n");
Mabey this ends up in an issue for the drupal-core-team.
Best regards.
#6
I know this is being worked on from the note on the module page. I'll be prepared to test any fixes asap when a potential fix becomes available.
#7
I raised this as a issue (which i've since flagged as a duplicate) here: http://drupal.org/node/279884, and can offer the following detail: if you give a user the modr8 permission "moderate content" then they don't get this error.
#8
i've just replaced the SQL (as per richi - November 7, 2007 - 14:43) and it seems to be ok. haven't done any thorough testing but will post again if i see a problem with it.