[Scoop-help] stopping comment spam

[Kelly Black]

Hello,

I have a perl script that I run periodically that goes through a list of
"bad" users and changes all of their comments. I will paste it below (I
assume the mailing list defangs attachments.) It should be straightforward
to adapt this in a block, but I just run it from the command line.  The idea
is that you keep a file that lists all of the user names of spammers (here
called bad.txt) ex:
houykdd
lostun
R3X

Then I run the script:
./clean.pl < bad.txt

Sincerely,
Kel

clean.pl:
#!/usr/bin/perl

use DBI;
use strict;


my $dbtype = 'mysql';
my $dbname = 'db name here';
my $dbhost = 'db host here';
my $username  = 'user name here';
my $password  = 'users passwd here';
my $data_source = "DBI:$dbtype:dbname=$dbname;host=$dbhost";
my $dbh = DBI->connect($data_source, $username, $password) ||
                die "  Can't connect to database! $@\n";

my $query;
my $output;
my $num;
my $user;
my $uid;
my $email;

while(<>) {
   chop;
   $user = $_;
   # uid | nickname

   $query = "select uid,realemail from users where nickname = " .
         $dbh->quote($user);
   $output = $dbh->prepare($query);
   $num = $output->execute;
   ($uid,$email) = $output->fetchrow();
   $output->finish();

   print "Looking for User: $user ($uid,$email)\n";

   $query = 'select * from comments where uid = ' .
       $dbh->quote($uid);
   $output = $dbh->prepare($query);
   $num = $output->execute;
   for(my $lupe=0;$lupe<$num;++$lupe){
       my $row = $output->fetchrow_hashref;
       my $inner;
       my $val;
       #foreach $inner (keys %{$row}) {
       #   print $inner,"  ",$$row{$inner},"\n";
       #}

   my $change = 'update comments set comment = ' .
      $dbh->quote(
      'This post automatically marked as comment spam and changed
accordingly <br />') .
      ' where sid = ' .
      $dbh->quote($$row{'sid'}) .
      ' and cid = ' . $dbh->quote($$row{'cid'});
   #print $change,"\n";
   my $changeit = $dbh->prepare($change);
   $changeit->execute;
   $changeit->finish();


   }
   $output->finish;
}




On 7/27/06, kpaul mallasch <kpaul at mallasch.com> wrote:
>
> in raw SQL you can, i don't think there's a block or op that does it,
> though. would be nice.
>
> -kpaul
>
> On Thu, 27 Jul 2006, Chris Schults wrote:
>
> >> Can someone advise me how to deal with comment spam?
> >
> > I'd be interested in this as well. One helpful feature would be the
> ability
> > to delete all comments from a single user at one time. Is this already
> > possible?
> >
> > Chris
> >
> > --------------------------
> >
> > Chris Schults
> > Web Production Manager
> > Grist Magazine
> > 710 Second Avenue, Suite 860
> > Seattle, WA  98104
> > Phone: 206-876-2020, ext. 204
> > Fax: 253-423-6487
> > <http://www.grist.org>
> >
> > To sign up for Grist by email, the world's top environmental news served
> up
> > with a sense of humor, click here <http://www.grist.org/signup/> or send
> a
> > blank email message to <daily-grist-subscribe at lists.grist.org>
> >
> > _______________________________________________
> > Scoop-help mailing list
> > Scoop-help at lists.kuro5hin.org
> > http://lists.kuro5hin.org/mailman/listinfo/scoop-help
> >
> _______________________________________________
> Scoop-help mailing list
> Scoop-help at lists.kuro5hin.org
> http://lists.kuro5hin.org/mailman/listinfo/scoop-help
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.kuro5hin.org/pipermail/scoop-help/attachments/20060727/5fa840f3/attachment.html