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: 38
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: 4461
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: 38
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: 4461
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: 38
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: 4461
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: 4461
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: 4461
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: 38
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: 4461
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: 38
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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: 4461
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:

miniGranis
New user
New user
Posts: 3
Joined: 2020-12-14 09:44

Re: Firewall Ban

Post by miniGranis » 2020-12-16 09:35

Hello.
Newbie here just moved to HMS from Mdaemon, anyway I got your scripts all setup from https://github.com/palinkas-jo-reggelt/ ... rewall-Ban and been running for 12+ hours now, mysql db seems to fill with IPs, FW rules gets created aso.
However when using the webgui I get:

Code: Select all

Spammers around the world:
Cannot read property 'apply' of undefined×
I am far from a web-html-php-javascript guy but it seems to me that something within the ipmap.php is not working as it should.

Any ideas ?

miniGranis
New user
New user
Posts: 3
Joined: 2020-12-14 09:44

Re: Firewall Ban

Post by miniGranis » 2020-12-16 10:21

miniGranis wrote:
2020-12-16 09:35
Hello.
Newbie here just moved to HMS from Mdaemon, anyway I got your scripts all setup from https://github.com/palinkas-jo-reggelt/ ... rewall-Ban and been running for 12+ hours now, mysql db seems to fill with IPs, FW rules gets created aso.
However when using the webgui I get:

Code: Select all

Spammers around the world:
Cannot read property 'apply' of undefined×
I am far from a web-html-php-javascript guy but it seems to me that something within the ipmap.php is not working as it should.

Any ideas ?
Additional info, almost all other graphs and tables sees to be working.
2020-12-16_092010.jpg

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

Re: Firewall Ban

Post by palinka » 2020-12-16 14:25

miniGranis wrote:
2020-12-16 09:35
Hello.
Newbie here just moved to HMS from Mdaemon, anyway I got your scripts all setup from https://github.com/palinkas-jo-reggelt/ ... rewall-Ban and been running for 12+ hours now, mysql db seems to fill with IPs, FW rules gets created aso.
However when using the webgui I get:

Code: Select all

Spammers around the world:
Cannot read property 'apply' of undefined×
I am far from a web-html-php-javascript guy but it seems to me that something within the ipmap.php is not working as it should.

Any ideas ?
Something changed with Google maps and i haven't been able to fix it. I should have simply removed it from github.

In the meantime, you can comment out or delete the section in index.php dealing with the map.

miniGranis
New user
New user
Posts: 3
Joined: 2020-12-14 09:44

Re: Firewall Ban

Post by miniGranis » 2020-12-16 14:28

palinka wrote:
2020-12-16 14:25

In the meantime, you can comment out or delete the section in index.php dealing with the map.
Got it thanks.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2021-01-06 11:13

palinka wrote:
2020-12-16 14:25
miniGranis wrote:
2020-12-16 09:35
Hello.
Newbie here just moved to HMS from Mdaemon, anyway I got your scripts all setup from https://github.com/palinkas-jo-reggelt/ ... rewall-Ban and been running for 12+ hours now, mysql db seems to fill with IPs, FW rules gets created aso.
However when using the webgui I get:

Code: Select all

Spammers around the world:
Cannot read property 'apply' of undefined×
I am far from a web-html-php-javascript guy but it seems to me that something within the ipmap.php is not working as it should.

Any ideas ?
Something changed with Google maps and i haven't been able to fix it. I should have simply removed it from github.

In the meantime, you can comment out or delete the section in index.php dealing with the map.
Sir, I think you need to apply API key now for the map to load. Can you check this please.

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

Re: Firewall Ban

Post by palinka » 2021-01-06 18:14

gotspatel wrote:
2021-01-06 11:13
Sir, I think you need to apply API key now for the map to load. Can you check this please.
I purposely did not use anything that would require an api key.

Something changed with Google maps and I haven't been able to fix it. I should have simply removed it from github.

In the meantime, you can comment out or delete the section in index.php dealing with the map.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2021-03-22 07:45

Getting an Error
"ERROR" 8396 "2021-03-22 09:36:01.591" "Script Error: Source: Microsoft VBScript runtime error - Error: 800A000D - Description: Type mismatch: 'oGeoip' - Line: 138 Column: 3 - Code: (null)"

Code: Select all

'	Credit 99.9% to SorenR
Function AccRejDB(xPort, xEvent, xAccRej, xReason, xIPAddress, xHELO)
	Dim oApp : Set oApp = CreateObject("hMailServer.Application")
'    Find VbsJson.vbs here: https://github.com/eklam/VbsJson
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")  '<--- Original Code
   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/" & xIPAddress, False
   oXML.Send
   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
   On Error Goto 0
   Dim strSQL, strSQLDEL, oDB : Set oDB = GetDatabaseObject
  
  strSQL = "INSERT INTO hm_accrej (timestamp, port, event, accrej, reason, ipaddress, country, helo) VALUES   _
  
  (NOW(),'" & xPort & "','" & xEvent & "','" & xAccRej & "','" & xReason & "','" & xIPAddress & "','" & oGeoip("country") & "','" & xHELO & "');" <--- THE CULPRIT LINE: 138 (I HAVE CUT IN HALF FOR ILLUSTRATION ONLY
   
   Call oDB.ExecuteSQL(strSQL)
End Function

Any pointer. I have the database table and working fine.

POP LOG

Code: Select all

