How To Manage Fail2ban Using Perl Script On Remote Servers and A Control Mysql Database
Earlier I posted how to use a text file created by a script to similarly manage the rogue IPs that Fail2ban bans. The problem with using scripts and crontab is that it is not reporting in real-time.
The method here works in real-time because Fail2ban reports directly to the MySQL on the control server.
On each remote you’ll want to setup a reverse SSH tunnel to the mysql database. (how to is beyond the scope of this post) Here is the command in rc.local
su username -c 'autossh -N -f -M 10001 -L 3309:127.0.0.1:3306 username@controlserver -p 11001' &
On the Control Server create a table according to your logic requirements. This table works with the perl script below. Consider in advance how you want to handle duplicates! The code below does not allow duplicates or removes them.
+-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | ip | varchar(20) | NO | MUL | NULL | | | octet1 | int(3) | YES | | NULL | | | octet2 | int(3) | YES | | NULL | | | octet3 | int(3) | YES | | NULL | | | octet4 | int(3) | YES | | NULL | | | ipname | varchar(255) | YES | | NULL | | | name_change | int(11) | YES | | NULL | | | dangerlevel | int(1) | YES | | NULL | | | cc | varchar(2) | YES | | NULL | | | source | varchar(25) | YES | | NULL | | | banaction | varchar(25) | YES | | NULL | | | counter | int(11) | YES | | NULL | | | bantime | timestamp | NO | | 0000-00-00 00:00:00 | | | last_seen | timestamp | NO | | 0000-00-00 00:00:00 | | | first_seen | timestamp | NO | | CURRENT_TIMESTAMP | |
Add the script call to your /etc/fail2ban/action.d/iptables-allports.conf (according to which actions you use)
actionban = iptables -I fail2ban-<name> 1 -s <ip> -j DROP
/usr/bin/perl /etc/fail2ban/fail2sql.pl <name> <protocol> <port> <ip>
And finally this is the perl script. Open Source – No warranty! Use freely but at your peril!
use strict;
use warnings;
no warnings 'uninitialized';
## set to 0 for production
my $testing = 1;
## set to 1 for production
my $execute = 0;
## set to 0 for production
my $verbose = 1;
use Scalar::MoreUtils qw(empty);
use Socket;
use IP::Country::Fast;
use Try::Tiny;
use Sys::Hostname;
my $dnshost = hostname;
if (empty($dnshost)) {
print "Cant proceed without hostname\n";
exit 0;
}
print "dnshost $dnshost\n" if ( $verbose );
my $mysqlport = "3309"; ## for reverse tunnel
if ( $dnshost =~ m{control}i ) {
$mysqlport = "3306"; # control server only
}
print "mysqlport $mysqlport\n" if ( $verbose );
if ( $verbose ) {
use Term::ANSIColor;
}
use Log::Handler;
my $log = Log::Handler->new();
$log->add(
file => {
filename => "/var/log/fail2sql.log",
message_layout => "%T %L %s %l %m",
mode => "append",
maxlevel => "debug",
minlevel => "emerg",
}
);
use DBI;
my $iptable = "iptable";
my $database = "foo";
my $host = "127.0.0.1";
my $user = "username";
my $pw = "password";
my $dsn = "dbi:mysql:$database:$host:$mysqlport";
my $dbh = DBI->connect(
$dsn,
$user,
$pw,
{
PrintError => 0,
PrintWarn => 1,
RaiseError => 1,
AutoCommit => 1,
}
) or die "Connection Error: $DBI::errstr\n";
my $cc;
my @martians;
## default to 5 if they got caught by Fail2ban
my $dangerlevel = 5;
my $banaction = 'default';
my ( $OcTeT1, $OcTeT2, $OcTeT3, $OcTeT4);
## shared by subs
my $sth_shared;
my $found = 0;
my $dnsname;
my $ipname;
my $namechange = 0;
my $ipname_from_db;
my $protocol;
my $port;
my $ip;
if ( $testing ) {
$banaction = 'testing';
$protocol = 'tcp';
$port = '9999';
$ip = '99.99.99.99';
} else {
## input from fail2ban action (eg. iptables-allports.conf)
## <name> <protocol> <port> <ip>
( $banaction, $protocol, $port, $ip ) = @ARGV;
}
( $OcTeT1, $OcTeT2, $OcTeT3, $OcTeT4 ) = $ip =~ m{(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})};
&getINETinfo;
my $sql_f2b = "SELECT * FROM $database.$iptable WHERE ip = '$ip' ORDER BY counter DESC";
$sth_shared = $dbh->prepare($sql_f2b);
$sth_shared->execute();
my $ROWS = ( $sth_shared->rows );
print "$ROWS\n" if ( $verbose );
if ( $ROWS < 1 ) {
######################################
### we have never seen this guy before
######################################
&firstTIMEer;
} else {
#################################
#### he is already in iptable ###
#################################
&oldFREINDS;
}
$log->debug("FINISH DBI & disconnect");
$sth_shared->finish();
SILENT:
$dbh->disconnect();
exit 0;
################################ SUBS ##################################
sub getINETinfo {
undef($dnsname);
$dnsname = gethostbyaddr( inet_aton($ip), AF_INET );
my $reg = IP::Country::Fast->new();
undef($cc);
$cc = $reg->inet_atocc("$ip");
$cc = lc $cc;
## rules ###################################
if ($dnsname =~ m{tor}i ) { $dangerlevel = 3 }
#if ($cc =~ m{tr|fr|is|cn|ru|ua|ro|bg|pl|ma|af|tn|ir|eh|mr|ye|tj|iq|jo|so|az|ne|dz|ly|uz|pk|sn|tm|sy|ml|bd|om|kw|gn|al|sd|bf|kz|td|bn|vn|ve|th|ph|ng}i ) { $dangerlevel = 5 }
####################################################################
$log->debug("INETinfo $ip >> $dnsname << $cc ");
}
sub firstTIMEer {
if ( $execute ) {
## should there be duplicates? ##
$dbh->do( "INSERT IGNORE INTO $database.$iptable (ip,octet1,octet2,octet3,octet4,ipname,dangerlevel,cc,source,banaction,counter,bantime,last_seen) VALUES ('$ip','$OcTeT1','$OcTeT2','$OcTeT3','$OcTeT4','$dnsname','$dangerlevel','$cc','$dnshost','$banaction','1',now(),now())" );
$log->debug("Insert DB >>> $ip < > $dnsname < > $dangerlevel < > $cc <<< ");
}
}
sub oldFREINDS {
$sth_shared->execute or die "Connection Error: $DBI::errstr\n";
my $flag = 0;
$log->debug("Seen before >>> $ip <<<");
my @row_array_shared;
while ( @row_array_shared = $sth_shared->fetchrow_array ) {
my $id = "$row_array_shared[0]";
undef($ipname_from_db);
$ipname_from_db = "$row_array_shared[6]";
$namechange = "$row_array_shared[7]";
my $ExistingDangerLevel = "$row_array_shared[8]";
$cc = "$row_array_shared[9]";
my $exitingbadbanaction = "$row_array_shared[11]";
my $counter = "$row_array_shared[12]";
my $exitingbadlast_seen = "$row_array_shared[14]";
if ( $flag > 0 ) {
if ( $execute ) {
## remove duplicate ips here ##
$dbh->do( "DELETE FROM $database.$iptable WHERE id = '$id'" );
}
$log->info("SEEN >>> $ip <<< DUPLICATE ");
print colored ['yellow on_red'], ">>> $ip <<< DUPLICATE ", "\n" if ( $verbose );
} else {
## some logic optional ##
if (empty($exitingbadbanaction)) { $exitingbadbanaction = 'default' }
if (empty($counter)) { $counter = 2 } else { $counter = ( $counter + ( $sth_shared->rows ) ) }
if ( $ipname_from_db && $dnsname ) {
if ( $ipname_from_db !~ m{$dnsname} ) {
$namechange = ( $namechange + 1 );
$dangerlevel = 5;
$banaction = 'spoofedbot';
}
}
if (empty($ipname_from_db)) { $dangerlevel = 5 }
if ( $ExistingDangerLevel > $dangerlevel ) { $dangerlevel = $ExistingDangerLevel }
if (empty($banaction)) {
$banaction = 'default';
}
##########################
## double sure we don't create duplicates
if ( $flag == 0 ) {
if ( $execute ) {
## if banaction causes an update fail on duplicates
try {
$dbh->do( "UPDATE $database.$iptable SET
ipname = '$ipname_from_db',
name_change = '$namechange',
dangerlevel = '$dangerlevel',
cc = '$cc',
source = '$dnshost',
banaction = '$banaction',
counter = '$counter',
last_seen = now()
WHERE ip = '$ip'");
} catch {
$dbh->do( "UPDATE $database.$iptable SET
ipname = '$ipname_from_db',
name_change = '$namechange',
dangerlevel = '$dangerlevel',
cc = '$cc',
source = '$dnshost',
counter = '$counter',
last_seen = now()
WHERE ip = '$ip'");
};
}
$log->debug("UPDATE >>> $ip <<< $banaction ");
print ">>> $ip <<< $banaction :: $ipname_from_db\n" if ( $verbose );
$flag++;
}
}
}
}