WordPress comment spam – clearing out the stuff..

Last updated on September 9th, 2013 at 01:54 pm

Firstly – this “fix” will not work half as well if you did NOT install Askimet and configure it properly – you *did* do that – right? If not – do go it now… PLEASE… you’ll thank yourself…

So – to the meat and potatoes of it – and this is fair warning – I will by typing “frankly” and without much of a filter in place.. this is going to be blunt people…

So the basic problem is spammers who use your wordpress installation to generate links to their websites, or send you comments in the hope that you’ll buy their fake V1agra or C1al1s pills… or whatever else they’re trying to sell you – or – persuade you to visit their site for… the end result is that your database fills up with junk and I recently got a notice on a HOSTING CUSTOMER’S SITE – that their database was approaching the limit for their DB Usage – that’s impossible I think… oh no… they had some idiot college kid install WordPress and do a half-baked job of securing it… sorry – I take that back – they idiot didn’t do a DAR$NED THING to secure it… which is why you need a professional web developer instead of an idiot college kid (book smarts isn’t worth sh1t when you get your site hack – trust me – I know a lot of web hosting customers who thought it was, and eventually came to admit that it wasn’t)… but I digress…

The problem is:

1. you get comment spam <<-- ANNOYING


2. these spammers use your resources – eventually this can result in your website costing you more money, or shutting down if it is “over quota” <<-- SERIOUSLY - this can cost you MONEY!!


3. these spammy comments actually make your site look like a pile of poo to Google <<-- MOST IMPORTANT

So how do you clean up the mess?!?

Well if you didn’t install / SETUP the Askimet plugin – you have to go and delete those comments manually – or using phpMyAdmin, scan the comments table and delete the comments using SQL.. that is a REAL pain in the rear – and even when you are done doing that – you’ll end up with the wp_commentmeta table with a ton of orphaned records and that’s still a mess… you STILL need to use this script I am going to give you…

So here is how the script works…

Firstly – you want to save this as a PHP script on your site – in the WordPress installation folder – it will need to access the “wp_load.php” as an “REQUIRE” – ie, it cannot work unless you alter the script significantly, so put it in your wordpress folder – either the root folder of the site, or the /wordpress/ or /blog/ folder – where-ever you installed wordpress.

Next – call the script from your browser:

http://www.yourdomainwhateverthatis.com/yourcleanupscript.php

That’s it – the script will do the following:

1. work out what your domain is from the $_SERVER’HTTP_HOST’] variable
2. require ‘wp_load.php’ so it can obtain the table prefix – incase you used something other than the default ‘wp_’
3. delete comments from the wp_comments table where the askimet plugin or you determined them to be ‘spam’
4. delete orphaned wp_commentmeta records (those where the comments got deleted manually, or by the previous SQL delete command)
5. delete askimet commentmeta records
6. finally – work out what tables are not optimized – and optimize them <<-- assuming your wp admin user is a DBA on the database

and without further ado.. here is the script – use it at your own risk and if you mash up your database – go to the backup you made before you tried to clean up the mess… oh… you didn’t make one..?!? ALWAYS MAKE A BACKUP BEFORE RUNNING SQL UPDATES ON YOUR DATABASE – ALWAYS!!!!

<?php
echo "Date:".date("Y/m/d H:i:s").'<br>';
// Connect to MySQL - use the wordpress built-in function - it is easier
// and it gives us access to $wpdb array - so we can grab the table prefix

require_once("./wp-load.php");

//whatever the domain is - we can get it from the server (don't access it using a domain alias of course)
$domain = str_replace('www.','',strtolower($_SERVER['HTTP_HOST']));
echo '<P>'.$domain.'';
$prefix = $wpdb->prefix;
echo ' - PREFIX='.$prefix.'</P>';

$sql1="DELETE from ".$prefix."comments WHERE comment_approved = 'spam';";
$sql2="DELETE FROM ".$prefix."commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM ".$prefix."comments);";
$sql3="DELETE FROM ".$prefix."commentmeta WHERE meta_key LIKE '%akismet%';";

// do the work
$sql = $sql1;
echo 'About to run sql ... ['.$sql.']<br>';
$result = mysql_query($sql) or die(mysql_error());
echo '['.$result.']<br>';

$sql = $sql2;
echo 'About to run sql ... ['.$sql.']<br>';
$result = mysql_query($sql) or die(mysql_error());
echo '['.$result.']<br>';

$sql = $sql3;

echo 'About to run sql ... ['.$sql.']<br>';
$result = mysql_query($sql) or die(mysql_error());
echo '['.$result.']<br>';


$sql = mysql_query('SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400');
echo 'About to optimize tables ... ['.$sql.']<br>';

while($row = mysql_fetch_assoc($sql)) {
  mysql_query('OPTIMIZE TABLE ' . $row['Name']);
}
echo '['.'DONE'.']<br>';

echo '<P>We are done - exiting...</P>';
echo '<P><a href="http://'.$_SERVER['HTTP_HOST'].'/">Check the site now...</a></P>'
?>

Thank me for putting this together with a coffee via: