Bottom Feeder Filter

Posted December 28th, 2009 by tstarback

Background of Drupal SEO Spam Issue:

We host another drupal site (saveagallon.org) that recently became inundated with SEO spammers (A.K.A. Link farm and Spamdexing) that only wanted to add their spammy links to the user profile page. Analytics showed us that most of this traffic was coming from the Philippines, India and Pakistan. Suddenly our site jumped from 5 new accounts a day to over 50 then 75. I love Drupal, but the User Administration was not designed to easily check and disable these type of accounts. At first we assumed they were automated bots.

Activated email verification.
Added Recaptcha.

Neither of these (I know they should have already been deployed) slowed them down at all. These were real people signing up and could get through any defense we added. In fact many of them tailored their profile to match the site. Some even posted relevant comments.

Pulling out the Big Guns

Along with good Drupal User Management practices including recaptcha and email verification you might want to block access from problem countries. I don't recommend this option lightly, but it did give us breathing room to figure out a solution. If your site will be fine without traffic from these problem countries just block the entire IP range. I found a list of country specific IP ranges on this site:
http://www.countryipblocks.net
Select the countries you want to block from the list, specify the format as ".htaccess deny" then open your .htaccess file, paste in the new denied list at the bottom, save and upload. This cut our SEO spam by around 90%. I suspect the issue will come back around as more sites do this and these guys figure out a way to hide their originating ip addresses.

Management is still going to be important.

Even if you block the major offenders, some will still get through. The procedure we created, is simple.

Check all accounts that were recently accessed for links in their profiles.
Make a list showing what we found.
Click the "block user" button.

When you click the "block user" button this is what happens:

We change the users status to "blocked."*
Grab the users IP address.
Add an access rule to the drupal database banning that IP.**

* This not only prevents access, but allows us an easy way to find and delete them from the Drupal User Administration.
** I realize it's probably futile to ban the individual IP, but it still feels good... After a week or so go back, sort that table by IP and then modify the rules to block a wider range.

Disclaimer:

This code was done in a fit of desperation at 3am. We've made it generic enough to share, but it might not apply to all sites. Please also note that we're sure it violates all kinds of "Drupal rules" but like we said we were desperate, and had not written a module for drupal yet and fell back on what we can do, regular old php. Eventually, with enough feedback We hope to make BFF available as a Module, but for now if you are desperate enough this is how it works.

This is the node code.

We limit access to this page by using an "admin" role.
Change "admin" to what ever you need and set the "input format" to php.

Here are the functions:

FUNCTION FIND_AD_BOTS($time){
include "sites/default/sqllink.php";
$ten_days_ago = time() - (60 * 60 * 24 * 10);
// the assumption is that if it's older than 10 days they are a good account.

$sql_1 ="SELECT users.uid as uid, users.mail as mail, users.name as name, profile_values.value as value FROM users JOIN profile_values ON profile_values.uid = users.uid WHERE users.status = '1' AND users.uid != '1' AND users.created >= '$ten_days_ago' AND users.access >= '$time' AND profile_values.value LIKE '%http%'";
$result = mysql_query($sql_1,$connection) or die ("Couldn't Execute query $sql_1");
$count = mysql_num_rows($result);

$bad_bots = "Found: $count <br><br>";

while ($row = mysql_fetch_array($result)) {
   $uid = $row['uid'];
   $name = $row['name'];
   $email = $row['mail'];
   $value = $row['value'];
  $ip = GET_USER_IP($uid);
  $bad_bots .= "
  <form action=\"/?q=node/105&uid=$uid\" method=\"post\">
  <input name=\"action\" type=\"hidden\" value=\"block\" />
  User id:$uid <a href=\"?q=user/$uid\">$name</a> $email $ip
  <br>	
  $value
  <br>
  <input name=\"\" type=\"submit\" value=\"block user\" />
  
  <br>
  <br>
  <br>

</form>";
   }

return "$bad_bots";

}

FUNCTION MY_BLOCK_USER($uid){
include "sites/default/sqllink.php";
$ip = GET_USER_IP($uid);
 $sql_update = "
	 			 UPDATE users SET			 		 	
						status = \"0\"
						WHERE uid =\"$uid\"
	 					";
	
$result2 = mysql_query($sql_update,$connection) or die ("Couldn't Execute query $sql_update");

if (!empty($ip)){
	ADD_ACCESS_RULE($ip);
	}					  
	//now we check any eco convessions and delete them.
	
	$sql_3 = " DELETE FROM sag_confession
				WHERE uid = \"$uid\"";
				$result3 = mysql_query($sql_3,$connection) or die ("Couldn't Execute query $sql_3");
	$sql_4 = " DELETE FROM comments
				WHERE uid = \"$uid\"";
				$result4 = mysql_query($sql_4,$connection) or die ("Couldn't Execute query $sql_4");
}

FUNCTION GET_USER_IP($uid){
include "sites/default/sqllink.php";

$sql_1 ="SELECT * FROM watchdog WHERE uid = '$uid' AND hostname != '' LIMIT 1";
$result = mysql_query($sql_1,$connection) or die ("Couldn't Execute query $sql_1");
$row = mysql_fetch_array($result);
   return $row['hostname'];
   
}

FUNCTION ADD_ACCESS_RULE($ip){
include "sites/default/sqllink.php";

$sql_1 ="SELECT * FROM access WHERE mask = '$ip'";
$result = mysql_query($sql_1,$connection) or die ("Couldn't Execute query $sql_1");
$count = mysql_num_rows($result);

if ($count == 0){
	$sql = " INSERT INTO access
				(mask, type, status)
				VALUES
				(\"$ip\", \"host\", \"0\" ) ";
	   			
				$result2 = mysql_query($sql,$connection) or die ("Couldn't Execute query $sql");
	}
   
}
?>

Feedback?

Got questions, problems or a better idea?
Email them to me!

tstarback's blog