Jul 30 2007 Programming

Used to run these scripts to modify the wordpress database.
I don't run them anymore but might modify and do so again in the future.  

This first script locates a spam post if it contains the characters 'http'. A bOt does not spam without posting those chars, it caught 100%. Then it modifies author, author email, and author url to the ip posted from. Last is setting post_content to a nice self promoting string.

#!/usr/bin/perl

# eric dziewa july 2007

use warnings;
use strict;
use DBI();

my $database = "dbname";
my $host = "dbhost";
my $user = "dbuser";
my $password = "dbpassword";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", "$user", "$password", {'RaiseError' => 1});

my ($c_id, $c_auth, $c_auth_mail, $c_auth_url, $c_auth_ip, $content);
my (@com_id, @c_auth, @c_auth_mail, @c_auth_url, @c_auth_ip, @content);

my $result = $dbh->prepare("select comment_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_content from wp_comments where comment_content regexp 'http.{1}'");
$result->execute();

while ( my @row = $result->fetchrow_array ) {
    ### populate our arrays
    ($c_id, $c_auth, $c_auth_mail, $c_auth_url, $c_auth_ip, $content) = @row;
    push(@com_id, $c_id);
    push(@c_auth, $c_auth);
    push(@c_auth_mail, $c_auth_mail);
    push(@c_auth_url, $c_auth_url);
    push(@c_auth_ip, $c_auth_ip);
    push(@content, $content);
}
$result->finish();

### populate some replacement strings
my $subject = "Spam from: ";
my $author; # ip?
my $author_email; # again?
my $author_url; # oh man
my $text = 'ArmOrbOt V.1 patrols this site. -spam removed- -ip exposed-
        "My bOt is better than your bot"

        Have a nice day.';

if (defined $com_id[0]) {
    
    for ( my $i = 0; $i <= $#com_id; $i++ ) {
    ### taint check
    ($c_auth[$i] = $c_auth[$i]) =~ s/([';])/\\$1/g;
    ($content[$i] = $content[$i]) =~ s/([';])/\\$1/g;

        my $author_payload = $dbh->prepare(
        "update wp_comments set comment_author = replace(comment_author, '$c_auth[$i]', '$c_auth_ip[$i]')"
        );
        $author_payload->execute();

        my $mail_payload = $dbh->prepare(
        "update wp_comments set comment_author_email = replace(comment_author_email, '$c_auth_mail[$i]', '$c_auth_ip[$i]')"
        );
        $mail_payload->execute();

        my $author_url_payload = $dbh->prepare(
        "update wp_comments set comment_author_url = replace(comment_author_url, '$c_auth_url[$i]', 'http://$c_auth_ip[$i]')"
        );
        $author_url_payload->execute();

        my $content_payload = $dbh->prepare(
        "update wp_comments set comment_content = replace(comment_content, '$content[$i]', '$text')"
        );
        $content_payload->execute();
    }
}

$dbh->disconnect();


That ran every 15 minutes. The next script ran hourly. It removes the post from display, and writes a file of all ips that have spammed us.


#!/usr/bin/perl

# eric dziewa july 2007

use warnings;
use strict;
use DBI();

my $database = "dbname";
my $host = "dbhost";
my $user = "dbuser";
my $password = "dbpassword";

my $month = `date +%Y-%m`;
chomp $month;

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", "$user", "$password", {'RaiseError' => 1});

# find our armorbot strings
my $result = $dbh->prepare("select comment_ID, comment_author_IP from wp_comments where comment_content regexp 'ArmOrbOt V\.1 patrols this site\. -spam removed- -ip exposed-.*'");

$result->execute();

my ( @comment_id, @author_ip, %stats );
my ( $comment_id, $author_ip );

while ( my @row = $result->fetchrow_array ) { # populate variables with the results
    ($comment_id, $author_ip ) = @row;
    push(@comment_id, $comment_id);
    push(@author_ip, $author_ip);
    $stats{$author_ip}++;
}

$result->finish();

if (defined $comment_id[0]) {
    for ( my $i = 0; $i <= $#comment_id; $i++ ) {
        my $spam_comment_eradicator = $dbh->prepare(
        "update wp_comments set comment_approved = 'spam' where comment_ID = '$comment_id[$i]'");
        ### delete from wp_comments where comment_ID = '$comment_id[$i]'
        $spam_comment_eradicator->execute();
    }
}

$dbh->disconnect();

no warnings; # lose the isn't numeric warning
my @sorted_stats = sort { $a <=> $b } keys %stats;
use warnings; # and back

my $outfile = '/absolutepath/spammers.txt';
open( FH, ">", $outfile ) or die "couldn't open $outfile\n$!\n";

select FH;

print "# IPs that have posted spam to http://eric.dziewa.com\n\n";
foreach ( @sorted_stats ) {
    print " $_ \n";
}
print "\n# Last run on $month\n# $#comment_id total\n";

close FH;


Here is the → list

The next version will delete the comment and all foreign keys outright.

   
Comments
No comments.
Comments for this entry available via RSS.
Comment Area
Your Name
Your Email (will not be published)
Your Website
Your Comment
Profanity is Prohibited
eric.dziewa.com is running WordPress.
WhiteSpace theme designed by E. Dziewa.
All content © E. Dziewa.
Thanks for stopping by.