"POP3D"	8396	5390	"2021-03-22 09:35:50.158"	"192.168.175.251"	"RECEIVED: CAPA"
"POP3D"	8396	5390	"2021-03-22 09:35:50.158"	"192.168.175.251"	"SENT: +OK CAPA list follows[nl]UIDL[nl]TOP[nl]USER[nl]."
"POP3D"	4744	5390	"2021-03-22 09:35:50.173"	"192.168.175.251"	"RECEIVED: USER mirror@mydomain.com"
"POP3D"	4744	5390	"2021-03-22 09:35:50.173"	"192.168.175.251"	"SENT: +OK Send your password"
"POP3D"	8396	5390	"2021-03-22 09:35:50.173"	"192.168.175.251"	"RECEIVED: PASS ***"
"POP3D"	8396	5390	"2021-03-22 09:36:01.591"	"192.168.175.251"	"SENT: +OK Mailbox locked and ready"
"POP3D"	9916	5390	"2021-03-22 09:36:01.591"	"192.168.175.251"	"RECEIVED: LIST"
"POP3D"	9916	5390	"2021-03-22 09:36:01.591"	"192.168.175.251"	"SENT: +OK 655 messages (256888281 octets)"
"POP3D"	9916	5390	"2021-03-22 09:36:01.607"	"192.168.175.251"	"SENT: 1 19976[nl]2 5212[nl]3 5240[nl]4 5499[nl]5 5522[nl]6 5481[nl]7 5504[nl]8 5386[nl]9 5409[nl]10 2290[nl]11 5374[nl]12 5402[nl]13 5395[nl]14 5423[nl]15 19210[nl]16 5379[nl]17 5407[nl]18 5422[nl]19 5450[nl]20 5208[nl]21 5236[nl]22 11718[nl]23 5473[nl]24 5443[nl]25 5501[nl]26 5471[nl]27 5441[nl]28 5469[nl]29 5491[nl]30 5491[nl]31 5519[nl]32 5519[nl]33 5460[nl]34 5488[nl]35 5356[nl]36 5384[nl]37 18363[nl]38 18359[nl]39 5301[nl]40 5329[nl]41 5299[nl]42 5327[nl]43 13723[nl]44 23210[nl]45 5208[nl]46 5236[nl]47 5377[nl]48 5405[nl]49 13712[nl]50 18155[nl]51 19879[nl]52 5207[nl]53 5235[nl]54 3852[nl]55 3712[nl]56 5440[nl]57 5468[nl]58 7620[nl]59 5491[nl]60 5519[nl]61 5395[nl]62 5423[nl]63 4623[nl]64 4651[nl]65 14812[nl]66 5225[nl]67 5253[nl]68 8560[nl]69 7507[nl]70 7620[nl]71 5362[nl]72 5390[nl]73 14881[nl]74 20374[nl]75 8815[nl]76 5201[nl]77 5229[nl]78 4478[nl]79 4192[nl]80 5361[nl]81 5389[nl]82 5496[nl]83 5524[nl]84 5210[nl]85 5238[nl]86 807093[nl]87 1368819[nl]88 401709[nl]89 1101217[nl]90 4111923[nl]91 4111919[nl]92 142578[nl]93 28335[nl]94 14388517[nl]95 17600643[nl]96 5927[nl]97 23843[nl]98 20359[nl]99 20887[nl]100 4939[nl]101 4967[nl]102 832029[nl]103 38436[nl]104 31372[nl]105 22144[nl]106 3878[nl]107 5121[nl]108 5123[nl]109 5146[nl]110 5148[nl]111 7011[nl]112 3711[nl]113 5102[nl]114 5112[nl]115 21335[nl]116 21255[nl]117 49703[nl]118 6721[nl]119 2673[nl]120 37865[nl]121 5581[nl]122 1129928[nl]123 42571[nl]124 111645[nl]125 2828[nl]126 24765[nl]127 21236[nl]128 562294[nl]129 143905[nl]130 364353[nl]131 122390[nl]132 14163[nl]133 3733[nl]134 4986[nl]135 4993[nl]136 245163[nl]137 30542[nl]138 3910[nl]139 494292[nl]140 1620305[nl]141 3743[nl]142 4836[nl]143 4835[nl]144 907662[nl]145 6824[nl]146 32933[nl]147 5896[nl]148 4932[nl]149 4951[nl]150 5670[nl]151 6474[nl]152 13513[nl]153 4859[nl]154 4867[nl]155 6131[nl]156 852977[nl]157 4160120[nl]158 5068[nl]159 5087[nl]160 184344[nl]161 5897[nl]162 27002[nl]163 5926[nl]164 392307[nl]165 562633[nl]166 47986[nl]167 36852[nl]168 36499[nl]169 20822[nl]170 19314[nl]171 41707[nl]172 32587[nl]173 4842[nl]174 4851[nl]175 886908[nl]176 400675[nl]177 48808[nl]178 359166[nl]179 4996[nl]180 5021[nl]181 5364[nl]182 5392[nl]183 5461[nl]184 5489[nl]185 5492[nl]186 5520[nl]187 19712[nl]188 5202[nl]189 5230[nl]190 1005[nl]191 996[nl]192 998[nl]193 5308[nl]194 5336[nl]195 5308[nl]196 5336[nl]197 5322[nl]198 5350[nl]199 16364[nl]200 201922[nl]201 201950[nl]202 5210[nl]203 5238[nl]204 5928[nl]205 891195[nl]206 10861[nl]207 10878[nl]208 16349[nl]209 16364[nl]210 45385[nl]211 5960[nl]212 6041[nl]213 6069[nl]214 7170[nl]215 7198[nl]216 21224[nl]217 921857[nl]218 12486[nl]219 12514[nl]220 12332[nl]221 44554[nl]222 7204[nl]223 7232[nl]224 43281[nl]225 43293[nl]226 131154[nl]227 131182[nl]228 6842[nl]229 201187[nl]230 7004[nl]231 6225[nl]232 17620[nl]233 5958[nl]234 5989[nl]235 5366[nl]236 5394[nl]237 39829[nl]238 6045[nl]239 41969[nl]240 85570[nl]241 35946[nl]242 35974[nl]243 50308[nl]244 50336[nl]245 2660[nl]246 20065[nl]247 5916[nl]248 5809[nl]249 16978[nl]250 17035[nl]251 17006[nl]252 5212[nl]253 5240[nl]254 893898[nl]255 83302[nl]256 83330[nl]257 11975[nl]258 12003[nl]259 78343[nl]260 12003[nl]261 78366[nl]262 78366[nl]263 78342[nl]264 78365[nl]265 78365[nl]266 155638[nl]267 155655[nl]268 3522[nl]269 3190[nl]270 4687[nl]271 4715[nl]272 363024[nl]273 363052[nl]274 369533[nl]275 134495[nl]276 134523[nl]277 78135[nl]278 78163[nl]279 3628[nl]280 4552[nl]281 4580[nl]282 3794[nl]283 113550[nl]284 113578[nl]285 1062301[nl]286 6099[nl]287 5367[nl]288 5395[nl]289 125996[nl]290 126024[nl]291 134272[nl]292 131540[nl]293 131568[nl]294 11978[nl]295 12006[nl]296 5509[nl]297 5493[nl]298 5537[nl]299 5495[nl]300 5521[nl]301 5493[nl]302 5523[nl]303 5521[nl]304 5444[nl]305 5472[nl]306 5439[nl]307 5467[nl]308 4557[nl]309 4574[nl]310 4585[nl]311 4602[nl]312 5383[nl]313 5411[nl]314 215360[nl]315 4574[nl]316 215388[nl]317 215388[nl]318 4602[nl]319 5486[nl]320 5514[nl]321 16881[nl]322 5934[nl]323 5339[nl]324 5099[nl]325 5127[nl]326 5111[nl]327 5139[nl]328 4979[nl]329 5007[nl]330 5113[nl]331 5141[nl]332 66893[nl]333 66921[nl]334 153663[nl]335 153691[nl]336 42680[nl]337 217599[nl]338 217627[nl]339 5448[nl]340 5476[nl]341 5448[nl]342 5476[nl]343 6221[nl]344 5069[nl]345 5036[nl]346 5073[nl]347 5097[nl]348 5094[nl]349 5122[nl]350 5097[nl]351 5125[nl]352 5196[nl]353 5061[nl]354 5224[nl]355 5016[nl]356 5089[nl]357 5044[nl]358 5091[nl]359 5119[nl]360 5093[nl]361 5121[nl]362 5075[nl]363 5092[nl]364 5103[nl]365 5132[nl]366 5120[nl]367 5160[nl]368 5092[nl]369 5120[nl]370 5383[nl]371 5025[nl]372 5021[nl]373 5411[nl]374 5049[nl]375 135802[nl]376 135830[nl]377 117690[nl]378 117718[nl]379 15529[nl]380 5489[nl]381 5517[nl]382 11981[nl]383 12009[nl]384 5976[nl]385 5055[nl]386 5083[nl]387 608703[nl]388 5093[nl]389 5121[nl]390 250131[nl]391 250159[nl]392 5105[nl]393 5133[nl]394 618637[nl]395 5044[nl]396 5048[nl]397 5072[nl]398 5035[nl]399 5039[nl]400 5063[nl]401 5107[nl]402 5135[nl]403 191598[nl]404 191626[nl]405 5112[nl]406 5140[nl]407 239910[nl]408 2543[nl]409 1270479[nl]410 1270507[nl]411 37980[nl]412 585939[nl]413 585967[nl]414 8207739[nl]415 8207767[nl]416 5485[nl]417 5513[nl]418 8238114[nl]419 26735[nl]420 5668284[nl]421 5668312[nl]422 8217169[nl]423 8217197[nl]424 5699266[nl]425 6431[nl]426 5211[nl]427 5239[nl]428 20090[nl]429 19927[nl]430 944198[nl]431 83227[nl]432 83244[nl]433 5002[nl]434 4998[nl]435 5026[nl]436 93748[nl]437 18619[nl]438 18396[nl]439 1025616[nl]440 1025512[nl]441 3679968[nl]442 5958[nl]443 6955[nl]444 9171316[nl]445 93815[nl]446 24970[nl]447 18226[nl]448 5962[nl]449 5954[nl]450 7040[nl]451 86900[nl]452 6356[nl]453 879125[nl]454 83107[nl]455 83132[nl]456 6453[nl]457 325854[nl]458 325882[nl]459 7005[nl]460 10867[nl]461 10895[nl]462 10895[nl]463 429323[nl]464 429351[nl]465 429351[nl]466 75729[nl]467 6087[nl]468 41117[nl]469 110320[nl]470 110348[nl]471 6858[nl]472 1502224[nl]473 6120[nl]474 6113[nl]475 6083[nl]476 50107[nl]477 50135[nl]478 40919[nl]479 7088[nl]480 7112[nl]481 1477485[nl]482 30587[nl]483 30615[nl]484 5984[nl]485 5382[nl]486 5410[nl]487 108547[nl]488 108575[nl]489 4547[nl]490 4575[nl]491 138750[nl]492 464091[nl]493 477288[nl]494 748398[nl]495 748426[nl]496 26315[nl]497 31789[nl]498 48634[nl]499 48662[nl]500 18266[nl]501 3545[nl]502 3210[nl]503 4772[nl]504 4500[nl]505 5211[nl]506 5239[nl]507 83296[nl]508 83324[nl]509 155249[nl]510 155277[nl]511 5949[nl]512 5947[nl]513 5968[nl]514 909587[nl]515 13392[nl]516 13469[nl]517 13420[nl]518 2059940[nl]519 2059968[nl]520 9488[nl]521 9543[nl]522 9516[nl]523 16857[nl]524 7800[nl]525 153874[nl]526 5455[nl]527 5483[nl]528 4429012[nl]529 5473[nl]530 5501[nl]531 867281[nl]532 867309[nl]533 21625[nl]534 21653[nl]535 21601[nl]536 21653[nl]537 21629[nl]538 867288[nl]539 21629[nl]540 867316[nl]541 2447625[nl]542 2447653[nl]543 205569[nl]544 205597[nl]545 2638[nl]546 13494[nl]547 8112[nl]548 8173[nl]549 8140[nl]550 13476[nl]551 26483[nl]552 37448[nl]553 37476[nl]554 37476[nl]555 37476[nl]556 26356[nl]557 39895[nl]558 39923[nl]559 201710[nl]560 39923[nl]561 201738[nl]562 2226[nl]563 40085[nl]564 40113[nl]565 40113[nl]566 323814[nl]567 13477[nl]568 37886[nl]569 38437[nl]570 5472[nl]571 5500[nl]572 867305[nl]573 867333[nl]574 13494[nl]575 17092[nl]576 398[nl]577 48728[nl]578 13470[nl]579 5873[nl]580 6032[nl]581 5203[nl]582 5231[nl]583 5830[nl]584 945748[nl]585 82954[nl]586 82979[nl]587 18091[nl]588 676365[nl]589 676393[nl]590 6987[nl]591 453[nl]592 6862[nl]593 204580[nl]594 204608[nl]595 204608[nl]596 204608[nl]597 2444[nl]598 450[nl]599 74781[nl]600 74809[nl]601 10602[nl]602 271628[nl]603 5508[nl]604 5536[nl]605 5536[nl]606 11522[nl]607 11550[nl]608 300060[nl]609 300088[nl]610 26168[nl]611 26196[nl]612 6995[nl]613 8648[nl]614 8873[nl]615 2934[nl]616 6850[nl]617 38663[nl]618 38691[nl]619 5469[nl]620 5497[nl]621 8952[nl]622 867323[nl]623 867351[nl]624 5210[nl]625 5238[nl]626 936434[nl]627 5979[nl]628 80552[nl]629 2444011[nl]630 2444039[nl]631 2444601[nl]632 2444629[nl]633 2445206[nl]634 2445234[nl]635 2445331[nl]636 2445359[nl]637 1402391[nl]638 1402419[nl]639 24116[nl]640 5639847[nl]641 5639875[nl]642 2445300[nl]643 2445328[nl]644 2445807[nl]645 2445835[nl]646 2446181[nl]647 2446209[nl]648 2446231[nl]649 2446259[nl]650 2446435[nl]651 2446463[nl]652 3087465[nl]653 3087493[nl]654 6029[nl]655 931390[nl]."
"POP3D"	4744	5390	"2021-03-22 09:36:01.607"	"192.168.175.251"	"RECEIVED: TOP 1 1"
"POP3D"	4744	5390	"2021-03-22 09:36:01.607"	"192.168.175.251"	"SENT: +OK 19976 octets"
"POP3D"	4744	5390	"2021-03-22 09:36:01.607"	"192.168.175.251"	"SENT: [nl]."
"POP3D"	8396	5390	"2021-03-22 09:36:01.623"	"192.168.175.251"	"RECEIVED: TOP 2 1"
"POP3D"	8396	5390	"2021-03-22 09:36:01.623"	"192.168.175.251"	"SENT: +OK 5212 octets"
"POP3D"	8396	5390	"2021-03-22 09:36:01.623"	"192.168.175.251"	"SENT: [nl]."
"POP3D"	9916	5390	"2021-03-22 09:36:01.638"	"192.168.175.251"	"RECEIVED: TOP 3 1"
"POP3D"	9916	5390	"2021-03-22 09:36:01.638"	"192.168.175.251"	"SENT: +OK 5240 octets"
"POP3D"	9916	5390	"2021-03-22 09:36:01.638"	"192.168.175.251"	"SENT: [nl]."


