#!/usr/local/bin/php -q 
<?php
###########################################################################################################
# This is step one in my personal fight against spam on MGAPS. What I'm going to do here is issue queries 
# to the database on a nightly basis that will delete spam entries that may have gotten in during the 
# day. This involves the use of two different queries.
# 
# 1) DELETE FROM b2comments WHERE LOCATE('search term', comment_author) != 0;
#
# 2) DELETE FROM b2comments WHERE LOCATE('search term', comment_author_url) != 0;
#
# These would work on any field / table combo. Those listed above are just particular to b2. 
#
# There are a couple of other things to be done here in the even that you are using an older version 
# of b2 (I don't know what any newer(?) versions are doing). Many of the tables are indexed enough. For 
# example, the search function has allways been slow, but adding a fulltext index to 
# b2comments.comment_author and b2comments.comment_author_url will provide big speed improvements not only 
# in speed on the site, but for these queries as well. The same goes for adding an index to 
# b2comments.comment_post_id. 
# 
# There is one drawback to using this approach, but it too can be handled automatically. When one deletes 
# something from a table in MySQL, the actual physical location in the file remains (I prolly didn't explain 
# it correctly) so what happens is you end up having an overhead in the file. If you look at the phpMyAdmin 
# output for a table (under the structure tab), there is a section titled 'space usage'. If there is a 
# row in there titled 'overhead', it's referring to the portions of the file that once had meaningful data.
# If this amount is relatively small in relation to the overall size of the file, there will be no performance 
# hit, but don't let it grow too big! Here is the documentation for the OPTIMIZE TABLE function that will 
# rectify this condition.
#
# http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html
#
# Now obviously, all of this is an after the fact solution. There are a couple of other ideas I will 
# attempt to employ as well. One of them will be another filter that (like the jarg and php filters) that 
# will work on inbound comments. If a universal blog registration system ever comes to the fore (with a 
# useable API) I will most likely employ that method as well.
#
# bdkr@highsidecafe.com
###########################################################################################################
# Some of the vars we will be using with this script. Plug in the appropriate info here.
define('DB_HOST', 'localhost');
define('DB_USER_NAME', 'user name here');
define('DB_PASSWD', 'password here');
define('DB', 'db name here');
define('AUTHOR_TABLE', 'b2comments');
define('AUTHOR_FIELD', 'comment_author');
define('URL_TABLE', 'b2comments');
define('URL_FIELD', 'comment_author_url');
define('AUTHOR_FILE', 'comment_spam_terms.txt');
define('URL_FILE', 'comment_spam_urls.txt');
define('ADMIN_EMAIL', 'your email address here');
define('EMAIL_SUBJECT', 'Your spam cleaning script');


###########################################################################################################
/*  			      NO EDITING BEYOND THIS POINT  						*/
###########################################################################################################
# Setup our data structures. 
$connection=new connection;
$connection->db=DB;
$authQuery=new query;
$authQuery->conn_id=&$connection->conn_id;
$urlQuery=new query;
$urlQuery->conn_id=&$connection->conn_id;
$counter=0;


# Now connect to the database
$connection->conn_id=@mysql_connect(DB_HOST, DB_USER_NAME, DB_PASSWD);
if(!$connection->conn_id)
	{
	mail(ADMIN_EMAIL, EMAIL_SUBJECT, 'The script failed to connect to the database.');
	exit;
	}
# And select the database we are working against
else
	{
	if(mysql_select_db($connection->db, $connection->conn_id)!=true)
		{
		$connection->error=mysql_error($connection->conn_id);
		mail(ADMIN_EMAIL, EMAIL_SUBJECT, $connection->error);
		exit;
		}
	}

	

# Let's first open the files with our search terms.
$author_terms=@file(AUTHOR_FILE);
$url_terms=@file(URL_FILE);
$auth_size=count($author_terms);
$url_size=count($url_terms);


# Let's check that we actually got arrays from those files.
if( (!$author_terms)||(!$url_terms)  ) 	// && (is_array($author_terms)) )
	{ 
	mail(ADMIN_EMAIL, 'Your spam cleaning script', 'It appears that we failed to open one or both of the terms files'); 
        mysql_close($connection->conn_id);
	exit;
	}

	
# Iterate over the lists and delete the junk using the above queries. Authors first.
for($counter=0; $counter<$auth_size; ++$counter)
	{
	# Clean up author term a little first
	$author_terms[$counter]=trim($author_terms[$counter]);
	str_replace("\n", '', $author_terms[$counter]);
	if(empty($author_terms[$counter]))
		{ continue; } 
	$SQL='DELETE FROM '.AUTHOR_TABLE.' WHERE LOCATE(\''.$author_terms[$counter].'\', '.AUTHOR_FIELD.') != 0';
	
	# Now search and delete it from the table
	$authQuery->res_id=mysql_query($SQL, $connection->conn_id);
	if(!$authQuery->res_id)
		{
		$authQuery->error=mysql_error($authQuery->conn_id);
		mail(ADMIN_EMAIL, EMAIL_SUBJECT, $authQuery->error);
		exit;
		}
	}

	
# Now urls	
for($counter=0; $counter<$url_size; ++$counter)
	{
	# Clean up author term a little first
	$url_terms[$counter]=trim($url_terms[$counter]);
	str_replace("\n", '', $url_terms[$counter]);
	if(empty($url_terms[$counter]))
		{ continue; } 
	$SQL='DELETE FROM '.URL_TABLE.' WHERE LOCATE(\''.$url_terms[$counter].'\', '.URL_FIELD.') != 0';
	
	# Now search and delete it from the table
	$urlQuery->res_id=mysql_query($SQL, $connection->conn_id);
	if(!$urlQuery->res_id)
		{
		$urlQuery->error=mysql_error($urlQuery->conn_id);
		mail(ADMIN_EMAIL, EMAIL_SUBJECT, $urlQuery->error);
		exit;
		}
	}	


# That should be all. Close up shop and email the boss.
mysql_close($connection->conn_id);
mail(ADMIN_EMAIL, EMAIL_SUBJECT, 'Execution of the spam clean up file ran with success.');



###########################################################################################################
# Some data structures

class connection
	{
	var $conn_id;
	var $error;
	var $db;
	}
	
class query
	{
	var $conn_id;
	var $res_id;
	var $error;		
	}
	
class tracking
	{
	var $search_term;
	var $deleted_rows;	
	var $date;		
	}