Firewall Ban

[palinka]

I ran an experiment. How many IPs have returned and been blocked by the firewall for a given number of days? Lots.

The "days" need not be consecutive. Some are, of course, but most are spread out once or twice a week. I only counted distinct days. Blocks are parsed from the firewall log.

Code: Select all

 12,810 : Total number of firewall rules

    117 : Number of days worth of data in database

  5,824 : Total number of return IPs blocked

  3,799 : Number of return IPs blocked on more than 1 day
  2,882 : Number of return IPs blocked on more than 2 days
  2,173 : Number of return IPs blocked on more than 3 days
  1,612 : Number of return IPs blocked on more than 4 days
  1,197 : Number of return IPs blocked on more than 5 days
    904 : Number of return IPs blocked on more than 6 days
    675 : Number of return IPs blocked on more than 7 days
    511 : Number of return IPs blocked on more than 8 days
    393 : Number of return IPs blocked on more than 9 days
    312 : Number of return IPs blocked on more than 10 days
    239 : Number of return IPs blocked on more than 11 days
    182 : Number of return IPs blocked on more than 12 days
    139 : Number of return IPs blocked on more than 13 days
     98 : Number of return IPs blocked on more than 14 days
     72 : Number of return IPs blocked on more than 15 days
     59 : Number of return IPs blocked on more than 16 days
     45 : Number of return IPs blocked on more than 17 days
     39 : Number of return IPs blocked on more than 18 days
     31 : Number of return IPs blocked on more than 19 days
     25 : Number of return IPs blocked on more than 20 days
     21 : Number of return IPs blocked on more than 21 days
     18 : Number of return IPs blocked on more than 22 days
     14 : Number of return IPs blocked on more than 23 days
     10 : Number of return IPs blocked on more than 24 days
      9 : Number of return IPs blocked on more than 25 days
      8 : Number of return IPs blocked on more than 26 days
      6 : Number of return IPs blocked on more than 27 days
      5 : Number of return IPs blocked on more than 28 days
      3 : Number of return IPs blocked on more than 29 days
      3 : Number of return IPs blocked on more than 30 days
      3 : Number of return IPs blocked on more than 31 days
      3 : Number of return IPs blocked on more than 32 days
      3 : Number of return IPs blocked on more than 33 days
      3 : Number of return IPs blocked on more than 34 days
      2 : Number of return IPs blocked on more than 35 days
      2 : Number of return IPs blocked on more than 36 days
      2 : Number of return IPs blocked on more than 37 days
      2 : Number of return IPs blocked on more than 38 days
        : Number of return IPs blocked on more than 39 days
        : Number of return IPs blocked on more than 40 days

[mattg]

So just blocking for 14 days would catch most of them?

[palinka]

So just blocking for 14 days would catch most of them?

No. They are not consecutive days. I counted distinct days. They could be consecutive, but experience shows they are not. Most are bots programmed for 5 hits per day, once per week. So a blocked IP that reappeared 14 times is likely to be once a week for 14 weeks.

I've seen randomly-frequent ones and some that hammer out a few hundred tries in two days and give up never to be seen again. Its kind of all over the map, but the largest frequency seems to be 5 tries per day, once per week.

[palinka]

For example, this is the most recent block on the list - not the most recent to be blocked, but rather the most recent banned IP that was blocked (last drop in the firewall log). This one is kind of random. Not once per week, but nonetheless...

[palinka]

I re-ran the 40 day+ test with percentages. Its more readable, I think.

I would say - completely arbitrarily - that 10% is a good number to look at. The 10% threshold is 4+ days, non-consecutively.

What its not telling us is how many blocks are actually occurring. Its only counting IPs and the number of days of reappearance. Maybe I'll throw that in too. Later.

Code: Select all

 12,832 : Total number of firewall rules

    117 : Number of days data in database

  5,833 : 45.46% : Number of return IPs blocked on at least 1 day
  3,804 : 29.64% : Number of return IPs blocked on more than 1 day
  2,886 : 22.49% : Number of return IPs blocked on more than 2 days
  2,179 : 16.98% : Number of return IPs blocked on more than 3 days
  1,615 : 12.59% : Number of return IPs blocked on more than 4 days
  1,199 :  9.34% : Number of return IPs blocked on more than 5 days
    907 :  7.07% : Number of return IPs blocked on more than 6 days
    677 :  5.28% : Number of return IPs blocked on more than 7 days
    515 :  4.01% : Number of return IPs blocked on more than 8 days
    395 :  3.08% : Number of return IPs blocked on more than 9 days
    313 :  2.44% : Number of return IPs blocked on more than 10 days
    241 :  1.88% : Number of return IPs blocked on more than 11 days
    184 :  1.43% : Number of return IPs blocked on more than 12 days
    142 :  1.11% : Number of return IPs blocked on more than 13 days
     98 :  0.76% : Number of return IPs blocked on more than 14 days
     74 :  0.58% : Number of return IPs blocked on more than 15 days
     59 :  0.46% : Number of return IPs blocked on more than 16 days
     45 :  0.35% : Number of return IPs blocked on more than 17 days
     40 :  0.31% : Number of return IPs blocked on more than 18 days
     31 :  0.24% : Number of return IPs blocked on more than 19 days
     25 :  0.19% : Number of return IPs blocked on more than 20 days
     21 :  0.16% : Number of return IPs blocked on more than 21 days
     18 :  0.14% : Number of return IPs blocked on more than 22 days
     15 :  0.12% : Number of return IPs blocked on more than 23 days
     10 :  0.08% : Number of return IPs blocked on more than 24 days
      9 :  0.07% : Number of return IPs blocked on more than 25 days
      8 :  0.06% : Number of return IPs blocked on more than 26 days
      6 :  0.05% : Number of return IPs blocked on more than 27 days
      5 :  0.04% : Number of return IPs blocked on more than 28 days
      3 :  0.02% : Number of return IPs blocked on more than 29 days
      3 :  0.02% : Number of return IPs blocked on more than 30 days
      3 :  0.02% : Number of return IPs blocked on more than 31 days
      3 :  0.02% : Number of return IPs blocked on more than 32 days
      3 :  0.02% : Number of return IPs blocked on more than 33 days
      3 :  0.02% : Number of return IPs blocked on more than 34 days
      2 :  0.02% : Number of return IPs blocked on more than 35 days
      2 :  0.02% : Number of return IPs blocked on more than 36 days
      2 :  0.02% : Number of return IPs blocked on more than 37 days
      2 :  0.02% : Number of return IPs blocked on more than 38 days
        :  0.00% : Number of return IPs blocked on more than 39 days
        :  0.00% : Number of return IPs blocked on more than 40 days

[hMailserver-User]

Hello palinka!

trying to get things sorted with my hmailserver - installation so i downloades .48 from your GitHub

Is it possible that in EventHandlers.vbs

at line 180 a ' is missing? ("Exclude Backup-MX & local LAN from test" -> " ' Exclude Backup-MX & local LAN from test"
at line 302 a ) is missing? ("Call idsDelIP(oClient.IPAddress" -> "Call idsDelIP(oClient.IPAddress)"

Path's to disconnect.exe and VbsJson.vbs are hardcoded, i changed that to my needs.

Thx!

[palinka]

Hello palinka!

trying to get things sorted with my hmailserver - installation so i downloades .48 from your GitHub

Is it possible that in EventHandlers.vbs

at line 180 a ' is missing? ("Exclude Backup-MX & local LAN from test" -> " ' Exclude Backup-MX & local LAN from test"
at line 302 a ) is missing? ("Call idsDelIP(oClient.IPAddress" -> "Call idsDelIP(oClient.IPAddress)"

Path's to disconnect.exe and VbsJson.vbs are hardcoded, i changed that to my needs.

Thx!

Yes, Yes and good! And thanks for letting me know about the bugs. I'll fix them.

Every time I make a change I need to wade through a bunch of errors because I always forget SOMETHING.

[hMailserver-User]

hi palinka,

currently i'm running into another problem, error message:
"ERROR" 2556 "2019-11-16 12:07:16.696" "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01F4 - Description: Variable is not defined: 'idsTable' - Line: 107 Column: 4 - Code: (null)"

referencing to (my) line 107:
strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"

shouldn't this be:
strSQL = "INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"

Thx!

[palinka]

hi palinka,

currently i'm running into another problem, error message:
"ERROR" 2556 "2019-11-16 12:07:16.696" "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01F4 - Description: Variable is not defined: 'idsTable' - Line: 107 Column: 4 - Code: (null)"

referencing to (my) line 107:
strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"

shouldn't this be:
strSQL = "INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"

Thx!

Yes. Or no. I copied Soren's IDS code for this and he declared the table name at the top of EventHandlers.vbs. it's ok to hard code that in.

Also, in hmsFirewallBan.ps1 you can define the expiration time for IDS entries. I set it to 2 days. Basically, if a random connection does not FAIL to either send mail OR successfully log in 3 times within that expiration period, then it gets deleted. There is no need to clog up the works with IPs that will never return. The expiration could be set to 1 day or 1 hour if you want. I think 2 days is reasonable. Most entries expire without getting banned.

[palinka]

Also, you should check for updates often because most of the changes are in the web interface pages which you can just drop in without worrying about changes.

Speaking of which, I just added a php version of BlockCount.ps1 to the web interface. Demo here: http://hmsfirewallbandemo.ddns.net/blocks.php

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

[hMailserver-User]

I still post my experience in setting this up - i hope you don't mind

There is another line (114) which is using "idsTable". Changed it to hm_ids because in your project its not defined as variable.

Another thing is that on the example for adding the three MSSQL tables on the hm_ids - table the column "helo" is missing. So i addes it with the same parameters as used in "hm_fwban" (`helo` varchar(192) NOT NULL,). Should be correct?

Adapted the above i now get another error: "ERROR" 3912 "2019-11-16 13:52:57.674" "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 261 Column: 2 - Code: (null)"
But as i am a normal user i have no idea what to do with it.

[palinka]

I still post my experience in setting this up - i hope you don't mind

There is another line (114) which is using "idsTable". Changed it to hm_ids because in your project its not defined as variable.

Another thing is that on the example for adding the three MSSQL tables on the hm_ids - table the column "helo" is missing. So i addes it with the same parameters as used in "hm_fwban" (`helo` varchar(192) NOT NULL,). Should be correct?

Adapted the above i now get another error: "ERROR" 3912 "2019-11-16 13:52:57.674" "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 261 Column: 2 - Code: (null)"
But as i am a normal user i have no idea what to do with it.

IDS table cannot use helo because it should be called at OnClientConnect. If you want to gather information about IDS connections (or any other) look at my connection log project.

Can you post line 261?

[palinka]

I still post my experience in setting this up - i hope you don't mind

Absolutely. I haven't had a lot of feedback so yours is very helpful. I'm revising as you find things. So thank you for that.

[hMailserver-User]

Line 261: Call FWBan(oClient.IPAddress, "HELO-Inv", oClient.HELO)

Hmm - but "helo" is used in this function:

Code: Select all

Function idsAddIP(sIPAddress, sCountry, sHELO)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

And this is the error generated:

Code: Select all

"ERROR"	4036	"2019-11-16 13:37:38.887"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Unknown column 'helo' in 'field list' (Additional info: INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'193.56.28.101',1,'Poland','User') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();)"
"ERROR"	4036	"2019-11-16 13:37:38.887"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: Execution of SQL statement failed. Error: MySQL: Unknown column 'helo' in 'field list' (Additional info: INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'193.56.28.101',1,'Poland','User') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();) - Line: 108 Column: 4 - Code: (null)"

So it would be the better idea to not add the "helo" - column to MSSQL but removing it from the function above??

[palinka]

Line 261: Call FWBan(oClient.IPAddress, "HELO-Inv", oClient.HELO)

Hmm - but "helo" is used in this function:

Code: Select all

Function idsAddIP(sIPAddress, sCountry, sHELO)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

And this is the error generated:

Code: Select all

"ERROR"	4036	"2019-11-16 13:37:38.887"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Unknown column 'helo' in 'field list' (Additional info: INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'193.56.28.101',1,'Poland','User') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();)"
"ERROR"	4036	"2019-11-16 13:37:38.887"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: Execution of SQL statement failed. Error: MySQL: Unknown column 'helo' in 'field list' (Additional info: INSERT INTO hm_ids (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'193.56.28.101',1,'Poland','User') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();) - Line: 108 Column: 4 - Code: (null)"

