730984 - A single whitespace in the Status Whiteboard field generates an invalid SQL query

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[David J. Fiddes]

User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.2) Gecko/20100101 Firefox/10.0.2
Build ID: 20120215223356

Steps to reproduce:

Used a python script based on PyBugz (http://code.google.com/p/pybugz/) to generate a query against bugzilla. This created the following GET request:

http://bugzilla.indigovision.com/buglist.cgi?status_whiteboard=%20&bugidtype=include&keywords=releasenote&emailreporter1=1&email1=%20&query_format=advanced&emailassigned_to1=1&ctype=csv&status_whiteboard_type=allwordssubstr&emailtype1=substring&order=Product&emailcc1=1&list_id=42


Actual results:

Bugzilla 4.2 fails with an internal SQL error:

Software error:

DBD::mysql::st execute failed: 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 bugs.bug_id
ORDER BY product
LIMIT 10000' at line 15 [for Statement "SELECT bugs.bug_id AS bug_id, bugs.bug_severity AS bug_severity, bugs.priority AS priority, bugs.bug_status AS bug_status, bugs.resolution AS resolution, map_product.name AS product, bugs.op_sys AS op_sys, map_assigned_to.login_name AS assigned_to, bugs.short_desc AS short_desc, bugs.target_milestone AS target_milestone, bugs.version AS version, bugs.delta_ts AS changeddate, map_component.name AS component
  FROM bugs
LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (9,11,10,24,12,13,7,3,6,8,4,5,2,1,22,17,23,16,14,18) )
LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 43
INNER JOIN products AS map_product ON bugs.product_id = map_product.id
INNER JOIN profiles AS map_assigned_to ON bugs.assigned_to = map_assigned_to.userid
INNER JOIN components AS map_component ON bugs.component_id = map_component.id
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 43)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 43
)
   AND bugs.bug_id IN (SELECT bug_id FROM keywords INNER JOIN keyworddefs ON keywords.keywordid = keyworddefs.id WHERE  keyworddefs.name IN ('releasenote') ) AND 
GROUP BY bugs.bug_id
ORDER BY product
LIMIT 10000
"] at /var/www/bugzilla/buglist.cgi line 834



Expected results:

A bug list should have been returned with all bugs containing the "releasenote" keyword.

This has previously worked fine with versions of bugzilla up to and including 3.6.

Simplifying the query by removing "status_whiteboard=%20&" allows the query to run successfully.

Comment 1

[Frédéric Buclin]

I can reproduce the crash using this query.

Comment 2

[Frédéric Buclin]

Actually, this is unrelated to old queries. Simply put a whitespace in the status whiteboard field, and see the crash.

Comment 3

[Frédéric Buclin]

Bugzilla 4.0.5 is not affected -> regression.

Comment 4

[Frédéric Buclin]

Attachment: patch, v1

Comment 5

[Max Kanat-Alexander]

Comment on attachment 603035 [details] [diff] [review]
patch, v1

Review of attachment 603035 [details] [diff] [review]:
-----------------------------------------------------------------

::: Bugzilla/Search.pm
@@ +1699,5 @@
>      $self->do_search_function(\%search_args);
> +
> +    # If term is left empty, then this means the criteria
> +    # has no effect and can be ignored.
> +    return unless $search_args{term};

My only concern with this solution is that it might hide other bugs. For example, why is the WHERE generator ever generating an empty AND clause? That seems like the actual bug here.

Comment 6

[Frédéric Buclin]

(In reply to Max Kanat-Alexander from comment #5)
> My only concern with this solution is that it might hide other bugs. For
> example, why is the WHERE generator ever generating an empty AND clause?

Because the status whiteboard field is parsed on whitespaces, and so you get an empty list, and then the generator joins everything using AND. So when you join a list which contains "", then you get ... AND  AND .... My patch is here to explicitly skips such cases. I don't think that's a bug in the generator.

Comment 7

[David Lawrence [:dkl]]

This patch also fixes the issue for me and may be better related to Max's comment:

=== modified file 'Bugzilla/Search.pm'
--- Bugzilla/Search.pm	2012-03-10 14:56:55 +0000
+++ Bugzilla/Search.pm	2012-03-15 03:28:49 +0000
@@ -2633,14 +2633,14 @@
     my ($full_field, $value) = @$args{qw(full_field value)};
     
     my @terms = $self->_substring_terms($args);
-    $args->{term} = join("\n\tOR ", @terms);
+    $args->{term} = join("\n\tOR ", @terms) if @terms;
 }
 
 sub _allwordssubstr {
     my ($self, $args) = @_;
     
     my @terms = $self->_substring_terms($args);
-    $args->{term} = join("\n\tAND ", @terms);
+    $args->{term} = join("\n\tAND ", @terms) if @terms;
 }

