Related Documents

Introduction

This series was created because we had a problem with a hacker that infiltrated our system and sent out emails in early 2015. After discussing the information with a couple colleagues and their experiences dealing with similar issues I was encouraged to share what I had created and why.

In this part of the series, I’ll provide some of the SQL code for selecting from and inserting into the database. As we process each file and identify attempted break-ins, we build up an SQL insert statement for the database. We use the multi-row insert form for MySQL to do this in a single step. MySQL allows the insert to look like this:

Recall that we are storing the integer equivalent of the dotted-quad IP addresses, so INET_ATON(‘114.4.137.34’) will actually store 1,912,899,874 (without the commas) in the IP field of the table.

Since we run this every 5 minutes for the system.log and every 30 minutes for each of the email, server-side SPAM logs and client-side SPAM logs, we’re running the program every few minutes. Our typical usage shows the vast majority of break-in attempts showing up in the system log, so we run that more often than the others.

We start off each run by selecting the existing records from the database and loading them into a Perl hash (aka associated array), which is a simple table where the IP and Datetime values are joined with commas, like so: '80.90.163.52,2016-08-17 17:04:01’. The database returns about 400,000 records per second from the following query:

SELECT INET_NTOA(IP) AS IP, DT
FROM CustomVisuals.AuthFail

The database typically has around 40,000 records in it, so retrieving the data from the database and converting it to a Perl hash happens in well under one second. In fact the steps listed below all take place in about one second:

Launch program

Check CPU load (program will pause if CPU load is excessive)

Connect to the database

Select 40,000 records from the database

Convert database records to Perl hash

Open/read/close 10,000 lines from log file

Compare contents from log file to database

Update database with new break-in attempts

Update blacklist file with IP addresses and time to block

Update blockedHosts file with IP addresses

Run the afctl command to update the firewall

Purge records older than 90 days from database

Disconnect from the database

Email log file

After updating the database with the new break-in attempts, we build the blacklist and blockedHosts file for the firewall. The blacklist file has a simple format:

Since I originally started writing Part I of this, I’ve incorporated a few other ideas, some useful, some just for my interest. I’ve expanded the AuthFail database to have two more fields, FileType and Line. Some of the attacks are more intense than others, with multiple attempts to break-in per second. With the original setup, those would be reduced to a single entry in the database. By adding the FileType and line number I can now identify every attempt, regardless of how aggressive it may be since it will occur on a different line number of the log file.

I’ve recently added a whois contact information in another table so I can have some information if I decide to send email notifications to the owners of the sites trying to break-in. This could be automated, but I don’t really expect that this would have any impact on deterring the break-ins.

Out of curiosity, I’ve added a table for keeping track of the country where each IP address is located. Here’s the top 10 tally of break-ins by country as of this writing:

US: 10467

SC: 5537

CN: 3836

NL: 3572

ID: 1809

PL: 1202

GB: 1175

KR: 988

FR: 883

CA: 882

Here’s a simple graph of the number of IPs banned over the last couple months. The information is provided over 5 minute intervals.