fail2ban_banned_db

#!/usr/bin/env bash
# Source Code: Toulis Dev <https://github.com/ToulisDev/fail2ban-action-mssql>
# Puprpose:
#   - Store banned IP address in SQL db while it's banned by Fail2ban.
#   - Remove unbanned IP address from SQL db while it's unbanned by Fail2ban.
#   - Unban IP addresses which have column `remove=1`.

# Usage:
#
#   *) Store a banned IP address:
#
#       banned_db ban <ip> <ports> <protocol> <jail> <failures> [loglines]
#
#       - <ip>: One IP address each time.
#       - <ports>: Network ports. Multiple ports must be separated by comma.
#       - <protocol>: `tcp` or `udp`.
#       - <jail>: Fail2ban jail name.
#       - <failures>: number of times the failure occurred in the log file.
#       - [loglines]: matched log lines. OPTIONAL.
#
#   *) Remove an one or multiple unbanned IP addresses. Notes:
#
#       - it removes IP from all jails.
#       - multiple IP addresses must be separated by space.
#
#       banned_db unban <ip> [ip] [ip]
#
#   *) Cleanup a jail. When Fail2ban is stopping or restarting, `cleanup` will
#      be executed. Cleanup manually is supported too:
#
#       banned_db cleanup <jail>
#
#   *) Query SQL db and remove IP addresses which have `remove=1`.
#
#       banned_db unban_db
#
# Examples:
#
#   banned_db ban   192.168.0.1 110,143,993,995 tcp dovecot-iredmail 3
#   banned_db unban 192.168.0.1
#   banned_db cleanup dovecot-iredmail
#   banned_db unban_db

#
# Sample Fail2ban jail config file (/etc/fail2ban/jail.d/xx.local):
#
#   [jail-name]
#   ...
#   action = ...[your other actions here]...
#            banned_db[name=jail-name, port="80", protocol=tcp]
#
# WARNING: the name set in `banned_db[name=]` must be same as the jail name.
logger "Started fail2ban_banned_db bash script"
export PATH="/usr/bin:/usr/local/bin:$PATH"

export DB_NAME="fail2ban"
export DB_TABLE="dbo.banned"
export DB_USER="fail2ban"

# GeoIP
export CMD_GEOIPLOOKUP="$(which geoiplookup 2>/dev/null)"
export CMD_GEOIPLOOKUP6="$(which geoiplookup6 2>/dev/null)"

# SQLCmd
export sqlcmd="sudo /opt/mssql-tools/bin/sqlcmd"

# `dig`. Used to query reverse hostname.
export CMD_DIG="/usr/bin/dig"

export CMD_SQL="${sqlcmd} -S localhost -U fail2ban -P `sudo cat /root/.my.cnf-fail2ban | grep -Po 'password="\K[^"]*'` -Q "
export DB_TYPE="mssqld"
    


export _action="$1"

if [[ X"${_action}" == X"ban" ]]; then
    logger "Starting to store banned ip to SQL Server"
    _ip="${2}"
    _ports="${3}"
    _protocol="${4}"
    _jail="${5}"
    _failures="${6}"

    shift 6
    _rest="$@"
    if [[ X"${_rest}" != X'' ]]; then
        _restnew=`echo "${_rest}" |  tr "'" " "`

        # Avoid SQL injection.
        printf -v _loglines "${_restnew}"
    fi

    if [[ X"${_ip}" == X'' ]] || \
        [[ X"${_ports}" == X'' ]] || \
        [[ X"${_protocol}" == X'' ]] || \
        [[ X"${_jail}" == X'' ]]; then
        echo "IP, ports, protocol, or jail name is empty. Abort."
        exit 255
    fi

    _hostname="$(hostname)"

    # Lookup for country name.
    _country=''
    if echo ${_ip} | grep ':' &>/dev/null; then
        if [[ -x ${CMD_GEOIPLOOKUP6} ]]; then
            _country="$(${CMD_GEOIPLOOKUP6} ${_ip} | grep '^GeoIP Country Edition:' | awk -F': ' '{print $2}' | grep -iv 'not found')"
        fi
    else
        if [[ -x ${CMD_GEOIPLOOKUP} ]]; then
            _country="$(${CMD_GEOIPLOOKUP} ${_ip} | grep '^GeoIP Country Edition:' | awk -F': ' '{print $2}' | grep -iv 'not found')"
        fi
    fi

    # Lookup reverse DNS name.
    _rdns=''
    if [[ -x "${CMD_DIG}" ]]; then
        _rdns_orig="$(${CMD_DIG} +short +timeout=3 -x ${_ip} 2>/dev/null)"
        _rdns_strip="${_rdns_orig%\.}"
        printf -v _rdns "${_rdns_strip}"
    fi

    logger "Trying to pass the SQL Querry to SQL Server"
    if [ X"${DB_TYPE}" == X'mssqld' ]; then
        # MSSQL stores local time with time zone info, we expect UTC time.
        ${CMD_SQL} <<EOF "INSERT INTO ${DB_TABLE} (ip, ports, protocol, jail,hostname, country, rdns, timestamp,failures, loglines) VALUES ('${_ip}', '${_ports}', '${_protocol}', '${_jail}','${_hostname}', '${_country}', '${_rdns}', CURRENT_TIMESTAMP,'${_failures}', '${_loglines}');"
EOF
        logger "Passed SQL Querry to SQL Server"
    fi
elif [[ X"${_action}" == X"unban" ]]; then
    echo "Starting Unban"
    shift 1
    _ips="$@"
    if [[ X"${_ips}" == X'' ]]; then
        echo "No IP address(es) specified."
    else
        for _ip in ${_ips}; do
            ${CMD_SQL} <<EOF "DELETE FROM ${DB_TABLE} WHERE ip='${_ip}';"
EOF
        done

        [[ X"$?" == X'0' ]] && echo "Removed."
    fi

elif [[ X"${_action}" == X"cleanup" ]]; then
    _jail="$2"

    if [[ X"${_jail}" != X'' ]]; then
        ${CMD_SQL} <<EOF "DELETE FROM ${DB_TABLE} WHERE jail='${_jail}';"
EOF
    fi

    [[ X"$?" == X'0' ]] && echo "All IP addresses have been removed."
elif [[ X"${_action}" == X"unban_db" ]]; then
    # Call fail2ban-client to unban given IP address(es).
    tmp_file="$(mktemp)"

    # Exclude extra info on output, just leave jail/ip.
    (${CMD_SQL} <<EOF "SELECT jail, ip FROM ${DB_TABLE} WHERE remove=1;"
EOF
) | grep -Ev '(jail.*ip|\--|\(|^$)' | tr -d '|' >> ${tmp_file}

    while read jail ip; do
        # Avoid SQL injection: don't allow whitespace, ';', quotes in
        # jail name and IP address.
        if echo ${jail} | grep "[ ;\"\']" &>/dev/null; then
            echo "[WARNING] Invalid jail name: '${jail}'."
            continue
        fi

        if echo ${ip} | grep "[ ;\"\']" &>/dev/null; then
            echo "[WARNING] Invalid IP address: '${ip}'."
            continue
        fi

        # fail2ban-client returns number of processed rows on command line,
        # let's discard it to avoid noise/confusion.
        fail2ban-client set ${jail} unbanip ${ip} >/dev/null
        [[ X"$?" == X'0' ]] && echo "Unbanned ${ip} from jail [${jail}]."
    done < ${tmp_file}
    rm -f ${tmp_file} &>/dev/null
fi