Firewall Ban

Use this forum if you have problems with a hMailServer script, such as hMailServer WebAdmin or code in an event handler.
hMailserver-User
Normal user
Normal user
Posts: 33
Joined: 2015-04-25 08:49

Re: Firewall Ban

Post by hMailserver-User » 2019-11-23 15:02

palinka wrote:
2019-11-23 14:34
I noticed there's a space at the beginning:

Code: Select all

VALUES (' 2019-11-23 08:29:31',
The script reads the date and time from the firewall log. So the formatting problem could be coming from the firewall log and not necessarily from powershell. What are the date and time formats in the firewall log (localized in Germany)?
I am from Austria - my windows default settings for time, ...:
Image

Here are some lines from my firewall log:

Code: Select all

2019-11-23 12:53:23 DROP TCP 193.56.28.101 000.000.000.000 59653 25 48 S 4232364007 0 8192 - - - RECEIVE
2019-11-23 12:56:17 DROP TCP 193.56.28.101 000.000.000.000 56217 25 52 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:56:20 DROP TCP 193.56.28.101 000.000.000.000 56217 25 52 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:56:26 DROP TCP 193.56.28.101 000.000.000.000 56217 25 48 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:57:01 DROP TCP 185.234.219.102 000.000.000.000 57140 25 52 S 404735548 0 8192 - - - RECEIVE
2019-11-23 12:57:04 DROP TCP 185.234.219.102 000.000.000.000 57140 25 52 S 404735548 0 8192 - - - RECEIVE
In the log there is no space in the beginning

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-23 16:22

hMailserver-User wrote:
2019-11-23 15:02
Here are some lines from my firewall log:

Code: Select all

2019-11-23 12:53:23 DROP TCP 193.56.28.101 000.000.000.000 59653 25 48 S 4232364007 0 8192 - - - RECEIVE
2019-11-23 12:56:17 DROP TCP 193.56.28.101 000.000.000.000 56217 25 52 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:56:20 DROP TCP 193.56.28.101 000.000.000.000 56217 25 52 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:56:26 DROP TCP 193.56.28.101 000.000.000.000 56217 25 48 S 3342926694 0 8192 - - - RECEIVE
2019-11-23 12:57:01 DROP TCP 185.234.219.102 000.000.000.000 57140 25 52 S 404735548 0 8192 - - - RECEIVE
2019-11-23 12:57:04 DROP TCP 185.234.219.102 000.000.000.000 57140 25 52 S 404735548 0 8192 - - - RECEIVE
In the log there is no space in the beginning
Here is the entirety of the firewall parsing. Is it possible that you introduced a space by accident?

Code: Select all

#	Get firewall logs - https://github.com/zarabelin/Get-WindowsFirewallLogs/blob/master/Get-WindowsFirewallLog.ps1
$LSRegex = "($LANSubnet\.\d{1,3})|(158\.201\.243\.\d{1,3})"
$MinuteSpan = 5 # Should match interval of scheduled task
$EndTime = $QueryTime
$StartTime = ([datetime]::parseexact($QueryTime, 'yyyy-MM-dd HH:mm:00', $Null ) - (New-TimeSpan -Minutes $MinuteSpan)).ToString("HH:mm:ss")
$DateEnd = $QueryTime
$DateStart = ([datetime]::parseexact($QueryTime, 'yyyy-MM-dd HH:mm:00', $Null ) - (New-TimeSpan -Minutes $MinuteSpan)).ToString("yyyy-MM-dd")

$FirewallLogObjects = import-csv -Path $FirewallLog -Delimiter " " -Header Date, Time, Action, Protocol, SourceIP, `
    DestinationIP, SourcePort, DestinationPort, Size, tcpflags, tcpsyn, tcpack, tcpwin, icmptype, icmpcode, info, path | `
    Where-Object {$_.date -match "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]"}
$FirewallLogObjects = $FirewallLogObjects | Where-Object {$_.Date -ge $DateStart -and $_.Date -lt $DateEnd}
$FirewallLogObjects = $FirewallLogObjects | Where-Object {$_.Time -ge $StartTime -and $_.Time -lt $EndTime}

$FirewallLogObjects | foreach-object {
	if ($_.DestinationPort -match $MailPorts) {
		if ($_.SourceIP -notmatch $LSRegex){
			$IP = ($_.SourceIP).trim()
			$DateTime = (($_.Date).trim()+" "+($_.Time).trim())
			$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('$DateTime', '$IP')"
			MySQLQuery $Query
		}
	}
}
Maybe you accidentally added a space here:

$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('?SPACE?$DateTime', '$IP')"

The date time format in the log is the same. I don't think its a formatting error.

hMailserver-User
Normal user
Normal user
Posts: 33
Joined: 2015-04-25 08:49

Re: Firewall Ban

Post by hMailserver-User » 2019-11-23 16:47

palinka wrote:
2019-11-23 16:22
Here is the entirety of the firewall parsing. Is it possible that you introduced a space by accident?

Code: Select all

#	Get firewall logs - https://github.com/zarabelin/Get-WindowsFirewallLogs/blob/master/Get-WindowsFirewallLog.ps1
$LSRegex = "($LANSubnet\.\d{1,3})|(158\.201\.243\.\d{1,3})"
$MinuteSpan = 5 # Should match interval of scheduled task
$EndTime = $QueryTime
$StartTime = ([datetime]::parseexact($QueryTime, 'yyyy-MM-dd HH:mm:00', $Null ) - (New-TimeSpan -Minutes $MinuteSpan)).ToString("HH:mm:ss")
$DateEnd = $QueryTime
$DateStart = ([datetime]::parseexact($QueryTime, 'yyyy-MM-dd HH:mm:00', $Null ) - (New-TimeSpan -Minutes $MinuteSpan)).ToString("yyyy-MM-dd")

$FirewallLogObjects = import-csv -Path $FirewallLog -Delimiter " " -Header Date, Time, Action, Protocol, SourceIP, `
    DestinationIP, SourcePort, DestinationPort, Size, tcpflags, tcpsyn, tcpack, tcpwin, icmptype, icmpcode, info, path | `
    Where-Object {$_.date -match "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]"}
$FirewallLogObjects = $FirewallLogObjects | Where-Object {$_.Date -ge $DateStart -and $_.Date -lt $DateEnd}
$FirewallLogObjects = $FirewallLogObjects | Where-Object {$_.Time -ge $StartTime -and $_.Time -lt $EndTime}

$FirewallLogObjects | foreach-object {
	if ($_.DestinationPort -match $MailPorts) {
		if ($_.SourceIP -notmatch $LSRegex){
			$IP = ($_.SourceIP).trim()
			$DateTime = (($_.Date).trim()+" "+($_.Time).trim())
			$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('$DateTime', '$IP')"
			MySQLQuery $Query
		}
	}
}
Maybe you accidentally added a space here:

$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('?SPACE?$DateTime', '$IP')"

The date time format in the log is the same. I don't think its a formatting error.
Checked it and the above line is like it should be:

Code: Select all

$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('$DateTime', '$IP')"
I have forgotten to say that i had that entry only one time. It doesn't happen every time a entry is made into hm_fwban_rh ...Sorry. :roll:

I have another error on this:

Code: Select all

19.11.23 12:55:01.49 : ERROR : Unable to run query : INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES ('2019-11-23 12:50:10', '193.56.28.101') 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Packets larger than max_allowed_packet are not allowed."[0]
But also only one time today.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-23 17:16

hMailserver-User wrote:
2019-11-23 16:47
I have another error on this:

Code: Select all

"Packets larger than max_allowed_packet are not allowed."[0]
I've never seen that before. I googled it. It seems strange. Apparently, the default max_allowed_packet is 16 MB. There is not a chance in hell that a single date and a single IP address approach a single kb, much less 16MB. I looked in my.ini and my own setting is max_allowed_packet = 1M.

I wouldn't worry about it. If it happens again look in MySQL my.ini and see if you have "max_allowed_packet". If not, add this and reboot:

max_allowed_packet = 1M

Or 16MB, which is supposedly the default. 1GB is the max.

hMailserver-User
Normal user
Normal user
Posts: 33
Joined: 2015-04-25 08:49

Re: Firewall Ban

Post by hMailserver-User » 2019-11-23 19:31

palinka wrote:
2019-11-23 17:16
I wouldn't worry about it. If it happens again look in MySQL my.ini and see if you have "max_allowed_packet". If not, add this and reboot:
max_allowed_packet = 1M
Or 16MB, which is supposedly the default. 1GB is the max.
My current my.ini has no entry for that so default will be used ...

Now i have a queston:

Code: Select all

	If (oClient.Port = 25) Then
		'  ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ)$"
		If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
	Else
		'  ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(AT|CZ|DE)$"
		If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
	End If
For my understanding the above setting should on port 25 allow the countries "US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ" and additional on every other port (993, 465, 587, ...) only the countries AT, CZ and DE are allowed. This will allow IMAP from mobile clients roaming there. correct?

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-23 20:00

hMailserver-User wrote:
2019-11-23 19:31
Now i have a queston:

Code: Select all

	If (oClient.Port = 25) Then
		'  ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ)$"
		If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
	Else
		'  ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(AT|CZ|DE)$"
		If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
	End If
For my understanding the above setting should on port 25 allow the countries "US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ" and additional on every other port (993, 465, 587, ...) only the countries AT, CZ and DE are allowed. This will allow IMAP from mobile clients roaming there. correct?
Yes, exactly.

One other thing I've found is that sometimes due to network or other errors, a geoip result cannot be found, which makes the result NULL. Since NULL does not match any of your allowed country codes, it will be rejected. The potential for false positives on NULL value is high, and I prefer a NULL response just get past the filter - if its spam, its likely to get picked up on one of the other filters or by spamassassin. To allow a NULL response, just add a pipe at the end of your string base like this:

...CZ|DE|)$