So it would be the better idea to not add the "helo" - column to MSSQL but removing it from the function above??

OK, I see. I should have revamped the whole thing. The problem is that as this project has evolved, I have not been removing obsolete elements.

Here's a comparison for IDS between my own version and the one posted on GitHub.

Code: Select all

********** MY IDS table: **********

CREATE TABLE `hm_ids` (
  `timestamp` datetime NOT NULL,
  `ipaddress` varchar(15) NOT NULL,
  `hits` int(1) NOT NULL,
  `country` varchar(64) DEFAULT NULL, <<-- REMNANT - COMPLETELY UNNECESSARY
  `helo` varchar(128) DEFAULT NULL,   <<-- REMNANT - COMPLETELY UNNECESSARY
  PRIMARY KEY (`ipaddress`),
  UNIQUE KEY `ipaddress` (`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


********** MY eventhandlers: **********

Function idsAddIP(sIPAddress)
	Dim m_CountryCode, m_CountryName
	Call GeoIPLookup(sIPAddress, m_CountryCode, m_CountryName)

    Dim strSQL, oDB : Set oDB = GetDatabaseObject
	strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country) VALUES (NOW(),'" & sIPAddress & "',1,'" & m_CountryName & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

Function idsDelIP(sIPAddress)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "DELETE FROM " & idsTable & " WHERE ipaddress = '" & sIPAddress & "';"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

Sub OnClientConnect(oClient)

	' Exclude Backup-MX & local LAN from test
	If (Left(oClient.IPAddress, 12) = "184.105.182.") Then Exit Sub
	If (Left(oClient.IPAddress, 8) = "192.168.") Then Exit Sub
	If oClient.IPAddress = "127.0.0.1" Then Exit Sub

	Call idsAddIP(oClient.IPAddress)

End Sub

Sub OnClientLogon(oClient)
	Dim strPort, strRegEx, sMSG, SMSNumber, ShURL
	' Ignore this ->strPort = Trim(Mid("SMTP POP  IMAP SMTPSSUBM IMAPSPOPS ", InStr("25   110  143  465  587  993  995  ", oClient.Port), 5))
	If oClient.Authenticated then
		Call idsDelIP(oClient.IPAddress)
		' Ignore this ->Call AccRejDB(strPort, oClient.Port, "OnClientLogon", "Accepted", "Login", oClient.IPAddress, oClient.Username)
	Else
		strRegEx = "@mydomain.us|@mydomain.com"
		If Lookup(strRegEx, oClient.Username) Then
			' Do something here if you want - I have a notification to let me know if someone fails login
		End If
		' Ignore this ->Call AccRejDB(strPort, oClient.Port, "OnClientLogon", "REJECTED", "Login", oClient.IPAddress, oClient.Username)
	End if
End Sub

Sub OnAcceptMessage(oClient, oMessage)

	'	Clean up IDS for successfully received mail
	'	This goes at the very end of OnAcceptMessage after all spam tests
	Call idsDelIP(oClient.IPAddress)

End Sub


********** github IDS table: **********

CREATE TABLE `hm_ids` (
  `timestamp` datetime NOT NULL,
  `ipaddress` varchar(15) NOT NULL,
  `hits` int(1) NOT NULL,
  `country` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`ipaddress`),
  UNIQUE KEY `ipaddress` (`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


********** eventhandlers on github: **********

Function idsAddIP(sIPAddress, sCountry, sHELO)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country,helo) VALUES (NOW(),'" & sIPAddress & "',1,'" & sCountry & "','" & sHELO & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

Function idsDelIP(sIPAddress)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "DELETE FROM " & idsTable & " WHERE ipaddress = '" & sIPAddress & "';"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

Sub OnClientConnect(oClient)
	'	Exclude Backup-MX & local LAN from test
	If (Left(oClient.IPAddress, 12) = "184.105.182.") Then Exit Sub
	If (Left(oClient.IPAddress, 8) = "192.168.") Then Exit Sub
	If oClient.IPAddress = "127.0.0.1" Then Exit Sub
End Sub

Sub OnHELO(oClient)

	Dim strRegEx, Match, Matches
	Dim strPort
	Dim ReturnCode, Json, oGeoip, oXML, strBase
	Dim bolGeoIP : bolGeoIP = False

	strPort = Trim(Mid("SMTP POP  IMAP SMTPSSUBM IMAPSPOPS ", InStr("25   110  143  465  587  993  995  ", oClient.Port), 5))

	'	Exclude local LAN & Backup from test after recording connection
	If (Left(oClient.IPAddress, 8) = "192.168.") Then Exit Sub
	If (Left(oClient.IPAddress, 9) = "127.0.0.1") Then Exit Sub
	If (Left(oClient.IPAddress, 12) = "184.105.182.") Then Exit Sub

	'   GEOIP Lookup HERE

	' Call IDS on all non-local SMTP connections
    If (InStr("|25|587|465|", oClient.Port) > 0) Then Call idsAddIP(oClient.IPAddress, oGeoip("country"), oClient.HELO)
	' This ^^^ is a problem because it fails to pickup most of the stuff IDS is designed to pickup - that's why I changed it.

Sub OnClientLogon(oClient)

	'	Successful logons get IDS entry removed
	If oClient.Authenticated Then
		Call idsDelIP(oClient.IPAddress)
	End If

End Sub

Sub OnAcceptMessage(oClient, oMessage)

	'	Successfully received mail gets IDS entry removed
    Call idsDelIP(oClient.IPAddress)
	'	^^^ also ^^^ needs clarification - should go at the very end of OnAcceptMessage

End Sub

Basically, the hm_ids table column "helo" is completely redundant. They are the result of a failed experiment. I should have removed them and I will fix up the code to remove them completely. Therefore, the idsAddIP function really should be changed to this:

Code: Select all

Function idsAddIP(sIPAddress)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
	strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits) VALUES (NOW(),'" & sIPAddress & "',1) ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

And you should call it like this:
Call idsAddIP(oClient.IPAddress)

Also notice that idsAddIP is called from OnClientConnect. This is important because it picks up EVERYTHING, not just mail submissions.

NOW ---- After all that - IF you want to insert the country name as well, you will need a method of doing that. I have since changed my geoip from ip-api.com to a local database using maxminds data. Look in the scripting forum for that if you want to use it. If you do, you can just copy the idsAddIP function that I use for myself. Actually, the maxminds thing works great and I highly recommend it.

I'll fix all this up and push it to GitHub.

[palinka]

Try this to get country info into IDS:

Code: Select all

Function idsAddIP(sIPAddress)
	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 " & idsTable & " (timestamp,ipaddress,hits,country) VALUES (NOW(),'" & sIPAddress & "',1,'" & oGeoip("country") & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

I just pushed these changes to GitHub.

[hMailserver-User]

Hi,

thx for your efforts. I deleted everything and started all over again with the latest version from github.
Now another error is occuring:

Code: Select all

"ERROR" 3516 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01A8 - Description: Object required: 'LockFile(...)' - Line: 142 Column: 3 - Code: (null)"

Line 142:

Code: Select all

   With LockFile(TEMPDIR & "\autoban.lck")

Thx!

[palinka]

Hi,

thx for your efforts. I deleted everything and started all over again with the latest version from github.
Now another error is occuring:

Code: Select all

"ERROR" 3516 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01A8 - Description: Object required: 'LockFile(...)' - Line: 142 Column: 3 - Code: (null)"

Line 142:

Code: Select all

   With LockFile(TEMPDIR & "\autoban.lck")

Thx!

I may have added this after you downloaded it. I made several changes today. It should go at the top of EventHandlers.vbs (below "option explicit", of course).

Code: Select all

' 	COM authentication

Private Const ADMIN = "Administrator"
Private Const PASSWORD = "supersecretpassword"

Private Const EVENTDIR = "C:\Program Files (x86)\hMailServer\Events"
Private Const LOGDIR   = "C:\Program Files (x86)\hMailServer\Logs"
Private Const TEMPDIR  = "C:\Program Files (x86)\hMailServer\Temp"
Private Const idsTable = "hm_ids"

Notice the TEMPDIR above. I'm not sure if its the TEMPDIR or COM authentication, but adding both should surely fix the problem.

Also, for faster response, quote me so I get a notification email.

[hMailserver-User]

I may have added this after you downloaded it. I made several changes today. It should go at the top of EventHandlers.vbs (below "option explicit", of course).

Code: Select all

' 	COM authentication

Private Const ADMIN = "Administrator"
Private Const PASSWORD = "supersecretpassword"

Private Const EVENTDIR = "C:\Program Files (x86)\hMailServer\Events"
Private Const LOGDIR   = "C:\Program Files (x86)\hMailServer\Logs"
Private Const TEMPDIR  = "C:\Program Files (x86)\hMailServer\Temp"
Private Const idsTable = "hm_ids"

Notice the TEMPDIR above. I'm not sure if its the TEMPDIR or COM authentication, but adding both should surely fix the problem.

Also, for faster response, quote me so I get a notification email.

Uuups - my fault - i am on the latest version but i used a wrong path (copy paste - error). Corrected it and now i'm a bit further
There is now a new one:

Code: Select all

"ERROR" 652 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 254 Column: 2 - Code: (null)"

Line 254:

Code: Select all

	Call FWBan(oClient.IPAddress, "GeoIP", oClient.HELO)

But it happens also on other lines where the function FWBan is called

[palinka]

I may have added this after you downloaded it. I made several changes today. It should go at the top of EventHandlers.vbs (below "option explicit", of course).

Code: Select all

' 	COM authentication

Private Const ADMIN = "Administrator"
Private Const PASSWORD = "supersecretpassword"

Private Const EVENTDIR = "C:\Program Files (x86)\hMailServer\Events"
Private Const LOGDIR   = "C:\Program Files (x86)\hMailServer\Logs"
Private Const TEMPDIR  = "C:\Program Files (x86)\hMailServer\Temp"
Private Const idsTable = "hm_ids"

Notice the TEMPDIR above. I'm not sure if its the TEMPDIR or COM authentication, but adding both should surely fix the problem.

Also, for faster response, quote me so I get a notification email.

Uuups - my fault - i am on the latest version but i used a wrong path (copy paste - error). Corrected it and now i'm a bit further
There is now a new one:

Code: Select all

"ERROR" 652 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 254 Column: 2 - Code: (null)"

Line 254:

Code: Select all

	Call FWBan(oClient.IPAddress, "GeoIP", oClient.HELO)

But it happens also on other lines where the function FWBan is called

Can you post a little more of the script surrounding it? Where is it? When is it being called?

Do you have a version of hmailserver that has OnHELO? Or an official version before 5.7.0?

[hMailserver-User]

Can you post a little more of the script surrounding it? Where is it? When is it being called?
Do you have a version of hmailserver that has OnHELO? Or an official version before 5.7.0?

I'm using RvdH's custom build v5.6.8-B2489.22 as you suggested a few postings before.

I am using the latest EventHandlers - script from GitHub and modified only the neccecary paramters at the beginning and the paths to VbsJson and Disconnect in the script.

So when a trigger is triggered - like in the examle in my previous posting:

Code: Select all

	If bolGeoIP Then
		'  Connection PASSED examination
	Else
		'  Disconnect all others.
		Result.Value = 2
		Result.Message = ". 01 This mail server does not accept connections from " & oGeoip("country") &". If you believe that this failure is in error, please contact the intended recipient via alternate means."
		Call Disconnect(oClient.IPAddress)
		Call AutoBan(oClient.IPAddress, "GeoIP - " & oClient.IpAddress, 1, "h")
		Call FWBan(oClient.IPAddress, "GeoIP", oClient.HELO)
		Exit Sub
	End If

it calls this function:

Code: Select all

'	Function FWBan - http://hmailserver.com/forum/viewtopic.php?f=9&t=34082
Function FWBan(sIPAddress, sReason)
   Include("D:\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,flag) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("countryCode") & "','" & oGeoip("country") & "','4');"
   Call oDB.ExecuteSQL(strSQL)
End Function

and the i run into this error:

Code: Select all

"ERROR" 652 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 254 Column: 2 - Code: (null)"

This happens also on trigger for "Validate HELO/EHLO greeting" or other triggers which call the FWBan - function.

[hMailserver-User]

And: thank you for your patience!

[palinka]

Call FWBan(oClient.IPAddress, "GeoIP", oClient.HELO)

Function FWBan(sIPAddress, sReason)

"ERROR" 652 "Script Error: Source: Runtime error in Microsoft VBScript - Error: 800A01C2 - Description: Wrong number of arguments or invalid property assignment: 'FWBan' - Line: 254 Column: 2 - Code: (null)"

Ah, sorry. Looks like diverging too far from my own setup is causing me to leave things out. I'll fix this on GitHub.

In the meantime, the issue is "wrong number of arguments". The call is sending 3: (oClient.IPAddress, "GeoIP", oClient.HELO). The function is asking for 2: (sIPAddress, sReason). Since you're using RvdH's build with OnHELO, you should be sending HELO, so modify the function like this:

Code: Select all

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

[hMailserver-User]

Ah, sorry. Looks like diverging too far from my own setup is causing me to leave things out. I'll fix this on GitHub.

In the meantime, the issue is "wrong number of arguments". The call is sending 3: (oClient.IPAddress, "GeoIP", oClient.HELO). The function is asking for 2: (sIPAddress, sReason). Since you're using RvdH's build with OnHELO, you should be sending HELO, so modify the function like this:

Code: Select all

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

Changed the code for FWBan with the one above and now there is another error:

Code: Select all

"SMTPD"	"103.4.94.178"	"RECEIVED: EHLO 103.4.94.178.pern.pk"
"ERROR"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Field 'ID' doesn't have a default value (Additional info: INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country,helo,flag) VALUES (NOW(),'103.4.94.178','GeoIP','PK','Pakistan','103.4.94.178.pern.pk','4');)"
"ERROR"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: Execution of SQL statement failed. Error: MySQL: Field 'ID' doesn't have a default value (Additional info: INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country,helo,flag) VALUES (NOW(),'103.4.94.178','GeoIP','PK','Pakistan','103.4.94.178.pern.pk','4');) - Line: 187 Column: 3 - Code: (null)"
"SMTPD"	"103.4.94.178"	"SENT: 554 . 01 This mail server does not accept connections from Pakistan. If you believe that this failure is in error, please contact the intended recipient via alternate means."

Line 187 is "Call oDB.ExecuteSQL(strSQL)" from the FWBan - function you posted above ...

[palinka]

Changed the code for FWBan with the one above and now there is another error:

Code: Select all

"SMTPD"	"103.4.94.178"	"RECEIVED: EHLO 103.4.94.178.pern.pk"
"ERROR"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Field 'ID' doesn't have a default value (Additional info: INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country,helo,flag) VALUES (NOW(),'103.4.94.178','GeoIP','PK','Pakistan','103.4.94.178.pern.pk','4');)"
"ERROR"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: Execution of SQL statement failed. Error: MySQL: Field 'ID' doesn't have a default value (Additional info: INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country,helo,flag) VALUES (NOW(),'103.4.94.178','GeoIP','PK','Pakistan','103.4.94.178.pern.pk','4');) - Line: 187 Column: 3 - Code: (null)"
"SMTPD"	"103.4.94.178"	"SENT: 554 . 01 This mail server does not accept connections from Pakistan. If you believe that this failure is in error, please contact the intended recipient via alternate means."

Line 187 is "Call oDB.ExecuteSQL(strSQL)" from the FWBan - function you posted above ...

Jeez... That's embarrasing. Field 'ID' should be autoincrement. I just looked at the GitHub readme and it does not list autoincrement as a property. Actually I've been thinking of adding "if not exist create table" to the powershell file.

Anyway, run this sql in phpmyadmin:

Code: Select all

ALTER TABLE hm_fwban MODIFY COLUMN ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY

[hMailserver-User]

Jeez... That's embarrasing. Field 'ID' should be autoincrement. I just looked at the GitHub readme and it does not list autoincrement as a property. Actually I've been thinking of adding "if not exist create table" to the powershell file.

Anyway, run this sql in phpmyadmin:

Code: Select all

ALTER TABLE hm_fwban MODIFY COLUMN ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Done - the hmailserver - part seems to be running now. At least there are currently no errors. I will have an eye on the logs.

Now ... to the powershell - file. There are also some problems in it:

Code: Select all

D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

out-file : Ein Teil des Pfades "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwrulelist.txt" konnte nicht gefunden
werden.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:223 Zeichen:5
+ } | out-file $RuleList
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Get-Content : Der Pfad "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwrulelist.txt" kann nicht gefunden werden,
da er nicht vorhanden ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:225 Zeichen:6
+ $a = Get-Content $RuleList
+      ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\hMailServer\...\fwrulelist.txt:String) [Get-Content], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

out-file : Ein Teil des Pfades "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwduplist.txt" konnte nicht gefunden
werden.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:228 Zeichen:68
+ $ht.keys | where {$ht["$_"] -gt 1} | foreach { write-output $_ } | out-file $Dup ...
+                                                                    ~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Get-Content : Der Pfad "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwduplist.txt" kann nicht gefunden werden, da
er nicht vorhanden ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:230 Zeichen:1
+ Get-Content $DupList | foreach {
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\hMailServer\...e\fwduplist.txt:String) [Get-Content], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

PS C:\Users\Administrator> D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1 > c:\temp\fehler.txt
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

out-file : Ein Teil des Pfades "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwrulelist.txt" konnte nicht gefunden
werden.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:223 Zeichen:5
+ } | out-file $RuleList
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Get-Content : Der Pfad "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwrulelist.txt" kann nicht gefunden werden,
da er nicht vorhanden ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:225 Zeichen:6
+ $a = Get-Content $RuleList
+      ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\hMailServer\...\fwrulelist.txt:String) [Get-Content], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

