Blog Posts

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:

INSERT IGNORE INTO AuthFail (IP, DT) VALUES 
(INET_ATON('114.4.137.34'), '2016-08-17 17:03:15'), 
(INET_ATON('216.12.41.72'), '2016-08-17 17:03:44'), 
(INET_ATON('80.90.163.52'), '2016-08-17 17:04:01');

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:

Format: address expiration date rule

155.133.82.77   1472524501.32   1700
94.97.246.22    1472524502.12   1701
61.163.231.229  1472525101.89   1702

Where the expiration date is expressed as seconds since the Unix epoch of 01/01/1970.

The blockedHosts file is simply a list of IP addresses:

41.160.96.90
45.32.42.56
61.163.231.229

The last step after updating the database and building the blacklist and blockedHosts files is updating the firewall with this command:

/Applications/Server.app/Contents/ServerRoot/usr/libexec/afctl -f A typical log file looks like this:

2016/08/29 21:10:01 INFO> CPU.pm:167 - CPU load: 1.98 <= 8.00
2016/08/29 21:10:01 INFO> banIP.pl:175 - Processing file: /var/log/system.log, fileType: system
2016/08/29 21:10:02 INFO> banIP.pl:264 - Selected 41727 records from CustomVisuals.AuthFail
2016/08/29 21:10:02 INFO> File.pm:110 - fileRead: /var/db/af/whitelist
2016/08/29 21:10:02 INFO> File.pm:110 - fileRead: /var/log/system.log
2016/08/29 21:10:02 INFO> banIP.pl:288 - Processing 8338 lines
2016/08/29 21:10:02 INFO> banIP.pl:291 - | IP Address   | YYYY-MM-DD HH:MN:SS |
2016/08/29 21:10:02 INFO> banIP.pl:350 - | 120.24.76.13 | 2016-08-29 21:06:27 |
2016/08/29 21:10:02 INFO> banIP.pl:350 - | 120.24.76.13 | 2016-08-29 21:06:58 |
2016/08/29 21:10:02 INFO> banIP.pl:350 - | 120.24.76.13 | 2016-08-29 21:07:08 |
2016/08/29 21:10:02 INFO> banIP.pl:350 - | 120.24.76.13 | 2016-08-29 21:07:39 |
2016/08/29 21:10:02 INFO> banIP.pl:391 - Deleted 1 old record from CustomVisuals.AuthFail
2016/08/29 21:10:02 INFO> banIP.pl:409 - Banning 120.24.76.13    for 08:30 HH:MM (count: 102)
2016/08/29 21:10:02 INFO> File.pm:110 - fileRead: /var/db/af/blacklist
2016/08/29 21:10:02 INFO> banIP.pl:430 - Reordering rules from 1700 to 1724
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 221.229.172.110: 08/29/16 21:02:51
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 155.133.82.77  : 08/29/16 21:35:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 94.97.246.22   : 08/29/16 21:35:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 61.163.231.229 : 08/29/16 21:45:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 217.92.236.149 : 08/29/16 21:50:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 120.210.207.250: 08/29/16 22:00:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 117.218.172.216: 08/29/16 22:10:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 187.17.19.36   : 08/29/16 22:50:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 99.12.224.73   : 08/29/16 22:55:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 222.186.34.214 : 08/29/16 23:00:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 45.32.42.56    : 08/29/16 23:35:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 122.116.53.44  : 08/30/16 00:50:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 89.248.163.3   : 08/30/16 01:10:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 120.150.181.13 : 08/30/16 01:30:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 148.243.43.140 : 08/30/16 02:30:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 41.160.96.90   : 08/30/16 03:35:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 80.178.204.121 : 08/30/16 03:35:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 89.248.172.149 : 08/30/16 03:35:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 120.24.76.13   : 08/30/16 05:40:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 203.4.172.162  : 08/30/16 06:00:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 185.125.4.137  : 08/30/16 06:20:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 155.133.38.225 : 08/30/16 11:35:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 91.224.160.184 : 08/30/16 12:20:01
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 190.210.189.236: 08/30/16 12:50:02
2016/08/29 21:10:02 INFO> banIP.pl:439 - Expiration DT for 91.224.160.106 : 08/30/16 12:55:02
2016/08/29 21:10:02 INFO> File.pm:264 - fileWrite: /var/db/af/blacklist
2016/08/29 21:10:02 INFO> File.pm:264 - fileWrite: /var/db/af/blockedHosts
2016/08/29 21:10:02 INFO> banIP.pl:481 - Deleted 0 old records

And that pretty much wraps up this discussion.

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