1704311 - Improperly sanitized string hit a mysql bug that crash the databases

Status: RESOLVED FIXED

(Tree Management :: Treeherder: Infrastructure, defect, P1)

Description

[Sarah Clements [:sclements]]

Friday afternoon around 12:22pm PST all of the Treeherder RDS instances were down and rebooting with connections being denied. It recovered within the hour but based on further investigations with :dividehex, we discovered that a specific query was hitting a mysql bug that was causing the databases to crash and reboot.

The query in question is:

SELECT id, summary, crash_signature, keywords, os, resolution, status, MATCH (`summary`) 
AGAINST ('\"input password unmask.html#abc_def 0 7 7 7\"' IN BOOLEAN MODE) 
AS relevance FROM bugscache 
WHERE 1 AND resolution = '' AND `summary` 
LIKE CONCAT ('%%', 'input-password-unmask.html#abc=_def-0-7-7-7', '%%') 
ESCAPE '=' AND modified >= '2020-04-09 19:22:58.963590' 
ORDER BY relevance DESC LIMIT 0,50;

which is old code in bugscache.search - used to provide bug suggestions (bugzilla bugs stored in bugscache) based on failure lines via an API and also done during log parsing of those failure lines to "warm the cache".

The offending line was this search term (I believe that came from a live backing log), '\"input password unmask.html#abc_def 0 7 7 7\"' with the escaped quotes being the culprit.

I've added the backslash and quotes to the characters we should be removing within Bugscache.search, but I'll also add some test cases to account for this edge case and investigate whether there is a mysql or django sanitation function we should be using instead (and whether we should still be using raw sql in that function).

I've also temporarily disabled retrieving the bug suggestions in the cache during log parsing as an extra precaution (bug suggestions are still surfaced when a user clicks on a Job in the UI), but the real fix is in stripping the escaped quotes which I was able to safely test against the replica.

Comment 1

[Sarah Clements [:sclements]]

Attachment: GitHub Pull Request

Comment 2

[Sarah Clements [:sclements]]

https://github.com/mozilla/treeherder/commit/9f0793f2c945fceee24e82dc11e3758e8aa8873b has merged and deployed to production.

Comment 3

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/treeherder/pull/7100

Comment 4

[Sarah Clements [:sclements]]

This issue hit again this morning and I was able to find the job that hit the bug_suggestions API in papertrail, which helped me to do some better debugging. I missed a place where we were wrapping the string in quotes to do a full text search by phrase. This particular string '"input password unmask.html#abc_def 0 7 7 7"' hits the mysql bug and causes the db to crash. I tried removing the hash and other characters and words even, but it still happened when testing it against the replica unless its not wrapped in quotes. I have no idea why this particular string does that so I've disabled full text search by phrase.

That change, some test cases and enabling caching of bug suggestions in ingestion, has been merged and deployed to prod: https://github.com/mozilla/treeherder/commit/b54fbc64ed882e59a98c020d77d5554270c8d7c0