Block IPs

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 15:36

My first tries were with PHP 7.0

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 15:38

I clicked on the link, I got
Access forbidden!
You don't have permission to access the requested directory. There is either no index document or the directory is read-protected.

If you think this is a server error, please contact the webmaster.

Error 403
hmsfirewallbandemo.ddns.net
Apache/2.4.26 (Win32) OpenSSL/1.0.2l PHP/5.6.31

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 15:40

getting "host cannot be resolved" in the browser.
Is this hosted on a synology NAS?

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 15:42

Also can you please provide a link or info on learning google charts that you are using?

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

Re: Block IPs

Post by palinka » 2020-02-16 15:43

Oops. I accidentally copied .htaccess there. I just deleted it but give it a minute - I just restarted for windows update.

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

Re: Block IPs

Post by palinka » 2020-02-16 15:46

eliassal wrote:
2020-02-16 15:42
Also can you please provide a link or info on learning google charts that you are using?
Just go to the Google charts pages. They have examples. That's how I worked it out.

Honestly, I think it would be a lot easier to just fix php/SQL.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 16:00

I tried both versions 5.3 and 7, it is not going through

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-16 20:57

Yes the demo is working fine I should say that it is very nice and useful, we see good ideas behind the desihgner
:wink:
I activated debug on the php.ini, now I can this error
Parse error: syntax error, unexpected '$pdo' (T_VARIABLE), expecting ',' or ';' in C:\inetpub\wwwroot\PHPBanAdmin\functions.php on line 7
which is
$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
As I told you yesterday, the app is not reaching sql when I triggered monitoring of sql server profiler

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

Re: Block IPs

Post by palinka » 2020-02-16 21:20

eliassal wrote:
2020-02-16 20:57
Yes the demo is working fine I should say that it is very nice and useful, we see good ideas behind the desihgner
:wink:
I activated debug on the php.ini, now I can this error
Parse error: syntax error, unexpected '$pdo' (T_VARIABLE), expecting ',' or ';' in C:\inetpub\wwwroot\PHPBanAdmin\functions.php on line 7
which is
$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
As I told you yesterday, the app is not reaching sql when I triggered monitoring of sql server profiler
Google around for a new connection string and hard code it into functions.php. For example: https://stackoverflow.com/questions/311 ... 7#31132520

Code: Select all

$pdo = new PDO("sqlsrv:Server=SQL\SC2K12;Database=hmailserver;", myuserrun, mypassword);

-or-

$pdo = new PDO("sqlsrv:Server=".$Database['host'].";".$Database['dbname'].";", $Database['username'], $Database['password']);
If you look around there are other variations. This keeps pointing back to connection, so I would definitely have a closer look at the connection string. There are plenty of variations you could use.

And also use PHP 7 with the correct SQL driver.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 00:32

Wooooooow, I discovered the issue, my bad

