1124810 - Searching for '---' in Simple Search causes a SQL error

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: General, defect)

Description

[Al Billings [:abillings - ex-MoCo]]

Attachment: Bugzilla_Mozilla_query

We received this email today:

-------- Forwarded Message --------
Subject: 	Bugzilla@Mozilla bug and vulnerability
Date: 	Thu, 22 Jan 2015 11:56:47 -0500
From: 	Marinus Moelker <[e-mail]>
To: 	[e-mail]


Hello,

please see attached screenshots of bug exposing SQL in your Bugzilla
install and steps to reproduce below.

Best regards,

Marinus Moelker

Steps to reproduce.

1. Go to https://bugzilla.mozilla.org/
2. Click on 'Search'
3. In https://bugzilla.mozilla.org/query.cgi
4. In the 'Words' field enter ---
5. Press 'Search'
6. Note exposed SQL

------

I, Al, noted that this was using the simple search. Sample results I saw are:

ugzilla has suffered an internal error:

An error occurred while performing a database operation:
DBD::mysql::db selectcol_arrayref failed: syntax error, unexpected '-' [for Statement "SELECT bugs.bug_id AS bug_id, (MATCH(bugs_fulltext_1.short_desc) AGAINST('---' IN BOOLEAN MODE)) AS relevance
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 (20,19,10,9,94,170,132,184,53,146,23,49,151,150,2,125,142,119,69,136,130,84,35,82,126,158,98,111,144,138,128,32,6,42,26,120,33,29,89,27,140,108,96,181,113,46,76,186,187,178,177,134,52,51,12,50,180,179,163) )
LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 280903
LEFT JOIN bugs_fulltext AS bugs_fulltext_1 ON bugs.bug_id = bugs_fulltext_1.bug_id
WHERE bugs.creation_ts IS NOT NULL
AND (security_map.group_id IS NULL
OR (bugs.reporter_accessible = 1 AND bugs.reporter = 280903)
OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
OR bugs.assigned_to = 280903
OR bugs.qa_contact = 280903)
AND bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') AND MATCH(bugs_fulltext_1.short_desc) AGAINST('---' IN BOOLEAN MODE)
GROUP BY bugs.bug_id
ORDER BY relevance DESC
LIMIT 500
"]

Comment 1

[Al Billings [:abillings - ex-MoCo]]

Attachment: Bugzilla_Mozilla_bug

Comment 2

[Frédéric Buclin]

This is not a security bug. We intentionally display the SQL query for debugging reasons.

I cannot reproduce this error locally, neither with Bugzilla 5.1 nor with 4.2, nor can I reproduce on landfill, GCC Bugzilla or Mageia Bugzilla. I can only reproduce on bmo.

Which MySQL version is bmo running?

Comment 3

[Marinus]

Search with other non-alpha characters reproduces same:

-
+
*
~
etc.

Interestingly enough Bugzilla for Apache OpenOffice does not have this issue. See https://issues.apache.org/ooo/query.cgi

Comment 4

[Reed Loden [:reed]]

Let's kick this over to bmo for debugging first. Too many customizations there.

Comment 5

[Gervase Markham [:gerv]]

I can reproduce this on BMO:
https://bugzilla.mozilla.org/buglist.cgi?query_format=specific&content=---
or similarly with "*" (a different error occurs, suggesting that the character is indeed being interpreted as a metacharacter by the SQL engine). However, when I put the SQL from the error with "---":

SELECT bugs.bug_id AS bug_id, (MATCH(bugs_fulltext_1.short_desc) AGAINST('---' IN BOOLEAN MODE)) AS relevance
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 (13,20,19,87,31,10,155,3,9,18,4,7,8,1,94,169,170,93,166,171,57,53,48,92,174,149,23,123,151,150,2,125,124,162,119,69,84,11,159,158,111,88,160,183,14,90,32,156,6,42,26,120,121,122,43,95,85,86,109,5,96,181,106,100,101,152,153,113,58,46,168,186,187,178,177,17,65,91,52,12,50,103,180,179,163,15) )
LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 9911
LEFT JOIN bugs_fulltext AS bugs_fulltext_1 ON bugs.bug_id = bugs_fulltext_1.bug_id
WHERE bugs.creation_ts IS NOT NULL
AND (security_map.group_id IS NULL
OR (bugs.reporter_accessible = 1 AND bugs.reporter = 9911)
OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
OR bugs.assigned_to = 9911
OR bugs.qa_contact = 9911)
AND bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED') AND MATCH(bugs_fulltext_1.short_desc) AGAINST('---' IN BOOLEAN MODE)
GROUP BY bugs.bug_id
ORDER BY relevance DESC
LIMIT 500

into my local instance of BMO (with a small DB), I get "Empty set" returned, not an error. I'm not sure why that is. Perhaps differing versions of MySQL.

It seems that the error is coming because we are searching IN BOOLEAN MODE:
https://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

That suggests that in one sense the errors are harmless - this is MySQL interpreting these characters as its supposed to. But perhaps we should syntax-check the query before using it if we know it's going to return an error.

If we feel we shouldn't be exposing the SQL, that would make this a problem. This SQL gives the group ID of all the security groups - are those numbers secret? I assume not.

Still, keeping secure until we've investigated fully.

Gerv

Comment 6

[Marinus]

Behavior has changed. It's returning an internal server error page now.

Comment 7

[Gervase Markham [:gerv]]

I still get an SQL error for:

https://bugzilla.mozilla.org/buglist.cgi?query_format=specific&content=---

glob/dkl: are you able to evaluate this, please? I hope comment 5 is useful.

Gerv

Comment 8

[:glob ✱]

investigating.  as per comment 2, exposing the sql (including the group ids) isn't a security issue.

Comment 9

[:glob ✱]

running the query directly against a trunk database works, however running it against a bmo dump results in this error.  this looks like difference between MyISAM and InnoDB's fulltext engine.

simpling escaping of the meta characters wouldn't work because they have meaning.
eg. you can search for "youtube -b2g" to exclude results which match b2g

i think the fix should be to automatically quote fulltext words that contain just boolean search operators:
+ - < > ~ *

Comment 10

[:glob ✱]

Attachment: 1124810_1.patch

Comment 11

[Dylan Hardison [:dylan] (he/him)]

Comment on attachment 8559593 [details] [diff] [review]
1124810_1.patch

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

r=dylan

Comment 12

[Dylan Hardison [:dylan] (he/him)]

Comment on attachment 8559593 [details] [diff] [review]
1124810_1.patch

this is a good edge case test for review history. yes...

Comment 13

[:glob ✱]

To ssh://[e-mail]/webtools/bmo/bugzilla.git
   8ec648e..f3cf75b  master -> master