out-file : Ein Teil des Pfades "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwduplist.txt" konnte nicht gefunden
werden.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:228 Zeichen:68
+ $ht.keys | where {$ht["$_"] -gt 1} | foreach { write-output $_ } | out-file $Dup ...
+                                                                    ~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Get-Content : Der Pfad "D:\hMailServer\Addons\HMSFirewallBan\Deduplicate\fwduplist.txt" kann nicht gefunden werden, da
er nicht vorhanden ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:230 Zeichen:1
+ Get-Content $DupList | foreach {
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\hMailServer\...e\fwduplist.txt:String) [Get-Content], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

The missing Deduplicate directory i have created manually. The $DBErrorLog - variable is not defined? The other errors are over my understanding

[palinka]

Done - the hmailserver - part seems to be running now. At least there are currently no errors. I will have an eye on the logs.

Now ... to the powershell - file. There are also some problems in it:

Code: Select all

D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:61 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Out-File : Das Argument kann nicht an den Parameter "FilePath" gebunden werden, da es NULL ist.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:58 Zeichen:130
+ ... 0]" | out-file $DBErrorLog -append
+                    ~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Out-File], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.OutFileComm
   and

The missing Deduplicate directory i have created manually. The $DBErrorLog - variable is not defined? The other errors are over my understanding