On MS site, I did not notice that there is versions when having php7.dll or php7ts.dll (I really don't know what or from where this php7ts.dll come fom)

64-bit php_sqlsrv_72_nts.dll
64-bit php_pdo_sqlsrv_72_nts.dll 7.2 no Use with 64-bit php7.dll

64-bit php_sqlsrv_72_ts.dll
64-bit php_pdo_sqlsrv_72_ts.dll 7.2 yes 64-bit php7ts.dll

I changed the php.ini file to use

extension="C:\Program Files\PHP\v7.2\ext\php_sqlsrv_72_nts_x64.dll"
extension="C:\Program Files\PHP\v7.2\ext\php_pdo_sqlsrv_72_nts_x64.dll"

and the site displayed like a charm, thanks for your help. I will walk through all functionalities and give you back my feedback

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

Re: Block IPs

Post by palinka » 2020-02-17 00:54

OK cool.

Today I uploaded a "major" change to github. I suggest starting from scratch since there is a new database table plus one altered table. There's no point in trying to use the upgrade scripts when you have very little data and you never really got started with it anyway.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 01:51

Palinka,
Clicking on "IDS" link. I find 2 records 1 with 8 hits.

How can I ban the one with 8 hits from web site? Or it should be through Powershell script?, can you give me an idea on the workflow for each function (link)

Should the site runs on the server where hMailServer is running in order the site works correctly or can we run it from anywhere.

The version working in my environment is on one of my desktops not on the hMailServer

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 01:56

search was returning nothing, I fixed it.
in sql server "Like operator" does not work with columns of type Datetime, it should be converted to a string, I replaced the line

Code: Select all

WHERE timestamp LIKE '%{$search}%'
by

Code: Select all

WHERE (convert(nvarchar(50), timestamp, 126) LIKE '%{$search}%'
or

Code: Select all

CONVERT(VARCHAR, DateTimeColumn , 120)
the page started to return results

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 02:05

Also as a general note, Stored procedures are more efficient and easy to maintain than T-sql statements distributed in all pages, much more pefrormant andm make code more easy to read

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 02:06

I will stop now, go to bed, time to sleep, my wife shouting :D
I will continue tomorrow. Tell me where are based, in Europe or US?

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

Re: Block IPs

Post by palinka » 2020-02-17 02:08

eliassal wrote:
2020-02-17 01:56
search was returning nothing, I fixed it.
in sql server "Like operator" does not work with columns of type Datetime, it should be converted to a string, I replaced the line

Code: Select all

WHERE timestamp LIKE '%{$search}%'
by

Code: Select all

WHERE (convert(nvarchar(50), timestamp, 126) LIKE '%{$search}%'
or

Code: Select all

CONVERT(VARCHAR, DateTimeColumn , 120)
the page started to return results
It works in mysql. Funny that lcamilo, the guy that added the mssql queries did not bring this up. Could this be an issue specific to a particular version of SQL server?

What you should do is create a function for it and pass it on to me so I can incorporate it into the project properly.

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

Re: Block IPs

Post by palinka » 2020-02-17 02:08

eliassal wrote:
2020-02-17 02:06
I will stop now, go to bed, time to sleep, my wife shouting :D
I will continue tomorrow. Tell me where are based, in Europe or US?
US and A....

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 02:17

ok, i will write the function tomorow
I have tested like operator with versions 2005, 2008, 2008R2, 2012, 2014 and 2016. I will check if it is the same thing for version 2017 and 2019

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 02:18

Do you mean a php function or T-SQL function?

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 02:20

You did not answer this question
Should the site runs on the server where hMailServer is running in order the site works correctly or can we run it from anywhere?

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

Re: Block IPs

Post by palinka » 2020-02-17 02:43

eliassal wrote:
2020-02-17 01:51
Palinka,
Clicking on "IDS" link. I find 2 records 1 with 8 hits.
Did you set up a scheduled task to run hmsFirewallBan.ps1 every 5 minutes? If you did not, you need to. If you did, then the last 6 hits came within the last 5 minute interval.
How can I ban the one with 8 hits from web site? Or it should be through Powershell script?, can you give me an idea on the workflow for each function (link)
You can't. It's done via the powershell script on a 5 minute interval.
Should the site runs on the server where hMailServer is running in order the site works correctly or can we run it from anywhere.
Before today, it could be anywhere. However, today I added a script that creates several data cache files for use by charts and other things (to speed things up). The www folder needs to be writable and accessible to the powershell script, so it needs to be on the same machine as the powershell components.
The version working in my environment is on one of my desktops not on the hMailServer
Everything should be on the same machine as hmailserver. After all, the firewall doing the work is there. :D

I think netsh works on remote hosts, so theoretically you could put everything on a different machine or multiple machines. But writing the data cache files would require smb or ftp or some other method to get the file moved. Also you'd have to modify the script, of course.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 13:46

Today I uploaded a "major" change to github. I suggest starting from scratch since there is a new database table plus one altered table. There's no point in trying to use the upgrade scripts when you have very little data and you never really got started with it anyway.
Palinka, I visited the github, in hmsFirewallBan.ps1, no table added or modified?
hmsFirewallBanDBSetup.ps1 Fixed all other queries 6 days ago
which is the I used to create the DB objects.

I have alreay ptr field in hm_fwban table I don't need the

I checked the downloaded zip file I downloade last week , I have already the 2 files
hmsRetroAddPTR.ps1 & hmsRetroAddRuleName.ps1
but did not apply them yet

I compared my EventHandlers.vbs in my download which I used in hmailserver and the one on github, they have exactly the same number of code lines.

So should't I only apply
hmsRetroAddPTR.ps1 & hmsRetroAddRuleName.ps1
BUT commenting the line?
$Query = "ALTER TABLE hm_fwban ADD ptr VARCHAR(192) NULL;"

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

Re: Block IPs

Post by palinka » 2020-02-17 14:06

Just start from scratch. Trust me.

Db creation is in hmsFirewallBanDBSetup.ps1.

I'll try to update the installation instruction today.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 14:24

Palinka, it is the same exact code that I have for creating the tables, only 3 tables and 1 function, here is the copy from Githyub, no new table and ptr field is already there, github says the file was updated 6 days ago

Code: Select all

If ($DatabaseType -eq "MSSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban')
		BEGIN
			CREATE TABLE hm_fwban (
				ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				ipaddress varchar NOT NULL,
				timestamp datetime NOT NULL,
				ban_reason varchar(192) DEFAULT NULL,
				country varchar(192) DEFAULT NULL,
				flag int DEFAULT NULL,
				helo varchar(192) DEFAULT NULL,
				ptr varchar(192) DEFAULT NULL,
				rulename varchar(192) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_rh')
		BEGIN
			CREATE TABLE hm_fwban_rh (
				id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				timestamp datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				ipaddress varchar(15) NOT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_ids table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_ids')
		BEGIN
			CREATE TABLE hm_ids (
				timestamp datetime NOT NULL,
				ipaddress varchar(15) NOT NULL PRIMARY KEY,
				hits int NOT NULL,
				country varchar(64) DEFAULT NULL,
				helo varchar(128) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#Create MSSQL Function equivalent to INET_ATON() from MySQL
	#first drop if exists
	$Query = "
	IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'ipStringToInt')
		DROP FUNCTION dbo.ipStringToInt 
	"
	RunSQLQuery $Query
	#then create
	$Query = "
		CREATE FUNCTION dbo.ipStringToInt 
		( 
			@ip CHAR(15) 
		) 
		RETURNS INT 
		AS 
		BEGIN 
			DECLARE @rv INT, 
				@o1 INT, 
				@o2 INT, 
				@o3 INT, 
				@o4 INT, 
				@base INT 
		
			SELECT 
				@o1 = CONVERT(INT, PARSENAME(@ip, 4)), 
				@o2 = CONVERT(INT, PARSENAME(@ip, 3)), 
				@o3 = CONVERT(INT, PARSENAME(@ip, 2)), 
				@o4 = CONVERT(INT, PARSENAME(@ip, 1)) 
		
			IF (@o1 BETWEEN 0 AND 255) 
				AND (@o2 BETWEEN 0 AND 255) 
				AND (@o3 BETWEEN 0 AND 255) 
				AND (@o4 BETWEEN 0 AND 255) 
			BEGIN      
				SELECT @base = CASE 
					WHEN @o1 < 128 THEN 
						(@o1 * 16777216) 
					ELSE 
						-(256 - @o1) * 16777216 
					END 
		
				SET @rv = @base +  
					(@o2 * 65536) +  
					(@o3 * 256) + 
					(@o4) 
			END 
			ELSE 
				SET @rv = -1 
			RETURN @rv 
		END
	"
	RunSQLQuery $Query


}

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

Re: Block IPs

Post by palinka » 2020-02-17 16:05

eliassal wrote:
2020-02-17 14:24
Palinka, it is the same exact code that I have for creating the tables, only 3 tables and 1 function, here is the copy from Githyub, no new table and ptr field is already there, github says the file was updated 6 days ago

Code: Select all

If ($DatabaseType -eq "MSSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban')
		BEGIN
			CREATE TABLE hm_fwban (
				ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				ipaddress varchar NOT NULL,
				timestamp datetime NOT NULL,
				ban_reason varchar(192) DEFAULT NULL,
				country varchar(192) DEFAULT NULL,
				flag int DEFAULT NULL,
				helo varchar(192) DEFAULT NULL,
				ptr varchar(192) DEFAULT NULL,
				rulename varchar(192) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_rh')
		BEGIN
			CREATE TABLE hm_fwban_rh (
				id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				timestamp datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				ipaddress varchar(15) NOT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_ids table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_ids')
		BEGIN
			CREATE TABLE hm_ids (
				timestamp datetime NOT NULL,
				ipaddress varchar(15) NOT NULL PRIMARY KEY,
				hits int NOT NULL,
				country varchar(64) DEFAULT NULL,
				helo varchar(128) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#Create MSSQL Function equivalent to INET_ATON() from MySQL
	#first drop if exists
	$Query = "
	IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'ipStringToInt')
		DROP FUNCTION dbo.ipStringToInt 
	"
	RunSQLQuery $Query
	#then create
	$Query = "
		CREATE FUNCTION dbo.ipStringToInt 
		( 
			@ip CHAR(15) 
		) 
		RETURNS INT 
		AS 
		BEGIN 
			DECLARE @rv INT, 
				@o1 INT, 
				@o2 INT, 
				@o3 INT, 
				@o4 INT, 
				@base INT 
		
			SELECT 
				@o1 = CONVERT(INT, PARSENAME(@ip, 4)), 
				@o2 = CONVERT(INT, PARSENAME(@ip, 3)), 
				@o3 = CONVERT(INT, PARSENAME(@ip, 2)), 
				@o4 = CONVERT(INT, PARSENAME(@ip, 1)) 
		
			IF (@o1 BETWEEN 0 AND 255) 
				AND (@o2 BETWEEN 0 AND 255) 
				AND (@o3 BETWEEN 0 AND 255) 
				AND (@o4 BETWEEN 0 AND 255) 
			BEGIN      
				SELECT @base = CASE 
					WHEN @o1 < 128 THEN 
						(@o1 * 16777216) 
					ELSE 
						-(256 - @o1) * 16777216 
					END 
		
				SET @rv = @base +  
					(@o2 * 65536) +  
					(@o3 * 256) + 
					(@o4) 
			END 
			ELSE 
				SET @rv = -1 
			RETURN @rv 
		END
	"
	RunSQLQuery $Query


}
I think you need to refresh your browser or something.

https://github.com/palinkas-jo-reggelt/ ... BSetup.ps1

Code: Select all

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

.SYNOPSIS
	Database Setup

.DESCRIPTION

.FUNCTIONALITY

.NOTES

.EXAMPLE

#>

# Include required files

Try {
	.("$PSScriptRoot\Config.ps1")
	.("$PSScriptRoot\CommonCode.ps1")
}
Catch {
	Write-Output "Error while loading supporting PowerShell Scripts" | Out-File -Path "$PSScriptRoot\PSError.log"
}

If ($DatabaseType -eq "MSSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban')
		BEGIN
			CREATE TABLE hm_fwban (
				ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				ipaddress varchar NOT NULL,
				timestamp datetime NOT NULL,
				ban_reason varchar(192) DEFAULT NULL,
				country varchar(192) DEFAULT NULL,
				flag int DEFAULT NULL,
				helo varchar(192) DEFAULT NULL,
				ptr varchar(192) DEFAULT NULL,
				rulename varchar(192) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_rh')
		BEGIN
			CREATE TABLE hm_fwban_rh (
				ipid INT(22) NULL,
				timestamp timestamp NOT NULL,
				ipaddress varchar(15) NOT NULL
			)
		END;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_blocks_ip table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_blocks_ip')
		BEGIN
			CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
			  id INT(22) NOT NULL AUTO_INCREMENT,
			  ipaddress varchar(15) NOT NULL UNIQUE,
			  hits INT(8),
			  lasttimestamp timestamp NOT NULL,
			  PRIMARY KEY (id)
			)
		END;
	"
	RunSQLQuery $Query

	#	Create hm_ids table if it doesn't exist
	$Query = "
	IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_ids')
	BEGIN
		CREATE TABLE hm_ids (
			timestamp datetime NOT NULL,
			ipaddress varchar(15) NOT NULL PRIMARY KEY,
			hits int NOT NULL,
			country varchar(64) DEFAULT NULL,
			helo varchar(128) DEFAULT NULL
		)
	END;
	"
	RunSQLQuery $Query
}

If ($DatabaseType -eq "MYSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban (
		ID int(11) NOT NULL AUTO_INCREMENT,
		ipaddress varchar(192) NOT NULL,
		timestamp datetime NOT NULL,
		ban_reason varchar(192) DEFAULT NULL,
		country varchar(192) DEFAULT NULL,
		flag int(1) DEFAULT NULL,
		helo varchar(192) DEFAULT NULL,
		ptr varchar(192) DEFAULT NULL,
		rulename varchar(192) DEFAULT NULL,
		PRIMARY KEY (ID),
		UNIQUE KEY ID (ID)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban_rh (
		  ipid INT(22) NULL,
		  timestamp timestamp NOT NULL,
		  ipaddress varchar(15) NOT NULL,
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query

	#	Create hm_fwban_blocks_ip table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
		  id INT(22) NOT NULL AUTO_INCREMENT,
		  ipaddress varchar(15) NOT NULL UNIQUE,
		  hits INT(8),
		  lasttimestamp timestamp NOT NULL,
		  PRIMARY KEY (id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query

	#	Create hm_ids table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_ids (
		  timestamp datetime NOT NULL,
		  ipaddress varchar(15) NOT NULL,
		  hits int(1) NOT NULL,
		  country varchar(64) DEFAULT NULL,
		  helo varchar(128) DEFAULT NULL,
		  PRIMARY KEY (ipaddress),
		  UNIQUE KEY ipaddress (ipaddress)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query
}


eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 16:28

Oh my god, I was at
https://github.com/lcamilo/hMailServer-Firewall-Ban

all files I used where downloaded from there

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

Re: Block IPs

Post by palinka » 2020-02-17 16:58

There's a new user variable in Config.ps1. You should start from scratch. I'm warning you now. I won't be able to help much if you're mixing and matching files and database tables without understanding the reason why they're there in the first place.

Also, every upgrade should be a complete upgrade. In other words - replace ALL files because they are interconnected.

lcamilo
New user
New user
Posts: 10
Joined: 2011-09-09 23:27

Re: Block IPs

Post by lcamilo » 2020-02-17 17:26

palinka wrote:
2020-02-17 02:08
eliassal wrote:
2020-02-17 01:56
search was returning nothing, I fixed it.
in sql server "Like operator" does not work with columns of type Datetime, it should be converted to a string, I replaced the line

Code: Select all

WHERE timestamp LIKE '%{$search}%'
by

Code: Select all

WHERE (convert(nvarchar(50), timestamp, 126) LIKE '%{$search}%'
or

Code: Select all

CONVERT(VARCHAR, DateTimeColumn , 120)
the page started to return results
It works in mysql. Funny that lcamilo, the guy that added the mssql queries did not bring this up. Could this be an issue specific to a particular version of SQL server?

What you should do is create a function for it and pass it on to me so I can incorporate it into the project properly.
Excuse me! It was my fault! : oops:
I don't think I tested it correctly or didn't understand how this search should work, perhaps because my data mass was too small and I didn't pay the necessary attention.

I will correct this and send a pull request as soon as possible.
Last edited by lcamilo on 2020-02-17 17:33, edited 1 time in total.

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

Re: Block IPs

Post by palinka » 2020-02-17 17:30

lcamilo wrote:
2020-02-17 17:26
Excuse me! It was my fault! : oops:
I don't think I tested it correctly or didn't understand how this search should work, perhaps because my data mass was too small and I didn't pay the necessary attention.

I will correct this and send a receipt request as soon as possible.

Code: Select all

WHERE timestamp LIKE '%{$search}%'
Should be:

Code: Select all

WHERE timestamp LIKE '{$search}%'
No % on the left side forces it to look at DATE.

lcamilo
New user
New user
Posts: 10
Joined: 2011-09-09 23:27

Re: Block IPs

Post by lcamilo » 2020-02-17 17:32

lcamilo wrote:
2020-02-17 17:26
palinka wrote:
2020-02-17 02:08
eliassal wrote:
2020-02-17 01:56
search was returning nothing, I fixed it.
in sql server "Like operator" does not work with columns of type Datetime, it should be converted to a string, I replaced the line

Code: Select all

WHERE timestamp LIKE '%{$search}%'
by

Code: Select all

WHERE (convert(nvarchar(50), timestamp, 126) LIKE '%{$search}%'
or

Code: Select all

CONVERT(VARCHAR, DateTimeColumn , 120)
the page started to return results
It works in mysql. Funny that lcamilo, the guy that added the mssql queries did not bring this up. Could this be an issue specific to a particular version of SQL server?

What you should do is create a function for it and pass it on to me so I can incorporate it into the project properly.
Excuse me! It was my fault! : oops:
I don't think I tested it correctly or didn't understand how this search should work, perhaps because my data mass was too small and I didn't pay the necessary attention.

I will correct this and send a receipt request as soon as possible.
Wait, I tested and it is working. See:

Image
Last edited by lcamilo on 2020-02-17 17:38, edited 1 time in total.

lcamilo
New user
New user
Posts: 10
Joined: 2011-09-09 23:27

Re: Block IPs

Post by lcamilo » 2020-02-17 17:36

lcamilo wrote:
2020-02-17 17:32
lcamilo wrote:
2020-02-17 17:26
palinka wrote:
2020-02-17 02:08


It works in mysql. Funny that lcamilo, the guy that added the mssql queries did not bring this up. Could this be an issue specific to a particular version of SQL server?

What you should do is create a function for it and pass it on to me so I can incorporate it into the project properly.
Excuse me! It was my fault! : oops:
I don't think I tested it correctly or didn't understand how this search should work, perhaps because my data mass was too small and I didn't pay the necessary attention.

I will correct this and send a receipt request as soon as possible.
Wait, I tested and is working. See:

Image
The final query is:

Code: Select all

SELECT Count(*) AS count 
FROM   hm_fwban 
WHERE  ( Cast(timestamp AS DATE) LIKE '%2020-02-10%' 
          OR ipaddress LIKE '%2020-02-10%' 
          OR ban_reason LIKE '%2020-02-10%' 
          OR country LIKE '%2020-02-10%' 
          OR helo LIKE '%2020-02-10%' 
          OR ptr LIKE '%2020-02-10%' ) 

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 17:38

Ok, don't worry, I downlaoded your version, deleted old tables, I noticed the existance of the new variable which I repalced with my iis path as follows

$wwwFolder = "P:\inetpub\wwwroot\PHPBanAdmin"

I have just tried to run the
hmsFirewallBanDBSetup.ps1
script but getting a strange behavior, it is able to include config.ps1 but not commoncode.ps1
in terminal and ise editor

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 17:46

I solved the strange issue, the file was blocked, I unblocked it
Any reason this specific security on this file?

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 17:56

Palinka, there is a typo in the script commoncode.ps1

Code: Select all

Function RunSQLQuery($Query){
    If ($DatabaseType -eq "MYSQL") {
        MySQLQuery($Query)
    } ElseIf ($DatabaseType -eq "MSSQL"){
        [b]MySQLQuery[/b]($Query)
    } Else {
        Out-Null
    }
}

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 18:43

Lot of issues in the new hmsFirewallBanDBSetup.ps1 and CommonCode.ps1

Bugs

1 -No need for the word "port=" in the connection string
;port=" + $SQLPort, only ,number
,427

2 - what is this for in mssql runquery which I commented

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

3 - Also, you can Cannot specify a column width on data type int as in lines

ipid INT(22) NULL,
hits INT(8), (table hm_fwban_blocks_ip)

4 - Several errors in creating hm_fwban_rh and 'hm_fwban_blocks_ip, I have the impression you have copied/pasted from mysql without updating

I replaced it from
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_blocks_ip')
BEGIN
CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
id INT(22) NOT NULL AUTO_INCREMENT,
ipaddress varchar(15) NOT NULL UNIQUE,
hits INT(8),
lasttimestamp timestamp NOT NULL,
PRIMARY KEY (id)
)
END;

To

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_ids')
BEGIN
CREATE TABLE hm_ids (
timestamp datetime NOT NULL,
ipaddress varchar(15) NOT NULL PRIMARY KEY,
hits int NOT NULL,
country varchar(64) DEFAULT NULL,
helo varchar(128) DEFAULT NULL
)
END;

5 You cannot do this in sql

CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip


Do you want me to share the updated 2 files which are working fine now with you?

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

Re: Block IPs

Post by palinka » 2020-02-17 19:18

eliassal wrote:
2020-02-17 18:43
Do you want me to share the updated 2 files which are working fine now with you?
Post it here in forum code block.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 19:25

so Palinke, I did everyhting from scratch, tables are created , events are working fine in hMaiserver,
I dumped the data in the tables before starting from scratch, I am thinking of populating the ids table manullay then fire hmsFirewallBan.ps1, of course I will take care of having those tables clean from new structure perspectives
but tell me, the other scripts in the directory, should I run them as well and when?

BlockCount.ps1
hmsConsolidateRules.ps1
hmsDuplicateRuleFinder.ps1
hmsRetroAddBlocksIPTable.ps1
hmsRetroAddPTR.ps1
hmsRetroAddRuleName.ps1
hmsRetroConsolidateRules.ps1
hmsUpdateChartTables.ps1

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 19:28

Here is the code for hmsFirewallBanDBSetup.ps1

Code: Select all

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

.SYNOPSIS
	Database Setup

.DESCRIPTION

.FUNCTIONALITY

.NOTES

.EXAMPLE

#>

# Include required files

Try {
	.("$PSScriptRoot\Config.ps1")
	.("$PSScriptRoot\CommonCode.ps1")
}
Catch {
	Write-Output "Error while loading supporting PowerShell Scripts" | Out-File -Path "$PSScriptRoot\PSError.log"
}


If ($DatabaseType -eq "MSSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban')
		BEGIN
			CREATE TABLE hm_fwban (
				ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
				ipaddress varchar NOT NULL,
				timestamp datetime NOT NULL,
				ban_reason varchar(192) DEFAULT NULL,
				country varchar(192) DEFAULT NULL,
				flag int DEFAULT NULL,
				helo varchar(192) DEFAULT NULL,
				ptr varchar(192) DEFAULT NULL,
				rulename varchar(192) DEFAULT NULL
			)
		END;
		"
	RunSQLQuery $Query
	Write-Host 'Created hm_fwban'

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_rh')
		BEGIN
			CREATE TABLE hm_fwban_rh (
				ipid INT NULL,
				timestamp timestamp NOT NULL,
				ipaddress varchar(15) NOT NULL
			)
		END;
		"
	RunSQLQuery $Query
	Write-Host 'Created hm_fwban_rh'

	#	Create hm_fwban_blocks_ip table if it doesn't exist
	$Query = "
		IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_fwban_blocks_ip')
		BEGIN
			CREATE TABLE hm_fwban_blocks_ip (
			  id INT IDENTITY(1,1) PRIMARY KEY,
			  ipaddress varchar(15) NOT NULL UNIQUE,
			  hits INT,
			  lasttimestamp timestamp NOT NULL
			)
		END;
	"
	RunSQLQuery $Query
	Write-Host 'hm_fwban_blocks_ip'
	
	#	Create hm_ids table if it doesn't exist
	$Query = "
	IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'hm_ids')
	BEGIN
		CREATE TABLE hm_ids (
			timestamp datetime NOT NULL,
			ipaddress varchar(15) NOT NULL PRIMARY KEY,
			hits int NOT NULL,
			country varchar(64) DEFAULT NULL,
			helo varchar(128) DEFAULT NULL
		)
	END;
	"
	RunSQLQuery $Query
	Write-Host 'Created hm_ids'
}

If ($DatabaseType -eq "MYSQL") {

	#	Create hm_fwban table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban (
		ID int(11) NOT NULL AUTO_INCREMENT,
		ipaddress varchar(192) NOT NULL,
		timestamp datetime NOT NULL,
		ban_reason varchar(192) DEFAULT NULL,
		country varchar(192) DEFAULT NULL,
		flag int(1) DEFAULT NULL,
		helo varchar(192) DEFAULT NULL,
		ptr varchar(192) DEFAULT NULL,
		rulename varchar(192) DEFAULT NULL,
		PRIMARY KEY (ID),
		UNIQUE KEY ID (ID)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
		"
	RunSQLQuery $Query

	#	Create hm_fwban_rh table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban_rh (
		  ipid INT(22) NULL,
		  timestamp timestamp NOT NULL,
		  ipaddress varchar(15) NOT NULL,
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query

	#	Create hm_fwban_blocks_ip table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_fwban_blocks_ip (
		  id INT(22) NOT NULL AUTO_INCREMENT,
		  ipaddress varchar(15) NOT NULL UNIQUE,
		  hits INT(8),
		  lasttimestamp timestamp NOT NULL,
		  PRIMARY KEY (id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query

	#	Create hm_ids table if it doesn't exist
	$Query = "
		CREATE TABLE IF NOT EXISTS hm_ids (
		  timestamp datetime NOT NULL,
		  ipaddress varchar(15) NOT NULL,
		  hits int(1) NOT NULL,
		  country varchar(64) DEFAULT NULL,
		  helo varchar(128) DEFAULT NULL,
		  PRIMARY KEY (ipaddress),
		  UNIQUE KEY ipaddress (ipaddress)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		COMMIT;
	"
	RunSQLQuery $Query
}

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 19:29

and here is the code for the commoncode.ps1

Code: Select all

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

.SYNOPSIS
	Powershell component to hMailServer Firewall Ban (CommonCode.ps1)

.DESCRIPTION
	Backend firewall rule administration for hMailServer Firewall Ban Project

.FUNCTIONALITY
	* Provide Common Code to run Firewall Ban modules

.NOTES

.EXAMPLE

#>

# Include required files
Try {
	.("$PSScriptRoot\Config.ps1")
}
Catch {
	Write-Output "Error while loading supporting PowerShell Scripts" | Out-File -Path "$PSScriptRoot\PSError.log"
}

#######################################
#                                     #
#             EMAIL CODE              #
#                                     #
#######################################

Function EmailResults {
	$Subject = "hMS Firewall Ban Notification" 
	$Body = (Get-Content -Path $EmailBody | Out-String )
	$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, $SMTPPort) 
	$SMTPClient.EnableSsl = [System.Convert]::ToBoolean($SSL)
	$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($SMTPAuthUser, $SMTPAuthPass); 
	$SMTPClient.Send($FromAddress, $Recipient, $Subject, $Body)
}

#######################################
#                                     #
#           DATABASE CODE             #
#                                     #
#######################################

Function RunSQLQuery($Query){
    If ($DatabaseType -eq "MYSQL") {
        MySQLQuery($Query)
    } ElseIf ($DatabaseType -eq "MSSQL"){
        MSSQLQuery($Query)
    } Else {
        Out-Null
    }
}

Function MySQLQuery($Query) {
	$Today = (Get-Date).ToString("yyyyMMdd")
	$DBErrorLog = "$PSScriptRoot\$Today-DBError.log"
	$ConnectionString = "server=" + $SQLHost + ";port=" + $SQLPort + ";uid=" + $SQLAdminUserName + ";pwd=" + $SQLAdminPassword + ";database=" + $SQLDatabase
	Try {
		[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
		$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
		$Connection.ConnectionString = $ConnectionString
		$Connection.Open()
		$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
		$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
		$DataSet = New-Object System.Data.DataSet
		$RecordCount = $dataAdapter.Fill($dataSet, "data")
		$DataSet.Tables[0]
	}
	Catch {
		Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
		$Connection.Close()
	}
}

Function MSSQLQuery($Query) {
	$Today = (Get-Date).ToString("yyyyMMdd")
	$DBErrorLog = "$PSScriptRoot\$Today-DBError.log"
    $ConnectionString = "Data Source=" + $SQLHost + ", " + $SQLPort + ";uid=" + $SQLAdminUserName + ";password=" + $SQLAdminPassword + ";Initial Catalog=" + $SQLDatabase + ";"
	Try {
		#[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
		$Connection = New-Object System.Data.SqlClient.SQLConnection($connectionString)
		$Connection.Open()
		$Command = New-Object System.Data.SqlClient.SqlCommand($Query, $Connection)
		$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
		$DataSet = New-Object System.Data.DataSet
		$RecordCount = $dataAdapter.Fill($dataSet, "data")
		$DataSet.Tables[0]
	}
	Catch {
		Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
		$Connection.Close()
	}
}

Function DBCastDateTimeFieldAsDate($fieldName){
    $Return = ""
    If ($DatabaseType -eq "MYSQL") {
        $Return = "DATE($fieldName)"
    } ElseIf ($DatabaseType -eq "MSSQL"){
        $Return = "CAST($fieldName AS DATE)"
    }
    return $Return
}

Function DBCastDateTimeFieldAsHour($fieldName){
	$Return = ""
    If ($DatabaseType -eq "MYSQL") {
		$Return = "HOUR($fieldName)"
    } ElseIf ($DatabaseType -eq "MSSQL"){
		$Return = DBFormatDate $fieldName, '%H'
	}
	return $Return;
}

Function DBSubtractIntervalFromDate(){
    param
    (
        $dateString,
        $intervalName, 
        $intervalValue
    )

    $Return = ""
    If ($DatabaseType -eq "MYSQL") {
        $Return = "'$dateString' - interval $intervalValue $intervalName"
    } ElseIf ($DatabaseType -eq "MSSQL"){
        $Return = "DATEADD($intervalName,-$intervalValue, '$dateString')"
    }
    return $Return
}

Function DBSubtractIntervalFromField(){
    param
    (
        $fieldName, 
        $intervalName, 
        $intervalValue
    )

    $Return = ""
    If ($DatabaseType -eq "MYSQL") {
        $Return = "$fieldName - interval $intervalValue $intervalName"
    } ElseIf ($DatabaseType -eq "MSSQL"){
        $Return = "DATEADD($intervalName,-$intervalValue, $fieldName)"
    }
    return $Return
}

Function DBGetCurrentDateTime(){
    $Return = ""
    If ($DatabaseType -eq "MYSQL") {
        $Return = "NOW()"
    } ElseIf ($DatabaseType -eq "MSSQL"){
        $Return = "GETDATE()"
    }
    return $Return
}

Function DBLimitRowsWithOffset(){
    param(
        $offset,
        $numRows
	)

	$QueryLimit = ""

    If ($DatabaseType -eq "MYSQL") {
		$QueryLimit = "LIMIT $offset, $numRows"
    } ElseIf ($DatabaseType -eq "MSSQL"){
		$QueryLimit = "OFFSET $offset ROWS 
		   	           FETCH NEXT $numRows ROWS ONLY"
	}
	return $QueryLimit
}

Function DBFormatDate($fieldName, $formatSpecifier){
	$Return = ""

	$dateFormatSpecifiers = @{
		'%Y'                   = 'yyyy'
		'%c'                   = 'MM'
		'%e'                   = 'dd'
		'Y-m-d'                = 'yyyy-MM-dd'
		'%y/%m/%d'             = 'yy/MM/dd'
		'Y-m'                  = 'yyyy-MM'
		'%Y-%m'                = 'yyyy-MM'
		'%y/%m/%d %T'          = 'yy-MM-dd HH:mm:ss'
		'%Y/%m/%d %HH:%mm:%ss' = 'yyyy-MM-dd HH:mm:ss'
		'%Y/%m/01'             = 'yyyy-MM-01'
		'%y/%c/%e'             = 'yy/MM/dd'
		'%H'                   = 'HH'
	}

    If ($DatabaseType -eq "MYSQL") {
		$Return = "DATE_FORMAT($fieldName, '$formatSpecifier')"
    } ElseIf ($DatabaseType -eq "MSSQL"){
		$Return = "FORMAT($fieldName, '$dateFormatSpecifiers[$formatSpecifier]', 'en-US')"
	}
	return $Return
}

#######################################
#                                     #
#           FIREWALL CODE             #
#                                     #
#######################################

<#  https://gist.github.com/Stephanevg/a951872bd13d91c0eefad7ad52994f47  #>
Function Get-NetshFireWallrule {
	Param(
		[String]$RuleName
	)
	$Rules = & netsh advfirewall firewall show rule name="$ruleName"
	$return = @()
		$HAsh = [Ordered]@{}
		foreach ($Rule in $Rules){
			switch -Regex ($Rule){
				'^Rule Name:\s+(?<RuleName>.*$)'{$Hash.RuleName = $MAtches.RuleName}
				'^RemoteIP:\s+(?<RemoteIP>.*$)'{$Hash.RemoteIP = $Matches.RemoteIP;$obj = New-Object psobject -Property $Hash;$return += $obj}
			}
		}
	return $return
}

Function RemRuleIP($IP) {
	$Query = "SELECT rulename FROM hm_fwban WHERE ipaddress = '$IP'"
	RunSQLQuery $Query | ForEach {
		$RuleName = $_.rulename
	}

	If (-not($RuleName)) {
		& netsh advfirewall firewall delete rule name=`"$IP`"
	}
	Else {
		$RuleList = "$PSScriptRoot\fwrulelist.txt"
		$NewLine = [System.Environment]::NewLine

		Get-NetshFireWallrule ("$RuleName") | ForEach {
			$RemoteIP = $_.RemoteIP
			$ReplaceCIDR = ($RemoteIP).Replace("/32", "")
			$ReplaceNL = ($ReplaceCIDR).Replace(",", $NewLine)
			Write-Output $ReplaceNL 
		} | out-file $RuleList

		Get-Content $RuleList | where { $_ -ne $IP } | Out-File "$RuleList.delIP.txt"
		$NL = [System.Environment]::NewLine
		$Content = [String] $Template = [System.IO.File]::ReadAllText("$RuleList.delIP.txt")
		$Content.Replace($NL, ",") | Out-File "$RuleList.rule.txt"
		(Get-Content -Path "$RuleList.rule.txt") -Replace ',$', '' | Set-Content -Path "$RuleList.rule.txt"

		& netsh advfirewall firewall delete rule name=`"$RuleName`"
		& netsh advfirewall firewall add rule name=`"$RuleName`" description="FWB Rules for $DateIP" dir=in interface=any action=block remoteip=$(Get-Content "$RuleList.rule.txt")
	}
}

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

Re: Block IPs

Post by palinka » 2020-02-17 19:54

eliassal wrote:
2020-02-17 19:25
I dumped the data in the tables before starting from scratch,
Altered columns in hm_fwban_rh mean your data won't line up correctly. Before: id=autoincrement. Now:id matches id on hm_fwban_blocks_ip where ipaddress is the same. In other words, the altered table accepts duplicates while the old table had unique key.
I am thinking of populating the ids table manullay then fire hmsFirewallBan.ps1, of course I will take care of having those tables clean from new structure perspectives
I don't understand what you're attempting to do here. IDS is triggered by activity in hmailserver. Why would you manually add entries? The web admin has a place to manually ban and release IPs.

RUN NIGHTLY AT 12:01 AM IN THIS ORDER
hmsConsolidateRules.ps1
hmsDuplicateRuleFinder.ps1
hmsUpdateChartTables.ps1

RUN WHENEVER YOU'RE CURIOUS - Same as BANALYZER in the web admin except without max execution time errors.
BlockCount.ps1

ONLY FOR UPGRADING
hmsRetroAddBlocksIPTable.ps1
hmsRetroAddPTR.ps1
hmsRetroAddRuleName.ps1
hmsRetroConsolidateRules.ps1

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

Re: Block IPs

Post by palinka » 2020-02-17 20:01

eliassal wrote:
2020-02-17 17:46
I solved the strange issue, the file was blocked, I unblocked it
Any reason this specific security on this file?
I think GitHub just transfers text without any file attributes. Must be something on your end. Nothing is blocked on my system.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 20:09

OK thanks for both answers, I will continue discovering the tool

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

Re: Block IPs

Post by palinka » 2020-02-17 20:31

eliassal wrote:
2020-02-17 20:09
OK thanks for both answers, I will continue discovering the tool
If you reload your old hm_fwban_rh table with the old structure and data ==> then run hmsRetroAddBlocksIPTable.ps1, it will do the table upgrade for you. You will need to modify the CREATE statements because they are written for MySQL only at the moment.

* It creates hm_fwban_blocks_ip
* Alters hm_fwban_rh
* Populates hm_fwban_blocks_ip with data from hm_fwban_rh
* Populates "ipid" in hm_fwban_rh from autoincrement value in newly created hm_fwban_blocks_ip

I haven't figured out yet how I'm going to use the altered hm_fwban_rh. When I get a chance I'm going to play with queries to see if I can get faster execution. Eventually, I want to delete the ipaddress column altogether, but its still there because its still being used by the blocks and repeat pages, as well as the charts.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 20:52

tell me why in table hm_fwban_rh, the field is of type timestamp like the other fields in the other timesatamp fields in other tables whgic are of datetime type
Thios can generate issues

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 21:32

Panilka, in hmsFirewallBan.ps1
section
#######################################
# #
# FIREWALL LOG PARSING #
# #
#######################################
there is a sql query as follows

Code: Select all

$Query = "INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ON DUPLICATE KEY UPDATE hits=(hits+1),lasttimestamp='$DateTime';"
This doesn't work in sql as you use a specific extnesion to mysql which allows the use of
ON DUPLICATE KEY UPDATE.....
This should be really dealt with differently. Did your freind helping you talked about it?

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

Re: Block IPs

Post by palinka » 2020-02-17 22:21

eliassal wrote:
2020-02-17 21:32
Panilka, in hmsFirewallBan.ps1
section

Code: Select all

$Query = "INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ON DUPLICATE KEY UPDATE hits=(hits+1),lasttimestamp='$DateTime';"
This doesn't work in sql as you use a specific extnesion to mysql which allows the use of
ON DUPLICATE KEY UPDATE.....
This should be really dealt with differently. Did your freind helping you talked about it?
No, he may not have seen it. I uploaded it last night. Do you have a workaround? Actually, the same ON DUPLICATE KEY UPDATE should be found in eventhandlers for IDS, which he did update.

Code: Select all

strSQL = "IF NOT EXISTS (SELECT 1 FROM " & idsTable & " WHERE ipaddress = '" & sIPAddress & "') INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country) VALUES (" & DBGetCurrentDateTime() & ",'" & sIPAddress & "',1,'" & oGeoip("country") & "') ELSE UPDATE " & idsTable & " SET hits=(hits+1), timestamp=" & DBGetCurrentDateTime() & " WHERE ipaddress= '" & sIPAddress & "';"

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

Re: Block IPs

Post by palinka » 2020-02-17 22:39

Speak of the devil... He just put in a pull request for a bunch of things including this query:

Code: Select all

$Query = "IF NOT EXISTS (SELECT 1 FROM hm_fwban_blocks_ip WHERE ipaddress='$ipaddress') INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$ipaddress',1,'$timestamp') ELSE UPDATE hm_fwban_blocks_ip SET hits=(hits+1),lasttimestamp='$timestamp'  WHERE ipaddress='$ipaddress';"
I'm going to merge it in a few minutes as soon as I get done checking it.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 23:00

This is very old way of processing such topic, There is a much better and neat way using "MERGE" Keyworg which does UpSert . either insert if record does not exist or update any other field if reord exists, somthing similar to this (this very quick and dirty example to show you)

Code: Select all

MERGE hm_fwban_blocks_ip AS TARGET
USING (Select ipaddress =126.23.55.123 )   AS SOURCE 
ON (TARGET.ipaddress = SOURCE.ipaddress) 
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ipaddress <> SOURCE.ipaddress OR ......OR ...and
THEN UPDATE SET TARGET.hits = TARGET.hits + 1, TARGET.otherfield = SOURCE.otherfield 
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (TARGET.ipaddress, TARGET.ipaddress, lasttimestamp) VALUES (value, value, value)
Aldo we can delete if want like that

Code: Select all

WHEN NOT MATCHED BY SOURCE 
THEN DELETE 

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-17 23:07

OK please let me know once it is done as nothing is getting to my tables not from the PS ban script nor the hMailserver VBS

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

Re: Block IPs

Post by palinka » 2020-02-17 23:08

eliassal wrote:
2020-02-17 21:32
This should be really dealt with differently. Did your freind helping you talked about it?

Code: Select all

$FirewallLogObjects | foreach-object {
	If (($_.Action -match 'DROP') -and ($_.DestinationPort -match $MailPorts) -and ($_.SourceIP -notmatch $LSRegex)) {
		$IP = $_.SourceIP
		$DateTime = $_.Date + " " + $_.Time
		If ($DatabaseType -eq "MYSQL"){
			$Query = "INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ON DUPLICATE KEY UPDATE hits=(hits+1),lasttimestamp='$DateTime';"
		} ElseIf ($DatabaseType -eq "MSSQL") {
			$Query = "IF NOT EXISTS (SELECT 1 FROM hm_fwban_blocks_ip WHERE ipaddress='$IP') INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ELSE UPDATE hm_fwban_blocks_ip SET hits=(hits+1),lasttimestamp='$DateTime'  WHERE ipaddress='$IP';"
		}
		RunSQLQuery $Query
		$Query = "SELECT id FROM hm_fwban_blocks_ip WHERE ipaddress = '$IP'"
		RunSQLQuery $Query | ForEach {
			$IPID = $_.id
			$Query = "INSERT INTO hm_fwban_rh (timestamp, ipaddress, ipid) VALUES ('$DateTime', '$IP', '$IPID')"
			RunSQLQuery $Query
		}
	}
}

Last edited by palinka on 2020-02-17 23:25, edited 1 time in total.

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

Re: Block IPs

Post by palinka » 2020-02-17 23:21

eliassal wrote:
2020-02-17 23:07
OK please let me know once it is done as nothing is getting to my tables not from the PS ban script nor the hMailserver VBS
How is that any different than before you learned of this project? :wink:

I just pushed another commit on top of the other changes lcamilo did.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 00:34

Here you records in hm_fwban_blocks_ip and hm_fwban_rh tqbles but nothing in hm_ids.
In section
#######################################
# #
# IDS #
# (Intrusion Detection System) #
# #
#######################################
you query hm_ids as follows
$Query = "SELECT ipaddress, country FROM hm_ids WHERE hits > 2"
no records no rules created and scripts finishes without creating the rules

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 00:36

I forgot to mention that the field timestamp in hm_fwban_blocks_ip should be changed to datetime otherwise insert will error

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 01:29

In scriptm hits per daym it generates a sql query

SELECT
a.daily,
a.year,
a.month,
a.day,
a.ipperday,
b.blockperday
FROM
(
SELECT
CAST(timestamp AS DATE) AS daily,
FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%Y]', 'en-US') AS year,
(FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%c]', 'en-US') ) AS month,
FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%e]', 'en-US') AS day,
COUNT(id) AS ipperday
FROM hm_fwban
WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
GROUP BY CAST(timestamp AS DATE)

) AS a
LEFT JOIN
(
SELECT
CAST(timestamp AS DATE) AS daily,
COUNT(DISTINCT(ipaddress)) AS blockperday
FROM hm_fwban_rh
WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
GROUP BY CAST(timestamp AS DATE)
) AS b
ON a.daily = b.daily
ORDER BY a.daily

SAL server does not understand this neither me :shock:

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 01:31

I will stop for tonight as I worked a lotm the eqrlier version did not have so many sql statements issues :(

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

Re: Block IPs

Post by palinka » 2020-02-18 01:35

eliassal wrote:
2020-02-18 00:34
Here you records in hm_fwban_blocks_ip and hm_fwban_rh tqbles but nothing in hm_ids.
In section

Code: Select all

#######################################
#                                     #
#                IDS                  #
#    (Intrusion Detection System)     #
#                                     #
#######################################
you query hm_ids as follows

Code: Select all

$Query = "SELECT ipaddress, country FROM hm_ids WHERE hits > 2"
no records no rules created and scripts finishes without creating the rules
Its not working for you? Do you have entries in IDS? Are any rules being created? Are you sure your changes to CommonCode.ps1 didn't break something?

Code: Select all

#	Pickup entries from IDS 
$Query = "SELECT ipaddress, country FROM hm_ids WHERE hits > 2"
RunSQLQuery $Query | foreach {
	$TS = $_.timestamp
	$IP = $_.ipaddress
	$Country = $_.country

	#	Grab PTR record
	try {
		$PTR = [System.Net.Dns]::GetHostEntry($IP).HostName
	}
	catch {
		$PTR = "No.PTR.Record"
	}
	
	#	Create firewall rule	
	& netsh advfirewall firewall add rule name="$IP" description="Rule added $((get-date).ToString('MM/dd/yy')) - IDS" dir=in interface=any action=block remoteip=$IP
	#	Insert IP record into firewall ban table
	$Query = "INSERT INTO hm_fwban (timestamp,ipaddress,ban_reason,country,flag,ptr,rulename) VALUES ($(DBGetCurrentDateTime),'$IP','IDS','$Country',NULL,'$PTR','$IP');"
	RunSQLQuery $Query
}


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

Re: Block IPs

Post by palinka » 2020-02-18 01:56

eliassal wrote:
2020-02-18 01:29
In scriptm hits per daym it generates a sql query

Code: Select all

SELECT 
		a.daily,
		a.year,
		a.month,
		a.day,
		a.ipperday,
		b.blockperday
	FROM
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily,
			FORMAT(CAST(timestamp AS DATE), [b]'System.Collections.Hashtable[%Y]'[/b], 'en-US') AS year,
			(FORMAT(CAST(timestamp AS DATE), [b]'System.Collections.Hashtable[%c]'[/b], 'en-US') ) AS month,
			FORMAT(CAST(timestamp AS DATE), [b]'System.Collections.Hashtable[%e]'[/b], 'en-US') AS day,
			COUNT(id) AS ipperday 
		FROM hm_fwban 
		WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
		GROUP BY CAST(timestamp AS DATE)
		
	) AS a
	LEFT JOIN
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily, 
			COUNT(DISTINCT(ipaddress)) AS blockperday  
		FROM hm_fwban_rh 
		WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE) 
		GROUP BY CAST(timestamp AS DATE)
	) AS b
	ON a.daily = b.daily
	ORDER BY a.daily
SAL server does not understand this neither me :shock:
Where is this coming from? System.Collections.Hashtable[%Y]

Code: Select all

			$( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%Y') AS year,
			($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') $(If ($DatabaseType -eq 'MYSQL'){Write "- 1"})) AS month,
			$( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%e') AS day,

Runs through 2 functions in CommonCode.ps1:

Code: Select all

Function DBCastDateTimeFieldAsDate($fieldName){
    $Return = ""
    If ($DatabaseType -eq "MYSQL") {
        $Return = "DATE($fieldName)"
    } ElseIf ($DatabaseType -eq "MSSQL"){
        $Return = "CAST($fieldName AS DATE)"
    }
    return $Return
}

	--and--

Function DBFormatDate(){

	param(
		$fieldName, 
		$formatSpecifier
	)

	$Return = ""

	$dateFormatSpecifiers = @{
		'%Y'                   = 'yyyy'
		'%c'                   = 'MM'
		'%e'                   = 'dd'
		'Y-m-d'                = 'yyyy-MM-dd'
		'%y/%m/%d'             = 'yy/MM/dd'
		'Y-m'                  = 'yyyy-MM'
		'%Y-%m'                = 'yyyy-MM'
		'%y/%m/%d %T'          = 'yy-MM-dd HH:mm:ss'
		'%Y/%m/%d %HH:%mm:%ss' = 'yyyy-MM-dd HH:mm:ss'
		'%Y/%m/01'             = 'yyyy-MM-01'
		'%y/%c/%e'             = 'yy/MM/dd'
		'%H'                   = 'HH'
	}
	
    If ($DatabaseType -eq "MYSQL") {
		$Return = "DATE_FORMAT($fieldName, '$formatSpecifier')"
    } ElseIf ($DatabaseType -eq "MSSQL"){
		$Return = "FORMAT($fieldName, '$($dateFormatSpecifiers[$formatSpecifier])', 'en-US')"
	}
	return $Return
}
Sooo.... MySQL:

Code: Select all

DATE_FORMAT(DATE(timestamp), %Y) AS year
... gets transformed into ...

Code: Select all

FORMAT(CAST(timestamp AS DATE), 'yyyy', 'en-US') AS year
I'm not sure why its not working for you. It looks as though the array %Y is not giving you back yyyy as it should be doing.
Last edited by palinka on 2020-02-18 02:02, edited 1 time in total.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 02:00

in hmsFirewallBan.ps1 there is no code to populate ids table

Code: Select all

$FirewallLogObjects | foreach-object {
	If (($_.Action -match 'DROP') -and ($_.DestinationPort -match $MailPorts) -and ($_.SourceIP -notmatch $LSRegex)) {
		$IP = $_.SourceIP
		$DateTime = $_.Date + " " + $_.Time
		If ($DatabaseType -eq "MYSQL"){
			$Query = "INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ON DUPLICATE KEY UPDATE hits=(hits+1),lasttimestamp='$DateTime';"
		} ElseIf ($DatabaseType -eq "MSSQL") {
			$Query = "IF NOT EXISTS (SELECT 1 FROM hm_fwban_blocks_ip WHERE ipaddress='$IP') INSERT INTO hm_fwban_blocks_ip (ipaddress, hits, lasttimestamp) VALUES ('$IP',1,'$DateTime') ELSE UPDATE hm_fwban_blocks_ip SET hits=(hits+1),lasttimestamp='$DateTime'  WHERE ipaddress='$IP';"
		}
		RunSQLQuery $Query
		$Query = "SELECT id FROM [b]hm_fwban_blocks_ip[/b] WHERE ipaddress = '$IP'"
		RunSQLQuery $Query | ForEach {
			$IPID = $_.id
			$Query = "INSERT INTO [b]hm_fwban_r[/b]h (timestamp, ipaddress, ipid) VALUES ('$DateTime', '$IP', '$IPID')"
			RunSQLQuery $Query
		}
	}
}

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

Re: Block IPs

Post by palinka » 2020-02-18 02:10

eliassal wrote:
2020-02-18 02:00
in hmsFirewallBan.ps1 there is no code to populate ids table
Yes. The table gets populated by hmailserver eventhandlers.vbs.

Powershell only deletes entries due to either expiration or because an IP got up to 3 tries within the interval, triggering a ban.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 02:13

$( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%Y') AS year,
...

in the hmsUpdateChartTables.ps1

Post Reply