Ban2SQL

Ban2SQL is a Fail2Ban plugin for logging attacks in a MySQL database for easy report building, and mapping. This application makes use of the MaxMind GeoIP database for gathering geo data.

Requirements

There are a couple of requirements prior to running Ban2SQL. First is Fail2Ban, while its not an absolute requirement, it does automate the insertion of bans into the database. Ban2SQL was written in Perl, so there are a few modules you will need to install. File::Copy, Geo::IP::PurePerl, DBI, LWP::Simple, Archive::Extract, IO::Uncompress::Gunzip.

Installation

1. Create a MySQL database called ban2sql (this step isn't necessary if you are sharing a db)

   $ mysql -u'root' -p
   $ mysql> CREATE DATABASE `ban2sql`;

2. Create ban2sql MySQL user to access ban2sql database (needs SELECT, INSERT, UPDATE, DELETE)

   $ mysql -u'root' -p
   $ mysql> CREATE USER 'ban2sql_user'@'localhost' IDENTIFIED BY 'ban2sql_password';
   $ mysql> GRANT SELECT, INSERT, UPDATE, DELETE PRIVILEGES ON `ban2sql`.* to 'ban2sql_user'@'localhost';

3. Create table by piping base.sql into mysql (mysql -u'ban2sql_user' -p'ban2sql_password' ban2sql < sql/base.sql)

   $ mysql -u'ban2sql_user' -p'ban2sql_password' `ban2sql` < sql/base.sql

You can also populate your table with some sample data by piping data.sql into your new table.

   $ mysql -u'ban2sql_user' -p'ban2sql_password' `ban2sql` < sql/data.sql

4. Edit ban2sql.pl and change home path and sql login details at the top of the file.

5. Update Geo IP Database (./ban2sql.pl -u)

6. Tell fail2ban to call ban2sql by appending to actionban in your action script. Usually the default action is 'banaction = iptables-multiport'

Example for

/etc/fail2ban/action.d/iptables-multiport.conf

actionban = iptables -I fail2ban-<name> 1 -s <ip> -j DROP
            /etc/fail2ban/Ban2SQL/ban2sql.pl -i <name> <protocol> <port> <ip>

Usage

 Usage: ./ban2sql.pl <argument>
  -l  : List the last 50 Bans.
  -u  : Download the latest MaxMind GeoIP database.
  -i  : Insert a new record into the database.
  -d  : Remove a record from the database.
  -c  : Clear the database and start fresh.
  -h  : The help menu.

Notes

Incase its not immediately obvious, here is a breakdown of how the database is built. This might be handy incase you would like to tweak the application (add db rows, etc).

MySQL Database Row Chart

Row ID	Row Name	Row Meaning
1	name	Service being attacked (ssh, ftp, etc..)
2	protocol	Protocol this attack is taking place over
3	port	Port number this service attack is taking place on
4	ip	IP address of the attacker
5	count	Number of attempts this ip has made
6	longitude	Geolocational longitude of attacker
7	latitude	Geolocational latitude of attacker
8	country	Country this attacker originates from (2 letters)
9	geo	More specific regional information about this attacker
10	date_last_seen	Date/Time of latest ban
11	date_first_seen	Date/Time of first ban

Contact/Credits

Ban2SQL by Kotori kotori@greenskin.hopto.org
Based off of Fail2SQL by Jordan Tomkinson jordan@moodle.com
Project Page: https://github.com/kotori/Ban2SQL