489109 - Junk data (javascript and SQL) in "event" dropdown

Status: VERIFIED FIXED

(support.mozilla.org :: Knowledge Base Software, task, P4)

Description

[Collin]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8
Build Identifier: n/a

When checking the "event" dropdown, I am greeted with a long list of javascript and SQL injections.


HTML readout:
http://yrikreirrh.pastebin.com/m320bb484

Screenshot:
http://collin1000.com/nastiesinsumo.png

Reproducible: Always

Steps to Reproduce:
1. log in to sumo
2. go to https://support.mozilla.com/tiki-user_watches.php
3. click on event dropdown
4. scroll to bottom of list
Actual Results:  
I see the injections.

Expected Results:  
Not had those...

n/a

Comment 1

[Matthew Middleton (:zzxc)]

All these items seem to be properly escaped, so there doesn't seem to be SQL or script injection here.  Users shouldn't be able to insert junk events in watches, however.

Comment 2

[Reed Loden [:reed]]

Is this not the same as bug 480458? Looks like bug 480458, comment #5 was never run on production? I'll check on it, and if it hasn't, I'll run it.

Comment 3

[Stephen Donner [:stephend] Not actively reading bugmail]

Reed: it hasn't been; thanks in advance for running it!

Comment 4

[Reed Loden [:reed]]

(In reply to comment #3)
> Reed: it hasn't been; thanks in advance for running it!

I ran it on staging and production... it didn't change anything.

Comment 5

[Stephen Donner [:stephend] Not actively reading bugmail]

Hrm; I guess bug 480458 was left to be just about the code, then.  Eric, mind fixing the SQL here?

Comment 6

[E. Cooper [:ecooper]]

(In reply to comment #5)
> Hrm; I guess bug 480458 was left to be just about the code, then.  Eric, mind
> fixing the SQL here?

The SQL from bug 480458 cleaned the table for injections from that bug. If the junk is still there, it's from a different vector.

Could we get the output of this (from staging) attached to the bug?

SELECT * FROM tiki_user_watches where event NOT IN('forum_post_thread', 'forum_post_topic', 'wiki_page_changed', 'new_in_category', 'category_changed_in_lang', 'wiki_page_in_lang_created', 'wiki_page_created', 'category_changed', 'forum_post_and_thread')

Or you can just use a select and group by, but this will let us see trends in the junk data and if it was inserted multiple times.

Comment 7

[Reed Loden [:reed]]

(In reply to comment #6)
> Could we get the output of this (from staging) attached to the bug?
....
mysql> SELECT * FROM tiki_user_watches where event NOT IN('forum_post_thread',
    -> 'forum_post_topic', 'wiki_page_changed', 'new_in_category',
    -> 'category_changed_in_lang', 'wiki_page_in_lang_created', 'wiki_page_created',
    -> 'category_changed', 'forum_post_and_thread')
    -> ;
Empty set (0.00 sec)

Pretty sure we truncate the tiki_user_watches table every time we sync prod to staging. I ran it on prod as well, and it returned 9071 rows.

Comment 8

[E. Cooper [:ecooper]]

(In reply to comment #7)
> (In reply to comment #6)
> > Could we get the output of this (from staging) attached to the bug?
> ....
> mysql> SELECT * FROM tiki_user_watches where event NOT IN('forum_post_thread',
>     -> 'forum_post_topic', 'wiki_page_changed', 'new_in_category',
>     -> 'category_changed_in_lang', 'wiki_page_in_lang_created',
> 'wiki_page_created',
>     -> 'category_changed', 'forum_post_and_thread')
>     -> ;
> Empty set (0.00 sec)
> 
> Pretty sure we truncate the tiki_user_watches table every time we sync prod to
> staging. I ran it on prod as well, and it returned 9071 rows.

Duh. I forgot we were doing that. I just didn't want to run the SQL on prod.

Any chance you could attach the output to the bug (or just send it to me)? It's needed to figure out where this stuff is coming from. 

9K does seems like a lot, though.

Comment 9

[Reed Loden [:reed]]

(In reply to comment #8)
> Duh. I forgot we were doing that. I just didn't want to run the SQL on prod.

We have extremely beefy database servers. Running that query wasn't even a drop in the bucket for the slave server I ran it on (it took 0.08 sec). :)

> Any chance you could attach the output to the bug (or just send it to me)? It's
> needed to figure out where this stuff is coming from.

It's 2.1MB, so I'll e-mail it to you at your @mozilla.com account.

Comment 10

[E. Cooper [:ecooper]]

Attachment: patch v1

This restricts what sort of watches can be assigned on webroot/tiki-index.php and webroot/tiki-view_forum_thread.php. It should stop user inputed data while retaining intended functionality.

Most of tiki reuses code via ctrl+c and ctrl+p, so it's likely that similar vectors exist. This patch closes the vectors used to enter the junk data outputed via comment 6.

STR
https://support-stage.mozilla.org/en-US/kb/Installing+Firefox?watch_event=whscheck()&watch_object=whscheck()&watch_action=add

https://support-stage.mozilla.org/tiki-view_forum_thread.php?locale=en-US&forumId=1&comments_parentId=311641&watch_event=whscheck()&watch_object=whscheck()&watch_action=add

Just for the sake of familiarity, whscheck() was used to signify affected inputs.

Comment 11

[E. Cooper [:ecooper]]

Attachment: sql to clean `tiki_user_watches` v1

This should clean out the junk event column data. Realistically, we could probably just delete all the watches associated to that user.

Limit of 9070 since one of the returned rows from comment 6 was legit and showed up due to a typo ('forum_post_and_thread' should have been 'forum_post_topic_and_thread'). Please look over this carefully.

Comment 12

[Reed Loden [:reed]]

If you just want to remove all the watches for [e-mail], that's fine... it's just sentinel's account, so it's not a real user.

Comment 13

[Collin]

(In reply to comment #12)
> If you just want to remove all the watches for [e-mail], that's
> fine... it's just sentinel's account, so it's not a real user.


Its showing up in my account too, and that is not my account. :S

Comment 15

[Laura Thomson :laura]

Status please?

Comment 16

[E. Cooper [:ecooper]]

I was tangled up with p10n, moving to 1.3.

The SQL should be adjusted to comment 12.

Comment 17

[James Socol [:jsocol, :james]]

Moving to 1.5.1 to keep on the radar, didn't make 1.5.

Comment 18

[Laura Thomson :laura]

Attachment: SQL to clean out sentinel watches

Comment 19

[James Socol [:jsocol, :james]]

Comment on attachment 421078 [details]
SQL to clean out sentinel watches

SELECT COUNT(*) FROM tiki_user_watches WHERE user='[e-mail]';

Before:
15775

After:
0

Comment 20

[Laura Thomson :laura]

This now just needs to be run in the maintenance window.  Leaving open until we have a push bug.

Comment 21

[Laura Thomson :laura]

This was done in bug 546712.

Comment 22

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED; https://support.mozilla.com/tiki-user_watches.php is fine now.