74 lines
2.0 KiB
Perl
74 lines
2.0 KiB
Perl
#!/usr/bin/perl
|
|
use strict;
|
|
use warnings;
|
|
use DBI;
|
|
use DateTime;
|
|
use Data::Printer;
|
|
use File::Slurp;
|
|
|
|
use Mojo::UserAgent;
|
|
|
|
# Connect to the database.
|
|
my $dbh = DBI->connect("DBI:MariaDB:database=kram;host=edna",
|
|
"steffen", "66WXRlvF0UUV",
|
|
{'RaiseError' => 1});
|
|
|
|
my $ua = Mojo::UserAgent->new();
|
|
|
|
my %knownips;
|
|
my %nolandips;
|
|
my $hundred = 0;
|
|
my $first = 0;
|
|
|
|
my $country = $dbh->selectcol_arrayref('SELECT country, cid FROM banip_country', {Columns=>[1, 2]});
|
|
my %coun = @$country;
|
|
|
|
$nolandips{$first} = ();
|
|
|
|
my $da = $dbh->selectall_arrayref('SELECT a.src FROM banip_attacker a WHERE cid IS NULL GROUP BY src' );
|
|
|
|
for my $d ( @$da ) {
|
|
#p @$d[0];
|
|
if ( $hundred == 100 ) {
|
|
$first ++;
|
|
$nolandips{$first} = ();
|
|
$hundred = 0;
|
|
}
|
|
|
|
$knownips{@$d[0]} = 1;
|
|
push @{$nolandips{$first}}, {"query"=> @$d[0], "fields"=> "country,countryCode,query", "lang"=> "de"};
|
|
$hundred ++;
|
|
}
|
|
p %nolandips;
|
|
|
|
# Land über IP von ip-api.com holen batch mit jeweils 100 ips
|
|
for my $ar ( keys %nolandips ) {
|
|
sleep(5);
|
|
my $req = $ua->post("http://ip-api.com/batch" => {Accept => '*/*'} => json => $nolandips{$ar} )->result->json;
|
|
# p $nolandips{$ar};
|
|
|
|
for my $query ( @$req ) {
|
|
my $cid;
|
|
# land in Datenbank bekannt cid zuordnen
|
|
if ( $coun{$query->{country}} ) {
|
|
$cid = $coun{$query->{country}};
|
|
} else {
|
|
# Land nicht bekannt eintragen und neue id zuordnen
|
|
$dbh->do(
|
|
'INSERT INTO banip_country (
|
|
country,
|
|
countryCode)
|
|
VALUES (
|
|
?,
|
|
?)', undef,
|
|
$query->{country},
|
|
$query->{countryCode},
|
|
);
|
|
$cid = $dbh->last_insert_id();
|
|
$coun{$query->{country}} = $cid;
|
|
}
|
|
|
|
$dbh->do('UPDATE banip_attacker set cid = ?, updated = updated WHERE src = ?', undef, $cid, $query->{query} );
|
|
}
|
|
}
|