My [[http://wiki.alexbernstein.com | RebelWiki]] has been getting a lot of comment spam. I've installed BadBehavior about a year ago and that helped for a while. When that started failing, I've installed SpamBlacklist, and that again helped for a some time, but it was struggle to keep the list updated especially as I [[http://hopstoptravel.com | travel]]. In October, even bigger wave of spam came and some pages gotten so big and take so much time to output, that my site started hitting CPU quotas. Also, in addition to comment spam, the pages themselves were now getting spam. So I had to come up with the new solution. I've cleaned up pages with DeleteSpamAction, then installed FreeCap, and finally added my own IP filter.

Here's a brief description of IP filter:
1) Added action MarkSpam that shows admin the number of comments made from each IP and allows blacklisting of IPs
2) Modified SpamBlacklist to log spam to DB instead of a file
3) Modified SpamBlacklist to block IPs that have been blacklisted or that previously hit blacklisted word before

A few days now with no spam. I received a single comment that said something like: "I've accessed your site 730 times" and promptly blacklisted it.

=====DB Changes=====
Added following fileds to wikka_comments
ip, varchar(16)
spam, tinyint(1)
spamrule, varchar(64)

=====MarkSpam=====
%%(php;1)
<?php

// actions/commentsspamlog.php
// written by AlexBernstein
if ($this->IsAdmin())
{
if (isset($_POST['show']) && isset($_POST['spammers'])) {
$wikitext = "<b>Mark following comments as SPAM?</b><br><br>\n";
//Show comments about to be marked as spam
$wikitext .= $this->FormOpen();
foreach ($_POST['spammers'] as $spammer) {
$query = "SELECT * FROM ".$this->config["table_prefix"]."comments WHERE ip = '".mysql_real_escape_string($spammer)."' AND spam = '0' ORDER BY time";
$comments = $this->LoadAll($query);

if ($comments) {
$wikitext .= "<input type=\"checkbox\" name=\"spammers[]\" value=\"".$spammer."\" checked><b>".$spammer."</b><br>\n";

foreach ($comments as $comment) {
$wikitext .= '<div class="comment">'."\n".
'<span id="comment_'.$comment['id'].'"></span>'.$comment['comment']."\n".
"\t".'<div class="commentinfo">'."\n-- ";
$wikitext .= $comment['user'];
$wikitext .= ' ('.$comment['time'].')'."\n";
$wikitext .= "\n\t".'</div>'."\n";
$wikitext .= '</div>'."\n";
}
}
}
$wikitext .= "<input type=\"submit\" value=\"Mark As Spam\" name=\"mark\">";
$wikitext .= $this->FormClose();
} else if (isset($_POST['mark']) && isset($_POST['spammers'])) {
// FINALIZE marking as SPAM
foreach ($_POST['spammers'] as $spammer) {
$query = "UPDATE ".$this->config["table_prefix"]."comments SET spam ='1' WHERE ip ='".$spammer."'";
$this->Query($query);

}
// redirect to page
$this->redirect($this->Href());
} else {
// FIRST page that display potential spammers
$wikitext = "This page manages blacklist for the " .
"comment spam IP filter.<br><br>\n";
$wikitext .= "<h1>Frequent Commenters not yet blacklisted</h1><hr>";

if ($ipentries = $this->LoadAll("SELECT ip, MAX(time) AS lastdate, COUNT(*) AS count FROM ".$this->config["table_prefix"]."comments WHERE spam = '0' GROUP BY ip ORDER BY count DESC;")) {
$wikitext .= $this->FormOpen();
foreach ($ipentries as $ipentry) {
$wikitext .= "<input type=\"checkbox\" name=\"spammers[]\" value=\"".$ipentry['ip']."\"><b>".$ipentry['ip']."</b> blocked ".$ipentry['count']." times, last was ".$ipentry['lastdate']."<br>\n";
}
$wikitext .= "<input type=\"submit\" value=\"Show Comments\" name=\"show\">";
$wikitext .= $this->FormClose();
} else {
$wikitext .= "<em>No entries found.</em>";
}
}
}

// show result
echo trim($wikitext);
?>
%%

=====Modified SpamBlacklist=====
%%(php;1)
<?php
// Spam Blacklisting Plugin for Wikka Wiki
// Copyright (C) Manuel Reimer (Manuel _dot_ Reimer _at_ gmx _dot_ de)
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// version 2 as published by the Free Software Foundation

// More information about SpamBlacklist here: http://wikkawiki.org/SpamBlacklist