User avatar
SorenR
Senior user
Senior user
Posts: 6315
Joined: 2006-08-21 15:38
Location: Denmark

Re: Firewall Ban

Post by SorenR » 2021-03-22 13:07

gotspatel wrote:
2021-03-22 07:45
Getting an Error
"ERROR" 8396 "2021-03-22 09:36:01.591" "Script Error: Source: Microsoft VBScript runtime error - Error: 800A000D - Description: Type mismatch: 'oGeoip' - Line: 138 Column: 3 - Code: (null)"
Probably because you are trying to decode an empty or invalid response in oXML.responseText ;-)

Perhaps switch these two lines and check if oXML.Status is "200" before decoding?

Code: Select all

   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2021-03-22 13:16

Thanks will check it out.

The error is random as I am already getting ONLOGIN entries in my database.

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

Re: Firewall Ban

Post by palinka » 2021-03-24 13:32

SorenR wrote:
2021-03-22 13:07
gotspatel wrote:
2021-03-22 07:45
Getting an Error
"ERROR" 8396 "2021-03-22 09:36:01.591" "Script Error: Source: Microsoft VBScript runtime error - Error: 800A000D - Description: Type mismatch: 'oGeoip' - Line: 138 Column: 3 - Code: (null)"
Probably because you are trying to decode an empty or invalid response in oXML.responseText ;-)