There should not have been a folder for deduplicate. It should be:
$RuleList = "$PSScriptRoot\fwrulelist.txt"
$DupList = "$PSScriptRoot\fwduplist.txt"

Also, $DBErrorLog is not defined means that the file path is missing. Add the missing path to the MySQLQuery function:

Code: Select all

Function MySQLQuery($Query) {
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
	Try {
	  $DBErrorLog = $PSScriptRoot\DBError.log
	  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
	  etc... etc...

I *think* that's what's causing the other issue with the Finally { $Connection.Close() } as well.

[palinka]

And let me know when its working.

[palinka]

I moved the table creation to the powershell script so it automatically created the tables.

[hMailserver-User]

And let me know when its working.

Checked out GitHub so i am on the bleeding edge now - now i got this error:

Code: Select all

In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:47 Characters:31
+ $DBErrorLog = $PSScriptRoot\DBError.log
+ ~~~~~~~~~~~~
Unexpected token "\DBError.log" in expression or statement.
    + CategoryInfo : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

[palinka]

Arrrrggg!

In the mysqlquery function, put the $ in front of DBError.log

Missing the $.... you'd be up and running if it weren't for my typos.

However, there must be a database error still, so when you get that sorted out, let me know what's actually in the error log.

Edit

No! Quotes are missing. Should be

"$PSScriptRoot\DBError.log"

[hMailserver-User]

Arrrrggg!

In the mysqlquery function, put the $ in front of DBError.log

Missing the $.... you'd be up and running if it weren't for my typos.

However, there must be a database error still, so when you get that sorted out, let me know what's actually in the error log.

Edit

No! Quotes are missing. Should be

"$PSScriptRoot\DBError.log"

Code: Select all

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In D:\hMailServer\Addons\HMSFirewallBan\hmsFirewallBan.ps1:62 Zeichen:4
+       $Connection.Close()
+       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

[hMailserver-User]

and from the now created DBError.log (in parts):

Code: Select all

Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird.[0]
19.11.18 21:45:50.92 : ERROR : Unable to run query : 
	SELECT 
		a.idsip,
		a.country
	FROM
	(
		SELECT ipaddress AS idsip, country
			FROM hm_ids 
			GROUP BY ipaddress
			ORDER BY ipaddress ASC
	) AS a
	INNER JOIN
	(
		SELECT ipaddress AS fwbip
			FROM hm_fwban 
			WHERE flag IS NULL OR flag='3' OR flag='4' OR flag='7'
			GROUP BY ipaddress
			ORDER BY ipaddress ASC
	) AS b
	ON a.idsip = b.fwbip
	ORDER BY b.fwbip
 
Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird.[0]
19.11.18 21:45:52.45 : ERROR : Unable to run query : SELECT ipaddress, id FROM hm_fwban WHERE timestamp < '2019-11-18 21:45:00' - interval 30 day AND ban_reason LIKE 'Spamhaus' AND flag IS NULL 

[palinka]

and from the now created DBError.log (in parts):

Code: Select all

Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird.[0]
19.11.18 21:45:50.92 : ERROR : Unable to run query : 
	SELECT 
		a.idsip,
		a.country
	FROM
	(
		SELECT ipaddress AS idsip, country
			FROM hm_ids 
			GROUP BY ipaddress
			ORDER BY ipaddress ASC
	) AS a
	INNER JOIN
	(
		SELECT ipaddress AS fwbip
			FROM hm_fwban 
			WHERE flag IS NULL OR flag='3' OR flag='4' OR flag='7'
			GROUP BY ipaddress
			ORDER BY ipaddress ASC
	) AS b
	ON a.idsip = b.fwbip
	ORDER BY b.fwbip
 
Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird. Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. Der Typ [MySql.Data.MySqlClient.MySqlConnection] kann nicht gefunden werden. Stellen Sie sicher, dass die Assembly, die diesen Typ enthält, geladen wird.[0]
19.11.18 21:45:52.45 : ERROR : Unable to run query : SELECT ipaddress, id FROM hm_fwban WHERE timestamp < '2019-11-18 21:45:00' - interval 30 day AND ban_reason LIKE 'Spamhaus' AND flag IS NULL 

I'm stumped. Can't run because null? I'm not really sure what that means.

The type [MySql.Data.MySqlClient.MySqlConnection] can not be found. Make sure that the assembly containing this type is loaded. It is not possible to call a method for an expression that has the NULL.

I'll look into it. In the meantime, is it affecting anything? The effect of not running both of those queries is not harmful. Firstly, you have no spamhaus entries over 30 days old and if IDS doesn't pick up an entry, who cares? It will pick it up next time or when ever we sort this error out. The actual entries aren't going anywhere.

[palinka]

The type [MySql.Data.MySqlClient.MySqlConnection] can not be found.

You said you re-downloaded the powershell file. Did you forget to change the database variables st the top?

[hMailserver-User]

The type [MySql.Data.MySqlClient.MySqlConnection] can not be found.

You said you re-downloaded the powershell file. Did you forget to change the database variables st the top?

Nope, MySQL and Fireall-Rules are changed to fit my setup ...

[hMailserver-User]

So, it seems that i have found the problem: the mysql-connector-net must be installed. Which wasn't on my setup.
I think you should ad this to the instructions on GitHub @palinka ...

Going to check out again a fresh copy from GitHub and set it up again from scratch

[palinka]

Ok. Awesome. I will update the instructions. Thanks for the heads up.

[hMailserver-User]

Ok. Awesome. I will update the instructions. Thanks for the heads up.

Hiho - its me again
hmsFirewallBan.ps1 produces errors like this (in the DBError.log):

Code: Select all

19.11.22 00:30:01.80 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'139.59.173.249','IDS','United Kingdom',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value"[0]
19.11.22 00:30:01.87 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'146.185.143.14','IDS','Netherlands',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value"[0]
19.11.22 00:30:01.93 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'178.62.52.237','IDS','United Kingdom',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value"[0]

[palinka]

Hiho - its me again
hmsFirewallBan.ps1 produces errors like this (in the DBError.log):

Code: Select all

19.11.22 00:30:01.80 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'139.59.173.249','IDS','United Kingdom',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'countrycode' doesn't have a default value"[0]

This is due to MySQL table hm_fwban : column "countrycode" REQUIRING a default value. Countrycode is basically an orphan left over from the very beginning, but abandoned later. I was lazy in not removing it. To permanently get rid of this error, you need to do a couple of things. First, you need to delete the column "countrycode" from MySQL table hm_fwban:

Code: Select all

ALTER TABLE hm_fwban DROP COLUMN countrycode

Then alter Function FWBan to delete references to "countrycode".

Code: Select all

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,country,helo,flag) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("country") & "','" & sHELO & "','4');"
   Call oDB.ExecuteSQL(strSQL)
End Function

Although I'm not sure why you're getting errors for that. I have the same MySQL query (with countrycode) and it works without errors. Off the top of my head, either we are using different column variables - which is a possibility due to all the changes recently - or your geoip calls are not producing countrycode data (unlikely if you're getting country name info). Either way, the 2 things above should fix it.

[hMailserver-User]

This is due to MySQL table hm_fwban : column "countrycode" REQUIRING a default value. Countrycode is basically an orphan left over from the very beginning, but abandoned later. I was lazy in not removing it. To permanently get rid of this error, you need to do a couple of things. First, you need to delete the column "countrycode" from MySQL table hm_fwban:

Tthere seems to also a problem with the field "helo":

Code: Select all

19.11.22 13:35:01.16 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'104.131.107.63','IDS','United States',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value"[0]
19.11.22 13:35:01.25 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'139.59.173.249','IDS','United Kingdom',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value"[0]
19.11.22 13:35:01.31 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'146.185.143.14','IDS','Netherlands',NULL); 

[hMailserver-User]

And: it seems that the above changes have broken the webinterface. Stats are working, but details for a specitic stat returns a "no result". No results for search term "Russi", no No results for search term "2019-11-22" and so on.

[palinka]

Tthere seems to also a problem with the field "helo":

Code: Select all

19.11.22 13:35:01.16 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'104.131.107.63','IDS','United States',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value"[0]
19.11.22 13:35:01.25 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'139.59.173.249','IDS','United Kingdom',NULL); 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value" Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Field 'helo' doesn't have a default value"[0]
19.11.22 13:35:01.31 : ERROR : Unable to run query : INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag) VALUES (NOW(),'146.185.143.14','IDS','Netherlands',NULL); 

