Closed Bug 1124810 Opened 5 years ago Closed 5 years ago

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

Categories

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

Production
defect
Not set

Tracking

()

RESOLVED FIXED

People

(Reporter: abillings, Assigned: glob)

Details

(Keywords: testcase-wanted)

Attachments

(3 files)

Attached image 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 <mpmoelker@gmail.com>
To: 	security@mozilla.org


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
"]
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?
Assignee: database → query-and-buglist
Group: bugzilla-security
Component: Database → Query/Bug List
OS: Mac OS X → All
Hardware: x86 → All
Summary: Searching for '---' in simple bugzilla search exposes SQL → Searching for '---' in Simple Search causes a SQL error
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
Let's kick this over to bmo for debugging first. Too many customizations there.
Assignee: query-and-buglist → nobody
Component: Query/Bug List → General
Product: Bugzilla → bugzilla.mozilla.org
QA Contact: default-qa
Version: unspecified → Production
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
Group: bugzilla-security
Behavior has changed. It's returning an internal server error page now.
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
investigating.  as per comment 2, exposing the sql (including the group ids) isn't a security issue.
Assignee: nobody → glob
Group: bugzilla-security
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:
+ - < > ~ *
Attached patch 1124810_1.patchSplinter Review
Attachment #8559593 - Flags: review?(dylan)
Flags: sec-bounty-
Comment on attachment 8559593 [details] [diff] [review]
1124810_1.patch

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

r=dylan
Attachment #8559593 - Flags: review?(dylan)
Whiteboard: [needs-test]G
Whiteboard: [needs-test]G → [needs-test]
Comment on attachment 8559593 [details] [diff] [review]
1124810_1.patch

this is a good edge case test for review history. yes...
Attachment #8559593 - Flags: review+
To ssh://gitolite3@git.mozilla.org/webtools/bmo/bugzilla.git
   8ec648e..f3cf75b  master -> master
Status: NEW → RESOLVED
Closed: 5 years ago
Keywords: testcase-wanted
Resolution: --- → FIXED
Whiteboard: [needs-test]
You need to log in before you can comment on or make changes to this bug.