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.

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.

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: ./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.

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

Ban2SQL by Kotori [email protected]
Based off of Fail2SQL by Jordan Tomkinson [email protected]
Project Page: https://github.com/kotori/Ban2SQL