Perhaps switch these two lines and check if oXML.Status is "200" before decoding?

Code: Select all

   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
The other possibility could be rate limiting. But it would not be random. It would be on every call until the limit was removed.

User avatar
SorenR
Senior user
Senior user
Posts: 6315
Joined: 2006-08-21 15:38
Location: Denmark

Re: Firewall Ban

Post by SorenR » 2021-03-24 17:18

I have switched to a different GEO site... Requires a KEY but you can get a free one.

Code: Select all

    '
    '   Check that SUBMS and IMAPS is from "Rigsfællesskabet"/"Naalagaaffeqatigiit"/"Ríkisfelagsskapurin" = The Danish Realm.
    '   zz = N/A, dk = Denmark, gl = Greenland, fo = Faroe Islands
    '
    If oClient.Port > 25 Then
        On Error Resume Next
        Dim oGeoIP : Set oGeoIP = IPGeolocation(oClient.IPAddress, ReturnCode)
        On Error GoTo 0
        If ReturnCode = 200 Then
            If (InStr("|DK|GL|FO|", oGeoIP("country_code2")) = 0) Then
                strPort = Trim(Mid("SMTP IMAP SUBMSSUBM IMAPS", InStr("25   143  465  587  993  ", oClient.Port), 5))
                Result.Value = 1
                If AutoBan(oClient.IPAddress, "GEOBLOCK " & strPort & " - " & oClient.Username, 3, "d") Then
                    EventLogX.Write( LPad("GEOBlock   ", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & strPort )
                    EventLogX.Write( LPad("IP Location", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "country_code2   >" & oGeoIP("country_code2") & "<" )
                    EventLogX.Write( LPad("IP Location", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "country_name    >" & oGeoIP("country_name") & "<" )
                    EventLogX.Write( LPad("IP Location", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "organization    >" & oGeoIP("organization") & "<" )
                    EventLogX.Write( LPad("IP Location", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "isp             >" & oGeoIP("isp") & "<" )
                    EventLogX.Write( LPad("IP Location", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "connection_type >" & oGeoIP("connection_type") & "<" )
                End If
                Disconnect(oClient.IPAddress)
                Set oGeoIP = Nothing
                Set EventLogX = Nothing
                Exit Sub
            End If
        Else
            EventLogX.Write( LPad("GEOBlock FAIL", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & "ReturnCode = " & ReturnCode )
        End If
        Set oGeoIP = Nothing
    End If


Function IPGeolocation(strIP, ByRef ReturnCode)

    'curl 'https://api.ipgeolocation.io/ipgeo-bulk?include=hostname&ip=8.8.8.8&apiKey=API_KEY'
    '{
    '"ip":8.8.8.8
    '"hostname":dns.google
    '"............":..........
    '}

    On Error Resume Next
    Dim ReturnValue
    Dim JSON : Set JSON = New VbsJSON
    Dim oShell : Set oShell = CreateObject("WScript.Shell")
    Dim oExec : Set oExec = oShell.Exec("curl https://api.ipgeolocation.io/ipgeo?apiKey=" & APIKEY & "&ip=" & strIP)
    While oExec.Status = EXECRUN
        Wait(1)
    Wend
    Select Case oExec.Status
        Case EXECDONE
            ReturnCode = 200
            ReturnValue = oExec.StdOut.ReadAll()
        Case Else
            ReturnCode = 999
            ReturnValue = oExec.StdErr.ReadAll()
    End Select
    Set IPGeolocation = JSON.Decode(ReturnValue)
    If (ReturnCode <> 200 ) Then EventLog.Write( "<error> api.ipgeolocation.io lookup failed, error code """ & ReturnCode & """ on IP address " & strIP & ". Message: " & ReturnValue )
    Set oShell = Nothing
    Set oExec = Nothing
    Set JSON = Nothing
    On Error GoTo 0
End Function
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2021-03-24 17:23

Will check that out tomorrow.

Thanks

User avatar
SorenR
Senior user
Senior user
Posts: 6315
Joined: 2006-08-21 15:38
Location: Denmark

Re: Firewall Ban

Post by SorenR » 2021-03-24 17:33

palinka wrote:
2021-03-24 13:32
The other possibility could be rate limiting. But it would not be random. It would be on every call until the limit was removed.
Got a new toy! Off-Server check for SnowShoe / LashBack with AutoBan :shock:

Runs with cscript from a command box on the server OR if you change "localhost" to LAN name you can run it from your workstation 8)

IPADR is taken from this line:
"TCPIP" 3516 "2021-03-24 16:24:50.492" "TCP - 192.168.0.158 connected to 192.168.0.5:993."

You stop execution by Ctrl+C OR :!: disable logging in hMailAdmin :mrgreen:

Oh, you need to enable TCP/IP logging in hMailAdmin :!:

Code: Select all

Option Explicit

Const ADMIN = "Administrator"
Const PASSW = "Cosmic Secret"
Const SERVR = "localhost"
Const IPADR = "192.168.0.5"

Dim sLog, sIPAddress

Dim oApp : Set oApp = CreateObject("hMailServer.Application", SERVR)
Call oApp.Authenticate(ADMIN, PASSW)

oApp.Settings.Logging.Enabled = True
oApp.Settings.Logging.LogSMTP = True
oApp.Settings.Logging.EnableLiveLogging(True)

Do Until oApp.Settings.Logging.Enabled = False
    sLog = oApp.Settings.Logging.LiveLog
    If Len(sLog) > 0 Then 
        WScript.Echo sLog
        If GetIPAddress(sLog, sIPAddress) Then
            If IsSnowShoe(sIPAddress) Then
                If AutoBan(sIPAddress, "*SnowShoe*", 48, "h") Then Disconnect(sIPAddress)
            End If
            If IsLashBack(sIPAddress) Then
                If AutoBan(sIPAddress, "*LashBack*", 48, "h") Then Disconnect(sIPAddress)
            End If
        End If
    End If
    WScript.Sleep 1000
Loop

Set oApp = Nothing
Wscript.Quit 0

Function GetIPAddress(strSource, ByRef sIPAddress) : GetIPAddress = False
    WScript.Echo "GetIPAddress ..."
    Dim i, strRegEx, oMatch, oMatchCollection
    strRegEx = "(?:.+TCP - )(.+)(?: connected to " & IPADR & ".+)"
    Set oMatchCollection = oLookup(strRegEx, strSource, False)
    For Each oMatch In oMatchCollection
        If oMatch.SubMatches.Count > 0 Then
            GetIPAddress = True
            sIPAddress = oMatch.SubMatches(0)
            Exit For
        End If
    Next
    Set oMatchCollection = Nothing
End Function

Function IsSnowShoe(strIP) : IsSnowShoe = False
    WScript.Echo "IsSnowShoe ..."
    Dim a, strLookup
    a = Split(strIP, ".")
    With CreateObject("DNSLibrary.DNSResolver")
        strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".zen.spamhaus.org")
    End With
    If (InStr(1, strLookup, "127.0.0.3", 1) > 0) Then IsSnowShoe = True
End Function

Function IsLashBack(strIP) : IsLashBack = False
    WScript.Echo "IsLashBack ..."
    Dim a, strLookup
    a = Split(strIP, ".")
    With CreateObject("DNSLibrary.DNSResolver")
        strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".ubl.unsubscore.com")
    End With
    If (InStr(1, strLookup, "127.0.0.2", 1) > 0) Then IsLashBack = True
End Function

Function AutoBan(sIPAddress, sReason, iDuration, sType) : AutoBan = False
    WScript.Echo "AutoBan ..."
    '
    '   sType can be one of the following;
    '   "yyyy" Year, "m" Month, "d" Day, "h" Hour, "n" Minute, "s" Second
    '
    Dim strSQL, strDate, SQLDate, oDB : Set oDB = oApp.Database
    strDate = DateAdd(sType, iDuration, Now())
    SQLDate = Right("0000" & Year(strDate),   4) & "-" & _
              Right("00"   & Month(strDate),  2) & "-" & _
              Right("00"   & Day(strDate),    2) & " " & _
              Right("00"   & Hour(strDate),   2) & ":" & _
              Right("00"   & Minute(strDate), 2) & ":" & _
              Right("00"   & Second(strDate), 2)
    'strSQL = "INSERT INTO hm_securityranges (rangepriorityid, rangelowerip1, rangeupperip1, rangeoptions, rangename, rangeexpires, rangeexpirestime) " & _
    '         "VALUES (20, " & INET_ATON(sIPAddress) & ", " & INET_ATON(sIPAddress) & ", 0, '(" & sReason & ") " & sIPAddress & "', 1, '" & SQLDate & "') " & _
    '         "ON DUPLICATE KEY UPDATE rangeexpirestime = '" & SQLDate & "';"
    strSQL = "INSERT INTO hm_securityranges (rangepriorityid, rangelowerip1, rangeupperip1, rangeoptions, rangename, rangeexpires, rangeexpirestime) " & _
             "VALUES (20, INET_ATON('" & sIPAddress & "'), INET_ATON('" & sIPAddress & "'), 0, '(" & sReason & ") " & sIPAddress & "', 1, '" & SQLDate & "') " & _
             "ON DUPLICATE KEY UPDATE rangeexpirestime = '" & SQLDate & "';"
    Call oDB.ExecuteSQL(strSQL)
    AutoBan = True
    oApp.Settings.SecurityRanges.Refresh
End Function

Function Lookup(strRegEx, strMatch) : Lookup = False
    If strRegEx = "" Then Exit Function
    With CreateObject("VBScript.RegExp")
        .Pattern = strRegEx
        .Global = False
        .MultiLine = True
        .IgnoreCase = True
        If .Test(strMatch) Then Lookup = True
    End With
End Function

Function oLookup(strRegEx, strMatch, bGlobal)
    On Error Resume Next
    With CreateObject("VBScript.RegExp")
        .Pattern = strRegEx
        .Global = bGlobal
        .MultiLine = True
        .IgnoreCase = True
        Set oLookup = .Execute(strMatch)
        If Err.number <> 0 Then EventLog.Write(  "RC = " & Err.number & " - Function oLookup(" & strRegEx & ", " & strMatch & ", " & bGlobal & ")" )
    End With
    On Error GoTo 0
End Function

Function Disconnect(strIP)
    WScript.Echo "Disconnect ..."
    Dim strOut
    Dim oShell : Set oShell = CreateObject("WScript.Shell")
    'Dim oExec : Set oExec = oShell.Exec("cports /close * * " & strIP & " *")
    Dim oExec : Set oExec = oShell.Exec("disconnect " & strIP)
    Set oExec = Nothing
    Set oShell = Nothing
End Function
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Firewall Ban

Post by palinka » 2021-03-25 14:49

SorenR wrote:
2021-03-24 17:18
I have switched to a different GEO site... Requires a KEY but you can get a free one.
MaxMind local db. https://hmailserver.com/forum/viewtopic.php?f=9&t=34496 :mrgreen:

User avatar
SorenR
Senior user
Senior user
Posts: 6315
Joined: 2006-08-21 15:38
Location: Denmark

Re: Firewall Ban

Post by SorenR » 2021-03-25 15:12

palinka wrote:
2021-03-25 14:49
SorenR wrote:
2021-03-24 17:18
I have switched to a different GEO site... Requires a KEY but you can get a free one.
MaxMind local db. https://hmailserver.com/forum/viewtopic.php?f=9&t=34496 :mrgreen:
Ah, yes ... Forgot about that one. :wink:
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Firewall Ban

Post by palinka » 2021-03-25 17:25

SorenR wrote:
2021-03-25 15:12
palinka wrote:
2021-03-25 14:49
SorenR wrote:
2021-03-24 17:18
I have switched to a different GEO site... Requires a KEY but you can get a free one.
MaxMind local db. https://hmailserver.com/forum/viewtopic.php?f=9&t=34496 :mrgreen:
Ah, yes ... Forgot about that one. :wink:
Weekly updates from MaxMind. Yesterday's update:

3,974 Records removed from database
5,051 Records inserted into database

322,175 total records

udgesbou
Normal user
Normal user
Posts: 41
Joined: 2021-04-05 19:33
Location: Germany

Re: Firewall Ban

Post by udgesbou » 2022-04-03 17:57

Heyho,
i want to use your Firewall Ban-Method, but it will always give out Errors when i want to run hmsFirewallBanDBSetup.ps1 to setup database tables:
22.04.03 17:46:33.85 : ERROR : Unable to run query :
CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
id INT(22) NOT NULL AUTO_INCREMENT,
ipaddress varchar(15) NOT NULL UNIQUE,
hits INT(8),
lasttimestamp datetime NOT NULL,
PRIMARY KEY (id)
UNIQUE KEY ipaddress (ipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMIT;

Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en): "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE KEY ipaddress (ipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMI' at line 7"[0]
Here is my Config.ps1:

Code: Select all

<#
_  _ _  _  _  _ _    ____ ____ ____ _  _ ____ ____     
|__| |\/| /_\ | |    [__  |___ |__/ |  | |___ |__/     
|  | |  |/   \| |___ ___] |___ |  \  \/  |___ |  \     
____ _ ____ ____ _ _ _  _  _    _       ___   _  _  _ 
|___ | |__/ |___ | | | /_\ |    |       |__] /_\ |\ | 
|    | |  \ |___ |_|_|/   \|___ |___    |__]/   \| \| 

.SYNOPSIS
	Config File

.DESCRIPTION
	Config File

.FUNCTIONALITY

.NOTES

.EXAMPLE

#>

###   MYSQL VARIABLES   ################################################################
#                                                                                      #
$DatabaseType     = 'MYSQL'            #<-- Options: "MYSQL" or "MSSQL"                #
$SQLAdminUserName = 'MySQLUsername'                                                      #
$SQLAdminPassword = 'MyLoginPassword'                                              #
$SQLDatabase      = 'hmailserverdb'                                                      #
$SQLHost          = '127.0.0.1'                                                        #
$SQLPort          = 3306                                                               #
$SQLSSL           = 'none'                                                             #
#                                                                                      #
###   MySQL SSL OPTIONS   ##############################################################
#                                                                                      #
#   Set to 'none' if Powershell and MySQL on same machine (seems to be MySQL bug)      #
#                                                                                      #
#	None       - Do not use SSL.                                                       #
#	Preferred  - Use SSL if the server supports it, but allow connection in all cases. #
#	Required   - Always use SSL. Deny connection if server does not support SSL.       #
#	VerifyCA   - Always use SSL. Validate the CA but tolerate name mismatch.           #
#	VerifyFull - Always use SSL. Fail if the host name is not correct.                 #
#                                                                                      #
###   FIREWALL VARIABLES   #############################################################
#                                                                                      #
$LANSubnet        = 'MyStaticIPAdress'  # <-- 3 octets only, please                           #
$MailPorts        = '25|465|587|110|995|143|993' # <-- add custom ports if in use      #
$FirewallLog      = 'C:\scripts\hmailserver\FWBan\Firewall\pfirewall.log'              #
#                                                                                      #
###   INTERVAL VARIABLES   #############################################################
#                                                                                      #
$Interval         = 5   # <-- (minutes) must match the frequency of Win Sched Task     #
$IDSExpire        = 12  # <-- (hours) expire IDS entries that have not resulted in ban #
#                                                                                      #
###   PHP VARIABLES   ##################################################################
#                                                                                      #
$wwwFolder        = "C:\inetpub\wwwroot\firewallban.captainservices.de" # <-- www folder location         #
$wwwURI           = "https://firewallban.captainservices.de"   # <-- no trailing slash, please   #
#                                                                                      #
###   EMAIL VARIABLES   ################################################################
#                                                                                      #
$FromAddress      = 'My GoogleMail-Adress'                                       #
$Recipient        = 'My Domain Adress - info@'                                                  #
$SMTPServer       = 'smtp.gmail.com'                                                   #
$SMTPAuthUser     = 'My GoogleMail-Adress'                                       #
$SMTPAuthPass     = 'My GoogleMail-Password'                                              #
$SMTPPort         = 587                                                                #
$SSL              = 'True'                                                             #
#                                                                                      #
########################################################################################

I am new in scriptconfig for hmailserver, so i hope you can help me? :(
If you need some more informations i think i can send it :wink:

Thanks! :D

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

Re: Firewall Ban

Post by palinka » 2022-04-03 18:22

udgesbou wrote:
2022-04-03 17:57
Heyho,
i want to use your Firewall Ban-Method, but it will always give out Errors when i want to run hmsFirewallBanDBSetup.ps1 to setup database tables:
22.04.03 17:46:33.85 : ERROR : Unable to run query :
CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
id INT(22) NOT NULL AUTO_INCREMENT,
ipaddress varchar(15) NOT NULL UNIQUE,
hits INT(8),
lasttimestamp datetime NOT NULL,
PRIMARY KEY (id)
UNIQUE KEY ipaddress (ipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMIT;

Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en): "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE KEY ipaddress (ipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMI' at line 7"[0]
Try changing the query to:

Code: Select all

		CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
		  id INT(22) NOT NULL AUTO_INCREMENT,
		  ipaddress varchar(15) NOT NULL,
		  hits INT(8),
		  lasttimestamp datetime NOT NULL,
		  PRIMARY KEY (id)
		  UNIQUE KEY ipaddress (ipaddress)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
Also, I stopped actively developing this a year ago or more. I'll try to help you where I can, but expect some bugs and lots of maintenance.

udgesbou
Normal user
Normal user
Posts: 41
Joined: 2021-04-05 19:33
Location: Germany

Re: Firewall Ban

Post by udgesbou » 2022-04-04 08:46

Hey palinka,
thanks for your fast reply :D
palinka wrote:
2022-04-03 18:22
Try changing the query to:

Code: Select all

		CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
		  id INT(22) NOT NULL AUTO_INCREMENT,
		  ipaddress varchar(15) NOT NULL,
		  hits INT(8),
		  lasttimestamp datetime NOT NULL,
		  PRIMARY KEY (id)
		  UNIQUE KEY ipaddress (ipaddress)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
I changed this, but i get the same Error as before :(

Oh okay, sorry that you stopped actively developing this tool :( Do you know another similar script witch i can use?

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2022-04-04 09:18

Try this

Code: Select all


CREATE TABLE `hm_fwban_blocks_ip` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ipaddress` varchar(15) COLLATE utf8mb4_general_ci NOT NULL,
  `hits` int DEFAULT NULL,
  `lasttimestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ipaddress` (`ipaddress`),
  UNIQUE KEY `ipaddress_2` (`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


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

Re: Firewall Ban

Post by palinka » 2022-04-04 12:21

gotspatel wrote:
2022-04-04 09:18
Try this

Code: Select all


CREATE TABLE `hm_fwban_blocks_ip` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ipaddress` varchar(15) COLLATE utf8mb4_general_ci NOT NULL,
  `hits` int DEFAULT NULL,
  `lasttimestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ipaddress` (`ipaddress`),
  UNIQUE KEY `ipaddress_2` (`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

commas are important! :D

Code: Select all

  PRIMARY KEY (`id`),
I think that's what was missing above in both mine and his.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Firewall Ban

Post by gotspatel » 2022-04-04 12:25

palinka wrote:
2022-04-04 12:21
gotspatel wrote:
2022-04-04 09:18
Try this

Code: Select all


CREATE TABLE `hm_fwban_blocks_ip` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ipaddress` varchar(15) COLLATE utf8mb4_general_ci NOT NULL,
  `hits` int DEFAULT NULL,
  `lasttimestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ipaddress` (`ipaddress`),
  UNIQUE KEY `ipaddress_2` (`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

commas are important! :D

Code: Select all

  PRIMARY KEY (`id`),
I think that's what was missing above in both mine and his.
:D Good Point Will keep that in mind.

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

Re: Firewall Ban

Post by palinka » 2022-04-04 12:31

udgesbou wrote:
2022-04-04 08:46
Hey palinka,
thanks for your fast reply :D
palinka wrote:
2022-04-03 18:22
Try changing the query to:

Code: Select all

		CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
		  id INT(22) NOT NULL AUTO_INCREMENT,
		  ipaddress varchar(15) NOT NULL,
		  hits INT(8),
		  lasttimestamp datetime NOT NULL,
		  PRIMARY KEY (id)
		  UNIQUE KEY ipaddress (ipaddress)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
I changed this, but i get the same Error as before :(

Oh okay, sorry that you stopped actively developing this tool :( Do you know another similar script witch i can use?
I think I missed a comma.

Code: Select all

		  PRIMARY KEY (id),
This project became overly cumbersome and never ran perfectly. Very complex and I was just bug fixing all the time. Overall it worked fine, but there was always something minor to fix.

The reason I stopped development is because my server crashed and when I rebuilt it, I decided not to rebuild it and see what happens. The result was virtually no change.

One of the things I learned - because the script kept a lot of statistics - is that most of the banned IPs were part of gigantic bot nets and never returned. They did something naughty, got banned, but I never needed to ban them because they never came back anyway. So now I just reject traffic based on the same rules I used to ban them. It hasn't really made much of a difference. The same ruleset is used to determine reject and firewall ban. Banning is only as good as the rules used to determine whether to ban or not.

Have a look here for some good rules: viewtopic.php?f=21&t=33566

udgesbou
Normal user
Normal user
Posts: 41
Joined: 2021-04-05 19:33
Location: Germany

Re: Firewall Ban

Post by udgesbou » 2022-04-04 14:48

palinka wrote:
2022-04-04 12:31
Have a look here for some good rules: viewtopic.php?f=21&t=33566
Okay, thank you palinka, i will take a look at these rules and hope i understand and can use them :oops:


Greetings

Post Reply