Comment 8

[Frédéric Buclin]

(In reply to David Lawrence [:dkl] from comment #7)
> This patch also fixes the issue for me

Did you try your patch with the example given in the URL field? It doesn't work, and throws an incorrect error message. I replied to mkanat's comment 5, and as I said, I don't that's a bug in the generator.

Comment 9

[Frédéric Buclin]

(In reply to Frédéric Buclin from comment #8)
> and as I said, I don't that's a bug in the generator.

I don't *think* ...

Comment 10

[David Lawrence [:dkl]]

Comment on attachment 603035 [details] [diff] [review]
patch, v1

Review of attachment 603035 [details] [diff] [review]:
-----------------------------------------------------------------

This change looks correct and solves the problem reported. r=dkl

Comment 11

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/Search.pm
Committed revision 8167.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.2/
modified Bugzilla/Search.pm
Committed revision 8058.

Comment 12

[Tom Dickson]

I can get a very similar thing to occur on my 4.2.1 installation by searching for " cats" or any word with a space in front of it in the Advanced search screen:

https://bugzilla/buglist.cgi?query_format=advanced&list_id=13786&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&longdesc=%20cats&longdesc_type=allwordssubstr

is the URL that causes it on our system, and it dumps out:

[Fri Jun 22 14:25:27 2012] [error] DBD::mysql::st execute failed: 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 ')\n        AND bugs.bug_id IN (SELECT bug_id FROM longdescs WHERE INSTR(thetext, ' at line 14 [for Statement "SELECT bugs.bug_id AS bug_id, bugs.bug_severity AS bug_severity, bugs.priority AS priority, bugs.bug_status AS bug_status, bugs.resolution AS resolution, map_product.name AS product, map_component.name AS component, map_assigned_to.login_name AS assigned_to, bugs.short_desc AS short_desc, bugs.delta_ts AS changeddate\n  FROM bugs\nLEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (1,12,10,61,11,13,9,4,8,5,6,7,3,2,43,42,19,26,30,45,39,14,57,22,53,44,17,21,40,54,27,29,48,25,28,36,46,33,37,41,47,59,24,32,52,31,50,51,16,18,34,20,56,49,38,58,23,55,60,62,35) )\nLEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 1\nINNER JOIN products AS map_product ON bugs.product_id = map_product.id\nINNER JOIN components AS map_component ON bugs.component_id = map_component.id\nINNER JOIN profiles AS map_assigned_to ON bugs.assigned_to = map_assigned_to.userid\n WHERE bugs.creation_ts IS NOT NULL\n   AND (security_map.group_id IS NULL\n        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 1)\n        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)\n        OR bugs.assigned_to = 1\n)\n   AND  bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')  AND bugs.bug_id IN (SELECT bug_id FROM longdescs WHERE )\n        AND bugs.bug_id IN (SELECT bug_id FROM longdescs WHERE INSTR(thetext, 'cats') > 0)\nGROUP BY bugs.bug_id\nORDER BY bug_id DESC\nLIMIT 500\n"] at /home/bugzilla/buglist.cgi line 829\n\tModPerl::ROOT::Bugzilla::ModPerl::ResponseHandler::home_bugzilla_buglist_2ecgi::handler('Apache2::RequestRec=SCALAR(0x2b987b120260)') called at /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm line 204\n\teval {...} called at /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm line 204\n\tModPerl::RegistryCooker::run('Bugzilla::ModPerl::ResponseHandler=HASH(0x2b987a26b750)') called at /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm line 170\n\tModPerl::RegistryCooker::default_handler('Bugzilla::ModPerl::ResponseHandler=HASH(0x2b987a26b750)') called at /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/Registry.pm line 31\n\tModPerl::Registry::handler('Bugzilla::ModPerl::ResponseHandler', 'Apache2::RequestRec=SCALAR(0x2b987b120260)') called at /home/bugzilla/mod_perl.pl line 138\n\tBugzilla::ModPerl::ResponseHandler::handler('Bugzilla::ModPerl::ResponseHandler', 'Apache2::RequestRec=SCALAR(0x2b987b120260)') called at -e line 0\n\teval {...} called at -e line 0\n

in the logs.

Shall I create a new bug?

Comment 13

[Frédéric Buclin]

(In reply to Tom Dickson from comment #12)
> Shall I create a new bug?

No need. For some reason, the error no longer happens in 4.3.1 (as expected), but is still present in 4.2.1. So the code related to queries must be different between 4.2.1 and 4.3.1. Reopening for investigation!

Comment 14

[Frédéric Buclin]

In fact, this problem has already been fixed in Bugzilla 4.2.2, see bug 760075.