Preventing and Cleaning up SPAM

If you have a public facing confluence site, your site may be affected by spammers.

Preventing Spammers

To prevent spammers you will need to:

1) Enable Captcha|. See Configuring Captcha for Spam Prevention.
2) Run confluence behind an Apache Webserver and create rules to block the spammers IP address.

Apache またはシステム レベルでのスパムのブロック

If a spam bot is attacking your Confluence site, chances are they are coming from one IP or a small range of IPs. To find the attacker's IP, it helps to follow the Apache access logs in real time and filter for a page that they are attacking.

For example, if the spammers are creating users you can look for signup.action:

$ tail -f confluence.atlassian.com.log | grep signup.action
1.2.3.4 - - [13/Jan/2010:00:14:51 -0600] "GET /signup.action HTTP/1.1" 200 9956 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)" 37750

You should correlate actual spam users being created with the log entries to make sure you do not block legitimate users. By default, Apache logs the clients IP in the first field of the log line.

Once you have the offender's IP or IP range, you can add it to your firewall's blacklist. For example the popular Shorewall firewall for Linux you can simply do:

# echo "1.2.3.4" >> /etc/shorewall/blacklist
# /etc/init.d/shorewall reload

To block at the Apache level, you can update your Apache vhost config with the line:

Deny from 1.2.3.4

クライアント セッションを切断すること無く変更を適用する "graceful" コマンドで Apache を再起動することができます。

If this still does not stop the spam

1) Turn off public sign up
2) See CONF-1469. Your comments and vote on that issue are very much appreciated.

スパムの削除

プロフィール スパム

This refers to spammers creating accounts on Confluence wikis and posting links to their profile page. This is a particularly common form of spam at the moment.

If you have had many such spam profiles created, it is easier to delete them via SQL.

Find the last real profile

SELECT bodycontentid,body FROM bodycontent WHERE contentid IN 
  (SELECT contentid FROM content WHERE contenttype='USERINFO') 
  ORDER BY bodycontentid DESC; 

スパムが見つかり始めるまで、プロフィール ページの本文に目を通します。大量の範囲を特定する必要がある場合があります。

Find the killset

CREATE TEMP TABLE killset AS SELECT bc.bodycontentid,c.contentid,c.username FROM 
  bodycontent bc JOIN content c ON bc.contentid=c.contentid WHERE 
  bodycontentid >= BOTTOM_OF_SPAM_RANGE AND bodycontendID <= TOP_OF_SPAM_RANGE 
  AND  c.contenttype='USERINFO';

DELETE FROM bodycontent WHERE bodycontentid IN (SELECT bodycontentid FROM killset);

DELETE FROM links WHERE contentid IN (SELECT contentid FROM killset);

DELETE FROM content WHERE prevver IN (SELECT contentid FROM killset);

DELETE FROM attachments WHERE pageid IN (SELECT contentid FROM killset);

DELETE FROM content WHERE contentid IN (SELECT contentid FROM killset);

DELETE FROM os_user_group WHERE user_id IN (SELECT id FROM killset k JOIN os_user o ON o.username=k.username);

DELETE FROM os_user WHERE username IN (SELECT username FROM killset);

Once the spam has been deleted, restart Confluence and run a rebuild of the index - which will remove any references to the spam from the search index.