In phpmyadmin, go to hm_fwban > structure > click "change" on the helo line > check the NULL box > hit save.

This is very strange. I looked at my own setup and I have the same thing (NOT NULL attribute for column "helo"), but I haven't seen these errors. Yet, IDS entries (with no helo) get successfully added to hm_fwban. I'll look into that.

[palinka]

And: it seems that the above changes have broken the webinterface. Stats are working, but details for a specitic stat returns a "no result". No results for search term "Russi", no No results for search term "2019-11-22" and so on.

Do you have the latest? All the php does is read what is in the database. Everything is working for me.

Please post the url with domain deleted. eg /search.php?submit=search&ip=blahblahblah

AND the php error. Very important they come together.

EDIT - With respect to the changes above - I assume you're referring to dropping "countrycode" - I also had that column on my personal setup and removed it immediately after advising you to do it. No queries in the php interface reference countrycode, and I'm not having any issues with "no result". I really need more info to diagnose your issue.

[hMailserver-User]

i try to visualize what i mean:

[palinka]

i try to visualize what i mean:

I compared my own search.php to the GitHub version and they are exactly the same. Can you re-download the www from GitHub and overwrite everything except cred.php and .htaccess?

[palinka]

If it still doesn't work, run these queries in phpmyadmin and report any errors:

GeoIP:

Code: Select all

SELECT
		a.tsf,
		a.ipaddress,
		a.ban_reason,
		a.country,
		a.flag,
		a.helo,
		b.returnhits
FROM
(
	SELECT DATE_FORMAT(timestamp, '%y/%m/%d %H:%i.%s') as tsf, timestamp, ipaddress, ban_reason, country, flag, helo 
	FROM hm_fwban 
	WHERE (timestamp LIKE '%%' OR ipaddress LIKE '%%' OR ban_reason LIKE '%%' OR countrycode LIKE '%%' OR country LIKE '%%' OR helo LIKE '%%') AND ban_reason LIKE 'GeoIP' 
	ORDER BY timestamp DESC 
) AS a
LEFT JOIN
(
	SELECT COUNT(ipaddress) AS returnhits, ipaddress, timestamp
	FROM hm_fwban_rh
	GROUP BY ipaddress
	ORDER BY timestamp DESC
) AS b
ON a.ipaddress = b.ipaddress
ORDER BY a.tsf DESC

"Lithuania":

Code: Select all

	SELECT
		a.tsf,
		a.ipaddress,
		a.ban_reason,
		a.country,
		a.flag,
		a.helo,
		b.returnhits
	FROM
	(
		SELECT DATE_FORMAT(timestamp, '%y/%m/%d %H:%i.%s') as tsf, timestamp, ipaddress, ban_reason, country, flag, helo 
		FROM hm_fwban 
		WHERE (timestamp LIKE '%Lithuania%' OR ipaddress LIKE '%Lithuania%' OR ban_reason LIKE '%Lithuania%' OR countrycode LIKE '%Lithuania%' OR country LIKE '%Lithuania%' OR helo LIKE '%Lithuania%') 
		ORDER BY timestamp DESC 
	) AS a
	LEFT JOIN
	(
		SELECT COUNT(ipaddress) AS returnhits, ipaddress, timestamp
		FROM hm_fwban_rh
		GROUP BY ipaddress
		ORDER BY timestamp DESC
	) AS b
	ON a.ipaddress = b.ipaddress
	ORDER BY a.tsf DESC

