Mediawiki SPAMMERS die!

Here is my possibly very dodgy Python script for checking if Mediawiki usernames are banned/non-existent in an Nginx log file and then blocking the associated IP with iptables (if it’s not blocked already). Only 700,000 odd lines to process …

# Regex module.
import re
# Mysql module.
import mysql.connector
# Iptables module.
import iptc

# Nginx log-file variable.
infile = r"/var/log/nginx.log"

# Connection to Mediawiki database.
dbconnection = mysql.connector.connect(user='mysql', password='yourpassword', database='your_mediawiki_database')

# Non-interactive Mysql cmd line to pipe commands through.
dbcursor = dbconnection.cursor()

# Blocklist Mysql query.
ipblocks_query = ("select ipb_address from ipblocks where ipb_address=%s")
# User Mysql query.
user_query = ("select user_name from user where user_name=%s")

table = iptc.Table(iptc.Table.FILTER)
rule = iptc.Rule()
chain = iptc.Chain(iptc.Table(iptc.Table.FILTER), "INPUT")

# Open log-file.
with open(infile) as f:
 f = f.readlines()

# Iterate through each line in log-file.
for line in f:
 # Select lines which contain an IP address at line start AND user talk page reference.
 if re.findall("^\d+\.\d+\.\d+\.\d+", line) and re.findall("User:\w+", line):
 found = 0
 # Replace line with IP address and user reference only.
 line = re.findall("^\d+\.\d+\.\d+.\d+|User:\w+", line)
 # Check if ip address is already blocked by iptables.

for chain in table.chains:
 for rule in chain.rules:
 if re.sub("/255.255.255.255", "", rule.src) == line[0]:
 found = 1
 break
 if found == 0:
 line[1] = re.sub("User:", "", line[1])
 line[1] = re.sub("_"," ", line[1])
 # Query user against ipblocks table.
 dbcursor.execute(ipblocks_query, (line[1],))
 print (line[1])
 row = dbcursor.fetchone()
 # If user exists in ipblocks table.
 if row is not None:
 print ("Is banned!")
 # If not found add to iptables.
 rule.in_interface = "eth0"
 rule.src = line[0]
 t = rule.create_target("DROP")
 chain = iptc.Chain(iptc.Table(iptc.Table.FILTER), "INPUT")
 chain.insert_rule(rule)
 print ("Blocking IP address with iptables")
 print ("-")
 # Check if user exists in user table.
 else:
 dbcursor.execute(user_query, (line[1],))
 row = dbcursor.fetchone()
 if row is not None:
 print ("Is not banned!")
 print ("-")
 # If user doesn't exist block IP with iptables..
 else:
 print ("Does not exist!")
 rule.in_interface = "eth0"
 rule.src = line[0]
 t = rule.create_target("DROP")

chain = iptc.Chain(iptc.Table(iptc.Table.FILTER), "INPUT")
 chain.insert_rule(rule)
 print ("Blocking IP address with iptables")
 print ("-")

BTW, the indentation is completely screwed there, sorry …

Leave a Reply