Since there is nothing after the pipe, NULL will pass because NULL = nothing. :D

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-23 23:02

I just added this auto expiration to my powershell:

Code: Select all

#	Automatic expiration from firewall - Reason: "One Hit Wonders"
#	Release all IPs that never returned after specified number of days 
$Days = "30" 	# <-- Number of days for automatic expiry                   
$Query = "
	SELECT id, ipaddress
	FROM hm_fwban 
	WHERE hm_fwban.ipaddress NOT IN 
	(
		SELECT ipaddress 
		FROM hm_fwban_rh
	) 
	AND timestamp < NOW() - INTERVAL $Days DAY
	AND flag IS NULL
	ORDER BY timestamp DESC
"
MySQLQuery $Query | foreach {
	$ID = $_.id
	$IP = $_.ipaddress
	& netsh advfirewall firewall delete rule name=`"$IP`"
	$Query = "UPDATE hm_fwban SET flag=1 WHERE id='$ID'"
	MySQLQuery $Query
}
Now that I have several months and 13k firewall rules - and a way to better analyze who gets blocked, I see that about 55% of my bans have never returned, so there's no point in keeping them.

Delete all firewall rules for IPs that have never appeared in the firewall log after N days. I chose 30 days and this will delete 5,352 firewall rules.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-24 00:52