// Main spam detection routine. If the message has been spam, then this
// one will call "sb_do_output_magic" and will *exit* the script immediately!
function sb_checkit($wikkaref, $body) {
if (!$wikkaref->config["sbl_page"])
die("SpamBlacklist: Please configure the plugin first!");

// test if user has been blocked by admin
if ($wikkaref->LoadSingle("SELECT spam FROM ".$wikkaref->config["table_prefix"]."comments WHERE ip = '".mysql_real_escape_string($_SERVER["REMOTE_ADDR"])."' AND spam = '1' LIMIT 1")) {
// store new comment
$wikkaref->SaveComment($wikkaref->tag, $body, 3, "");
sb_do_output_magic($wikkaref);
exit();
}

// test if user hit RegExp blacklist before
if ($wikkaref->LoadSingle("SELECT spam FROM ".$wikkaref->config["table_prefix"]."comments WHERE ip = '".mysql_real_escape_string($_SERVER["REMOTE_ADDR"])."' AND spam = '2' LIMIT 1")) {
// store new comment
$wikkaref->SaveComment($wikkaref->tag, $body, 4, "");
sb_do_output_magic($wikkaref);
exit();
}

$body = sb_unhtmlentities(trim($body));
$sb_blacklist = $wikkaref->LoadPage($wikkaref->config["sbl_page"]);
if (!$wikkaref->GetUser() || !$wikkaref->config["sbl_only_anon"]) {
if ($sb_blacklist && isset($sb_blacklist["body"])) {
$sb_blacklist = $sb_blacklist["body"];
$sb_blacklist = explode("\n", $sb_blacklist);
foreach ($sb_blacklist as $sb_expression) {
if (preg_match('/(^\s*$|^\s*#)/', $sb_expression))
continue;
if (preg_match($sb_expression, $body)) {
// store new comment
$wikkaref->SaveComment($wikkaref->tag, $body, 2, $sb_expression);
// if ($wikkaref->config["sbl_logfile"]) {
// $sb_fp = fopen($wikkaref->config["sbl_logfile"], "a");
// if ($sb_fp && flock($sb_fp, LOCK_EX)) {
// $sb_logline = date("M d Y H:i:s") . "\t";
// $sb_logline .= $sb_expression . "\t";
// $sb_logline .= $wikkaref->GetUserName() . "\n";
// fwrite($sb_fp, $sb_logline);
// fclose($sb_fp);
// }
// }
sb_do_output_magic($wikkaref);
exit();
}
}
}
}
}

// Function for decoding all html entities
// http://www.php.net/manual/en/function.html-entity-decode.php
function sb_unhtmlentities($string) {
$string = html_entity_decode($string);
$string = preg_replace('~&#x([0-9a-f]+);~ei', 'chr(hexdec("\\1"))', $string);
$string = preg_replace('~&#([0-9]+);~e', 'chr("\\1")', $string);
return $string;
}

// Function for doing the output magic
// Will send the user a message first
// Then a short definition of "spam" is sent *really* slow, to slow down
// the spammer (teergrubing). The whole process takes about 20 seconds.
// This should be within the "max_execution_time" of most providers.
function sb_do_output_magic($wikkaref) {
$slow_message = array("Spamming", "is", "the", "abuse", "of", "electronic", "messaging", "systems", "to", "send", "unsolicited", "bulk", "messages,", "which", "are", "almost", "universally", "undesired.");

while(@ob_end_clean());

$headercode = file_get_contents("actions/header.php");
$headercode = str_replace('$this->', '$wikkaref->', $headercode);
eval("?>" . $headercode);

print("<div class=\"page\">");
print $wikkaref->config["sbl_message"] . "<br/>\n<br/>\n";
flush();
sleep(1);
foreach ($slow_message as $word) {
print $word . " ";
flush();
sleep(1);
}
print "</div>";

$footercode = file_get_contents("actions/footer.php");
$footercode = str_replace('$this->', '$wikkaref->', $footercode);
eval("?>" . $footercode);

flush();
sleep(1);
print "<div class=\"smallprint\">Spam notice was generated in > 20 seconds. ";
flush();
sleep(1);
print "Spam filtering powered by <a href=\"http://www.wikkawiki.org/SpamBlacklist\">SpamBlacklist<a>. <a href=\"http://en.wikipedia.org/wiki/teergrubing\">Teergrubing</a> ends here ;-)</div>\n</body>\n</html>";
flush();
sleep(1);
}
?>
%%


=====Changes to Wakka.class.php=====
%%(php;1)
// COMMENTS
function LoadComments($tag) { return $this->LoadAll("SELECT * FROM ".$this->config["table_prefix"]."comments WHERE page_tag = '".mysql_real_escape_string($tag)."' AND spam = '0' ORDER BY time"); }
function LoadRecentComments($limit = 50) { return $this->LoadAll("SELECT * FROM ".$this->config["table_prefix"]."comments WHERE spam = '0' ORDER BY time DESC LIMIT ".$limit); }
function LoadRecentlyCommented($limit = 50)
{
$sql = "SELECT comments.id, comments.page_tag, comments.time, comments.comment, comments.user"
. " FROM ".$this->config["table_prefix"]."comments AS comments"
. " LEFT JOIN ".$this->config["table_prefix"]."comments AS c2 ON comments.page_tag = c2.page_tag AND comments.spam = '0' AND c2.spam = '0' AND comments.id < c2.id"
. " WHERE c2.page_tag IS NULL AND comments.spam = '0' "
. " ORDER BY time DESC "
. " LIMIT ".$limit;
return $this->LoadAll($sql);
}
function SaveComment($page_tag, $comment, $spam = 0, $spamrule= "")
{
// get current user
$user = $this->GetUserName();
$ip = $_SERVER["REMOTE_ADDR"];

// add new comment
$this->Query("INSERT INTO ".$this->config["table_prefix"]."comments SET ".
"page_tag = '".mysql_real_escape_string($page_tag)."', ".
"time = now(), ".
"comment = '".mysql_real_escape_string($comment)."', ".
"user = '".mysql_real_escape_string($user)."', ".
"ip = '".mysql_real_escape_string($ip)."', ".
"spam = '".$spam."', ".
"spamrule = '".$spamrule."'");
}
%%