removeSpam2.pl

#!/usr/bin/perl --

# プロフィールの表のの中のURLのホスト名のIPアドレスが特定の値ならサスペンドします。

# usage:
# export PSQL="docker exec mastodon1_db_backend_1 psql -U user database"
# export RAILS_CONSOLE=""docker-compose run --rm web bundle exec rails console"
# perl removeSpam2.pl --userDomain=mastodon.social 185.117.119.170

use strict;
use warnings;
use utf8;
use Socket;
use IO::Select;
use Scalar::Util qw(openhandle);
use Data::Dump qw(dump);
use Getopt::Long;

# psql コマンドの起動
# ex: psql -U user database
# ex: docker exec mastodon1_db_backend_1 psql -U user database
my $psql = $ENV{PSQL} || "psql -U user database";

# rails consoceの起動
my $railsConsole = $ENV{RAILS_CONSOLE} || "docker-compose run --rm web bundle exec rails console";

my $userDomain = "";

GetOptions (
    "psql=s" => \$psql,
    "railsConsole=s" => \$railsConsole,
    "userDomain=s" => \$userDomain
) or die("Error in command line arguments\n");

my @badAddrs = @ARGV or die "usage: perl removeSpam2.pl aaa.bbb.ccc.ddd ..)";


##############################################################

my %hostMap;
my $bgMax = 255;
my $s = IO::Select->new();

sub handleFhs{
    my(@ready)=@_;

    local $/ = undef;

    for my $fh ( @ready ){
        openhandle($fh) or next;
        my $line = <$fh>;
        if( not defined $line ){
            $s->remove($fh);
            close $fh;
        }else{
            my $data = eval $line ;
            $@ and die "eval failed: $line $@\n";
            $hostMap{ $data->{host} }{result} = $data->{result};
        }
    }
}

sub bgWait{

    # wait while only handles is max count.
    my($onlyMax)=@_;

    for(;;){
        my $handleCount = 0 + $s->handles;
        last if not $handleCount;

        last if $onlyMax and $handleCount < $bgMax;

        my @ready;

        @ready = $s->can_read(10);
        if( @ready ){
            handleFhs(@ready);
        }else{
            warn "waiting resolver: $handleCount\n";
        }

        @ready = $s->has_exception(0);
        @ready and handleFhs(@ready);
    }
}

sub bgStart{
    my($list) = @_;

    my $total = 0+@$list;
    my $idx = 0;
    while(@$list){

        bgWait(1);

        ++$idx;
        my $host = shift @$list;

        print STDERR "$idx/$total $host        \r";

        my $sleep_count = 0;
        my $pid;
        my $fh;
        for(;;){
            $pid = open $fh, "-|";
            last if defined $pid;
            warn "cannot fork: $!";
            die "bailing out" if $sleep_count++ > 6;
            sleep 10;
            next;
        }

        if( $pid ){
            # I am parent.
            $s->add( $fh );
            next;
        }else{
            # I am child.
            my( $name,   $aliases,  $addrtype,  $length,  @addrs ) = gethostbyname($host);
            if(not @addrs ){
                my $data = {
                    host => $host,
                    result =>{
                        error => "gethostbyname failed($?)",
                    },
                };
                print dump($data),"\n";
            }else{
                my $data = {
                    host => $host,
                    result =>{
                        addrs => [ map{ inet_ntoa $_} @addrs ],
                    },
                };
                print dump($data),"\n";
            }
            exit;
        }
    }
}

# アカウントのfieldのvalueがhttpを含むものを抽出するSQLクエリ

my $accountWhere="jsonb_typeof(fields)='array'";

$userDomain and $accountWhere .= " and domain='$userDomain'";

my $query = <<"END";
SELECT a.id, a.username, a.domain, f.value
FROM (select * from accounts where $accountWhere) as a
JOIN jsonb_to_recordset(a.fields)  as f(name text,value text) ON true
where f.value like '\%http\%'
and a.suspended_at is null
END

$query =~ s/^\s*#.+/ /gm;      # '#'で始まる行はコメントアウト
$query =~ s/[\s\x0d\x0a]+/ /g; # 改行とインデントを空白1文字に変換

my $result = `$psql -c "$query;"`;

for( split /[\x0d\x0a]+/,$result){
    next if /^[-(]/; # skip header
    my($id,$username,$domain,$html)=split /\s*\|\s*/,$_;
    if(not $html){
        print "?? $_\n";
        next;
    }

    my $user = { id=>$id, username=>$username, domain=>$domain};
    while($html =~ m|\bhref="\w+://([^"/#:]+)|g ){
        my $host = $1;
        my $entry = $hostMap{ $host };
        $entry or $entry = $hostMap{ $host } = { users => [] };
        push @{ $entry->{users} }, $user;
    }
}

print "\nresolve ip address...\n";
bgStart( [ sort keys %hostMap ] );
bgWait();
print "\n\n";
my @badIds;
my @badInfo;
for my $host ( sort keys %hostMap ){

    my $entry = $hostMap{$host};

    my $users = $entry->{users};
    my $result = $entry->{result};
    if(not $result){
        print "$host: missing result.\n";
    }elsif( $result->{error}){
        print "$host: $result->{error}\n";
    } elsif( not $result->{addrs} ){
        warn "$host: missing addrs in result.";
    }else{
        for my $addr ( @{ $result->{addrs} } ){
            next if not grep{ $addr eq $_} @badAddrs;
            for my $user ( @$users ){
                push @badIds,$user->{id};
                warn "$host: $addr $user->{id} $user->{username} $user->{domain}\n";
            }
        }
    }
}

if(1){
    while(@badIds){
        # 100件ずつ処理する
        my $ids = join ',', splice @badIds,0,100;

        # rails console に送るコマンドはワンライナーにする必要がある
        # 随所にセミコロンが必要
        my $cmd = <<"END";
Account
    .where(id: [$ids])
    .where(suspended_at:nil)
    .map{ |account|
              SuspendAccountService.new.call(account, reserve_email: false)
              ;"#{account.created_at.in_time_zone('Asia/Tokyo')},#{account.username},#{account.domain}"
        }
END
        # ワンライナーを整形する
        $cmd =~ s/^\s*#.+/ /gm;      # '#'で始まる行はコメントアウト
        $cmd =~ s/[\s\x0d\x0a]+/ /g; # 改行とインデントを空白1文字に変換

        # ワンライナーをdockerコンテナのrails console に送る
        open(my $fh,"|-",$railsConsole) or die $!;
        print $fh $cmd;
        close($fh) or die $!;
    }
}