hMailserver-User wrote:
2019-11-23 01:11
Its me again :-)

I am still on my "old" version which is working so far as expected. Before i start from scratch: there must be a problem with releasing / unban IPs via webif.
The following ip is released / marked as safe.
https://www.bilder-upload.eu/bild-0ecb0 ... 9.png.html
Afair marking as safe doesnt automatically removed it from the firewall. Releasing did it.
But the entry comes back. Even if i delete it manually from the windows firewall - the entry comes back again.
How can i track that down?

Hope you understand what i mean :oops:
I think I figured out why marking SAFE didn't remove the firewall rule. I changed the order of operation so processing SAFE comes first. I'm going to experiment a little and see if it works, then I'll update GitHub.

hMailserver-User
Normal user
Normal user
Posts: 33
Joined: 2015-04-25 08:49

Re: Firewall Ban

Post by hMailserver-User » 2019-11-24 01:03

palinka wrote:
2019-11-24 00:52
I think I figured out why marking SAFE didn't remove the firewall rule. I changed the order of operation so processing SAFE comes first. I'm going to experiment a little and see if it works, then I'll update GitHub.
Thank you!

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-24 01:08

hMailserver-User wrote:
2019-11-24 01:03
Thank you!
Funny thing is the only IP I ever marked safe was the one for hmailserver.com. Something Martin did once caused the helo to change temporarily to the name of his VM, which caused my hmailserver to trigger a ban. :mrgreen:

hMailserver-User
Normal user
Normal user
Posts: 33
Joined: 2015-04-25 08:49

Re: Firewall Ban

Post by hMailserver-User » 2019-11-24 10:07

palinka wrote:
2019-10-31 10:58
nitro wrote:
2019-10-31 10:28
You have to be careful with the lords of Microsoft.
It can be a false positive in, FQDN as HELO.

Code: Select all

AM5EUR02FT035.mail.protection.outlook.com
EUR04-DB3-obe.outbound.protection.outlook.com
EUR03-VE1-obe.outbound.protection.outlook.com
I discovered that yesterday too. I added outbound.protection.outlook.com$ to the list of "known false positives" so they skip the test.
you should add this to the version on github too :wink:

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-26 00:27

hMailserver-User wrote:
2019-11-24 10:07
you should add this to the version on github too :wink:
That's really for individuals to set up. Here's how I did it:

Code: Select all

	'	Run on PTR-Record
	If PTR_Record <> "" Then
		'   Exclude certain false positives
		strRegEx = "sendgrid|facebook\.com$|outbound\.protection\.outlook\.com$"
		If Lookup(strRegEx, PTR_Record) Then Exit Sub
		'   Search for dynamic looking PTR
		strRegEx = 	"(.*(((?:[0]{0,2})" & a(0) & "|(?:[0]{0,2})" & a(1) & "|(?:[0]{0,2})" & a(2) & "|(?:[0]{0,2})" & a(3) & ")(?:.+)){3}" &_
					"((?:[0]{0,2})" & a(0) & "|(?:[0]{0,2})" & a(1) & "|(?:[0]{0,2})" & a(2) & "|(?:[0]{0,2})" & a(3) & ").+)$"
		If (oClient.Port = 25) Then
		etc........
Also, I think I've found the practical limit of the number of firewall rules. Its something more than 10,000 rules. I started getting database connection errors that I could not pin down and I think they're related to the firewall not responding / not responding in time. Anyway, that caused me to rethink the individual rule per IP. I've changed it to consolidate all rules for a single day into one rule with multiple remote addresses. I'm testing it now. I consolidated 14k rules into about 300. New rules are added normally, then the next day they get consolidated into a single rule. I also figured out a way to update those consolidated rules in case you release, reban manually add or mark safe an IP. :D

So far, no MySQL connection errors, so I think its working. In a couple of days I'll post it to GitHub with instructions.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-26 20:54

I just uploaded ^this^ to GitHub. I haven't had any errors at all in 2 days, so I guess its good to go.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-27 17:23

I woke up this morning and nothing was working. OH NOES!!! :lol:

I had to log in to my server from a real keyboard and monitor. In my installation, as I was messing with GitHub yesterday, I changed my db password to "supersecretpassword" instead of the real password. Therefore, the query failed for rule consolidation. Therefore the output of yesterday's IPs was blank. Therefore the firewall rule created had NOTHING as remoteaddress. Therefore, I created a firewall rule blocking all local and all remote IPs. :mrgreen:

So... I added a test to make sure that never happens again. If blank then stop. This is updated on GitHub.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-11-27 18:23

New GitHub update: Removed deduplicator from hmsFirewallBan.ps1 and created new standalone deduplicator that also looks for orphaned rules.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2019-12-05 23:05

hMailserver-User wrote:
2019-11-24 10:07
you should add this to the version on github too :wink:
Everything working ok?

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-01-14 03:51

Updated search page so if you click on "HELO", a popup window shows details including PTR. The PTR record is derived dynamically from PHP "gethostbyaddr", so no new db column is required.