These queries are exactly as presented to mysql after the variables are filled in by php. I only left off "LIMIT ".$offset.", ".$no_of_records_per_page;" because its unnecessary to see if the query is working. If you get errors after running either of these queries, report them here.

[hMailserver-User]

If it still doesn't work, run these queries in phpmyadmin and report any errors:

These queries are exactly as presented to mysql after the variables are filled in by php. I only left off "LIMIT ".$offset.", ".$no_of_records_per_page;" because its unnecessary to see if the query is working. If you get errors after running either of these queries, report them here.

Have now done that - here are the results for the GeoIP - Query:

Code: Select all

#1054 - Unknown column 'countrycode' in 'where clause'

The second one (Lithuana) runs without error ...

[palinka]

Jeez, the answer was in my post. The query DOES contain countrycode, which I completely overlooked.

When I get home I'll go through every page and make sure it's removed completely. I'll let you know when the changes are uploaded.

[hMailserver-User]

Jeez, the answer was in my post. The query DOES contain countrycode, which I completely overlooked.

When I get home I'll go through every page and make sure it's removed completely. I'll let you know when the changes are uploaded.

thx!

[hMailserver-User]

Removed the "countrycode" - thing from search-php (2 times) and now the webif is working again ...
When you updated GitHub with all the latest corrections i will start from scratch again to test
Thx

[palinka]

Ok give it a try now. Also, since you're starting from scratch, make sure you also take the powershell script. I updated the create tables query after scrutinizing it.

I did not touch eventhandlers.vbs but I think I already updated everything we went through before.

[hMailserver-User]

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

[palinka]

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

I found a problem (I think)

Code: Select all

	#	If previously marked safe (firewall rule already removed), update flag to safe
	ElseIf ($FlagSafe -match 6){
		$Query = "UPDATE hm_fwban SET flag = 6 WHERE id='$ID'"	
		MySQLQuery $Query
	}

Let me think about this. It doesn't make sense now, but I must have had a reason for it.

By the way, it does work for me. I just tested it and the IP I selected to test was deleted and flag updated to "6".

[hMailserver-User]

Could it have something to do with this:

Code: Select all

19.11.23 08:30:02.62 : ERROR : Unable to run query : INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES (' 2019-11-23 08:29:31', '45.95.35.35') 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Incorrect datetime value: '' for column 'timestamp' at row 1"[0]

I have noticed that since the removing fom "contrycode" the graphics for "hits per day from inception" and "Total blocks per day (block frequency)" no longer be filled. Is that possible?

[hMailserver-User]

Mhh - it must have something to do with "duplicate ips". The IP has triggered IDS, i have marked it for release and it have been removed from firewall.
I have made no screenshot but the ip (its a 5 minute check from uptimerobot.com) after some time was shown 2 times under duplicate ip's. 1 time with release status YES and at the same time with release status NO. Because of that the ip is marked for blocking a second time (which happens). After some time it have been removed from duplicate ip's, is shown as released but its not - firewall blocks it.
Possible?

EDIT - it happend again - here is what i mean:

[Dravion]

This is not a IP SQL Insert Error.
It's an Timestamp insert error.
I assume you are in the German, central European Time Zone (CET) and Palinka doesn't check the correct Timestamp format for any location of the World. The Format might be differen't in it's Home location.

[palinka]

Could it have something to do with this:

Code: Select all

19.11.23 08:30:02.62 : ERROR : Unable to run query : INSERT INTO hm_fwban_rh (timestamp, ipaddress) VALUES (' 2019-11-23 08:29:31', '45.95.35.35') 
Ausnahme beim Aufrufen von "Fill" mit 2 Argument(en):  "Incorrect datetime value: '' for column 'timestamp' at row 1"[0]

I have noticed that since the removing fom "contrycode" the graphics for "hits per day from inception" and "Total blocks per day (block frequency)" no longer be filled. Is that possible?

The graphics only show everything before today. So you must have data from yesterday - at least - in order to populate the graphs.

The date time format is universal in mysql. The query function to fill "timestamp" is "NOW()" so it should be independent of timezone or formatting.

I'm using MariaDB. Is there any difference for timestamp formatting with mysql?

Edit- I just noticed the error was on hm_fwban_rh. That's significant. The date time format comes from the powershell script which does not adjust for locality. I don't know how to fix that other than matching the format to your locality. Currently the format is "yyyy-MM-dd HH:mm:ss". What is your default format?

I'll see if I can figure out localization, but the quickest fix is to just change the hard-coded format.

[palinka]

Mhh - it must have something to do with "duplicate ips". The IP has triggered IDS, i have marked it for release and it have been removed from firewall.
I have made no screenshot but the ip (its a 5 minute check from uptimerobot.com) after some time was shown 2 times under duplicate ip's. 1 time with release status YES and at the same time with release status NO. Because of that the ip is marked for blocking a second time (which happens). After some time it have been removed from duplicate ip's, is shown as released but its not - firewall blocks it.
Possible?

EDIT - it happend again - here is what i mean:

If you release an IP, the firewall rule gets removed but entry remains in the database. Then, if that same IP returns to your hmailserver and triggers the firewall ban, it gets added to the database as a separate incident and a new firewall rule is created. Therefore, duplicates in that case are perfectly normal. The first instance was released and the second has not. Both are shown. One with NO and one with YES as release status.

When you mark an IP as SAFE, the firewall rule should be deleted and all future bans should be ignored (marked SAFE with no additional firewall rules created). You could still have duplicates on that IP but there should be no blocking by firewall. I think you pointed out a bug for SAFE yesterday and I'm working on a solution for it. Still trying to figure out the best logic for it. When I get it fixed I'll let you know.

In the meantime, if there is a persistent false positive, you could exclude it from testing in eventhandlers.vbs the same as you exclude localhost and LAN IPs.

[palinka]

This is not a IP SQL Insert Error.
It's an Timestamp insert error.
I assume you are in the German, central European Time Zone (CET) and Palinka doesn't check the correct Timestamp format for any location of the World. The Format might be differen't in it's Home location.

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)?