Demo: http://hmsfirewallbandemo.ddns.net/

Files: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

adrianmihai83
New user
New user
Posts: 26
Joined: 2018-01-26 17:19

Re: Firewall Ban

Post by adrianmihai83 » 2020-01-18 23:44

Just a little inconsistency:

in hmsFirewallBan.ps1 you create table hm_fwban and in EventHandlers.vbs you add to table hm_FWBan

Edit: also, there is no field countrycode

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-01-18 23:59

Countrycode is a remnant. That should be removed, I guess... :oops:

Post your errors here and I'll walk through the fixes. At one point, I thought I had it all fixed, but I keep changing my own installation. :D

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-01-19 00:07

I just looked on GitHub.

Code: Select all

'	Function FWBan - http://hmailserver.com/forum/viewtopic.php?f=9&t=34082
Function FWBan(sIPAddress, sReason, sHELO)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
   Set Json = New VbsJson
   On Error Resume Next
   Set oXML = CreateObject ("Msxml2.XMLHTTP.3.0")
   oXML.Open "GET", "http://ip-api.com/json/" & sIPAddress, False
   oXML.Send
   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
   On Error Goto 0

   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country,helo,flag) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("countryCode") & "','" & oGeoip("country") & "','" & sHELO & "','4');"
   Call oDB.ExecuteSQL(strSQL)
End Function
Yes, you are 100% correct about country code - NOT present in hmsFirewallBan.ps1 table create. The powershell is correct. In eventhandlers.vbs, change "strSQL" above to:

Code: Select all

   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,country,helo,flag) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("country") & "','" & sHELO & "','4');"
Also, you're right about hm_fwban vs hm_FWBan, although I don't think it makes any difference on windows, which generally doesn't care about capital vs lowercase letters. I will change it, though.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-01-21 00:14

Big time changes.

* Added PTR as a database column.
* Added column "rulename" to keep track of which firewall rules contain which IPs. This is important because...
* … Added a method of preventing consolidated rules from containing more than 400 IPs, which could cause trouble with firewall creation, among other things, on very busy servers.
* Removed all references to Powershell NetFirewallRule cmdlet for compatibility reasons - the Firewall Ban project should work on older windows systems now. Now firewall is controlled by Netsh.

Files: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-02-01 15:25

Funny helo by script kiddie trying to send spam or guess a password.
Screenshot_20200201-082308_Brave.jpg
And of course there is no PTR....

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-02-02 04:14

I updated GitHub today. Before, I had a method of manually adding or releasing IP ranges but it was "fake", meaning it only worked for /24 CIDR ranges and ignored any previously added or released IPs. Banned IP ranges were added as firewall rules using x.x.x.0/24 remote IP. That meant that they could only be removed exactly the same way. Therefore there was no control over individual IPs within the range.

Now it works with real CIDR ranges for both adding and releasing IPs. You can only add or release /22 - /32 ranges - a maximum of 1024 IP addresses at a time.

For CIDR ban:
* Looks for previously banned entries within the range - ignores them because they're already banned
* Looks for released IPs within the range - updates them to banned and adds firewall rule
* Looks for IPs marked safe - ignores them (no change to safe status)
* All other IPs within the range (any not found in the database) get banned and adds firewall rules for them

For CIDR release:
* Looks for banned IPs within the range and releases them, removes firewall rules
* Ignores IPs within the range that are already released or marked safe
* No need to deal with IPs not found in database

When banning a range, IP entries in the database and the firewall are made individually. Therefore you can also release them individually if, for example, you learn one former spammer has turned his back on his evil ways. Or, you learn that you accidentally banned all of facebook's messaging servers - you can release them en masse.

Anyway, a useful feature, I think. Plus the entire operation occurs at the PHP admin, so no changes were required for the powershell part of things.

Files: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

Demo: http://hmsfirewallbandemo.ddns.net/

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-02-21 02:11

Latest updates:

* MSSQL support
* Added IP map (works great on mobile too!)
* Added new table for fast queries of certain firewall blocks data - old table queries become slow with several hundred thousand rows. I'm working on a solution to kill off that table altogether, but for now, the web admin is much speedier in general.
* Many bugfixes

Demo - http://hmsfirewallbandemo.ddns.net/

Files - https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-02-25 22:46

Screenshot_20200225-153541_Brave.jpg

I added these dial guages to the web admin. They show today's results for how many IPs were added, how many IPs were dropped at the firewall, and how many total connections were dropped at the firewall.

The yellow zone represents 75% - 100% of the highest number of hits in a single day, so it's subject to change over time.

The red zone is 100% - 120%. If you're in the red zone, you're breaking a daily record.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-03-12 02:51

Fixed an annoying but minor bug. First connection to MySQL often failed with this error:

Code: Select all

20/03/11 18:52:32.16 : ERROR : Unable to run query : SELECT ipaddress, id, DATE(timestamp) AS dateip FROM hm_fwban WHERE flag=5 
Exception calling "Open" with "0" argument(s): "Authentication to host '127.0.0.1' for user 'hmailserver' using method 'mysql_native_password' failed with message: Reading from the stream has failed."[0]
Sometimes the error has to do with connecting with anonymous password, which is obviously not the case - password is indeed required.

This is random and only affects the very first query when the script is called on its 5 minute interval. Fortunately, this query rarely produces results anyway, so it didn't affect me much. I finally found the solution here:

http://www.voidcn.com/article/p-phfoefri-bpr.html

What this guy described fits the symptoms perfectly. I hope to never see an error log again.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-03-12 12:33

Hit another milestone: single digit bans. :mrgreen:

Screenshot_20200312-062447_Brave.jpg
Oh, they'll be back with a vengeance soon enough. They come in waves that (I think) coincide with new bot viruses. But the overall trend is still downward.

One thing I've noticed, since I record the helo, is patterns in the helo. They come in waves too.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-04-04 03:21

Hit 20k firewall rules today. Another milestone. :mrgreen:

Technically, I hit 20k remote-IPs banned by this project.

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-04-26 14:22

Another milestone. ONE MILLION firewall drops. :mrgreen:

9,380 IPs attempted to connect but were dropped at the firewall a total of 1,010,440 times since July 17th, 2019

palinka
Senior user
Senior user
Posts: 2169
Joined: 2017-09-12 17:57

Re: Firewall Ban

Post by palinka » 2020-07-19 14:09

This project had a birthday a couple days ago. One year old and still going strong.

Ban Enforcement:
34,356 Total number of IPs banned
(40) Number of IPs released from firewall
--------
34,316 Number of IPs currently banned by firewall rule

20,811 (60.58%) IPs were banned but never returned to be dropped.

13,544 IPs (39.42%) attempted to connect but were dropped at the firewall a total of 1,335,919 times since July 17th, 2019

Most firewall drops: 45.82.153.131 from Russia dropped 58,261 times

Most days dropped: IP 139.162.99.243 denied access 1,798 times over 101 distinct days between October 13, 2019 and July 18, 2020. This only counts the days connections were attempted, NOT the timespan between the first and last connection attempt.

Top 5 spammer countries:
United States 5,484 hits 15.96%
Vietnam 5,139 hits 14.96%
Russia 2,859 hits 8.32%
Brazil 2,660 hits 7.74%
Egypt 1,178 hits 3.43%

I just realized i have no way to count the number of firewall rules. When an IP is banned, an individual rule is created. Then at the end of the day, the rules are consolidated into batches of 400 IPs per rule (very rare to have > 400 bans in a single day). Then at the end of each month, the rules are consolidated again into batches of 400 IPs per rule. I guess i end up with about ~15 rules per month on average.

I tried leaving individual IP rules but when it got ti be around 10k rules, wacky things started to happen. The consolidation works great. It immediately relieved a lot of stress from the firewall. Why batches of 400? It's not based on anything but guesses about windows defender firewall. I researched it but could not find anything conclusive. It seems to work very well. YMMV with larger batches.

Anyway, things are still going strong. One year old and already had its first steps. :mrgreen:

Post Reply