Using ip2location to block whole countrys ?

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.
M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-19 15:50

... I have an idea ...

IP2LOCATION has free lite access (https://lite.ip2location.com). As a registered user, you can download tables / databases there, which break down IP address ranges by country.

In connection with the table "hm_securityranges" you could go there and create a script that either blocks all unwanted countries or, conversely, only writes the countries in the table from which you actually expect emails.
Ok, you would have to add some exceptions, such as all the Gmail servers and other (free) mail servers, but their IP's actually never change.

From my point of view, this would help a lot against spam and hack attempts through botnets.

what do you think about it?

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-19 17:05

Have a look at this: https://hmailserver.com/forum/viewtopic.php?f=9&t=34496

And this: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

How I do it:

I have a whitelist function that checks various RWLs (RBLs that also do whitelisting). If any come back as whitelisted, then I assume the server should not be geographically discriminated against.

If not IsWhitelisted(oClient.IPAddress) Then do geoip test

Code: Select all

Function Whitelisted(strIP) : Whitelisted = 0

	Dim a : a = Split(strIP, ".")
	Dim strLookup, strRegEx
	Dim IsWLMailSpike, IsWLHostKarma, IsWLNSZones, IsWLSPFBL, IsWLSpamDonkey, IsWLIPSWhitelisted
	
	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".rep.mailspike.net")
	End With
	strRegEx = "^127\.0\.0\.(18|19|20)$" '18=Good, 19=Very Good, 20=Excellent Reputation
	IsWLMailSpike = Lookup(strRegEx, strLookup)

	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".hostkarma.junkemailfilter.com")
	End With
	strRegEx = "^127\.0\.0\.(1|5)$" '1=Good, 5=NoBL
	IsWLHostKarma = Lookup(strRegEx, strLookup)

	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".wl.nszones.com")
	End With
	strRegEx = "^127\.0\.0\.5$" '5=whitelisted
	IsWLNSZones = Lookup(strRegEx, strLookup)

	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".dnswl.spfbl.net")
	End With
	strRegEx = "^127\.0\.0\.(2|3|4|5)$" '2=excellent rep, 3=indispensable public service, 4=corp email (no marketing), 5=safe bulk mail
	IsWLSPFBL = Lookup(strRegEx, strLookup)

	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".dnsbl.spamdonkey.com")
	End With
	strRegEx = "^126\.0\.0\.0$" '126.0.0.0=whitelisted
	IsWLSpamDonkey = Lookup(strRegEx, strLookup)

	With CreateObject("DNSLibrary.DNSResolver")
		strLookup = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".ips.whitelisted.org")
	End With
	strRegEx = "^127\.0\.0\.2$" '2=whitelisted
	IsWLIPSWhitelisted = Lookup(strRegEx, strLookup)

	If (IsWLMailSpike OR IsWLHostKarma OR IsWLNSZones OR IsWLSPFBL OR IsWLSpamDonkey OR IsWLIPSWhitelisted) Then Whitelisted = 1

End Function

Sub OnClientConnect(oClient)

	REM - Test Whitelist (0 = Not Listed, 1 = Whitelisted)
	Dim IsWhitelisted : IsWhitelisted = Whitelisted(oClient.IPAddress)

	If IsWhitelisted = 0 Then
		'
		' Do GeoIP
		'
	End If

End Sub

User avatar
mattg
Moderator
Moderator
Posts: 22046
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Using ip2location to block whole countrys ?

Post by mattg » 2021-10-20 01:08

How I do it:
Use zz.countries.nerd.dk as a DNS blacklist in hMailserver
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
RvdH
Senior user
Senior user
Posts: 2317
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Using ip2location to block whole countrys ?

Post by RvdH » 2021-10-20 09:13

[offtopic]
is the zone countries.nerd.dk being still being maintained/updated?
Got mixed result the other day
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-20 09:22

ok... takes a while to understand that ...

Right now I'm a little confused ...
So ... the script here from Palinka does something different than the thing in the referring links ... Right?

What is better now?

And if I include this script here and enter zz.countries.nerd.dk in HMS, what do I have to enter under "Expected Result, Rejection Message, Score"?
... I don't really understand how it works :roll: I'm still a long way from being able to hold a candle to you...


BTW: https://cbxforum.de/block/ and https://kie-hb.de/block/
Here you can see what is blocked via AutoBan (red) and what I have enter manually as a whole range due massive hack attempts out of this range...
I have clean up the 1st one yesterday so the shown result is started yesterday evening and just run in over night.
Problem with that is that it can be that I make a mistake while entering a range; cut a number on start or end while copy & past and then the half of the world are blocked :roll:
That's why I'm looking vor an other solution...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-20 12:43

M*I*B wrote:
2021-10-20 09:22
And if I include this script here and enter zz.countries.nerd.dk in HMS, what do I have to enter under "Expected Result, Rejection Message, Score"?
I never used nerd.dk, so I tried it out on a known US IP, then changed us to uk to force an error and see what the return codes are:

Code: Select all

C:\Users\palinka>nslookup 4.4.4.4.us.countries.nerd.dk
Server:  dns.opendns.com
Address:  208.67.222.222

DNS request timed out.
    timeout was 2 seconds.
DNS request timed out.
    timeout was 2 seconds.
DNS request timed out.
    timeout was 2 seconds.
DNS request timed out.
    timeout was 2 seconds.
*** Request to dns.opendns.com timed-out

C:\Users\palinka>nslookup 4.4.4.4.us.countries.nerd.dk
Server:  dns.opendns.com
Address:  208.67.222.222

Non-authoritative answer:
Name:    4.4.4.4.us.countries.nerd.dk
Address:  127.0.0.2


C:\Users\palinka>nslookup 4.4.4.4.uk.countries.nerd.dk
Server:  dns.opendns.com
Address:  208.67.222.222

DNS request timed out.
    timeout was 2 seconds.
*** dns.opendns.com can't find 4.4.4.4.uk.countries.nerd.dk: Non-existent domain

C:\Users\palinka>
Looks like return code is 127.0.0.2 if there's a match and NX if no match.

Lots of timeouts. You'll get false positives with this. You'll get false positives with maxmind too because not all of the data is accurate.

If you want to ban only a few countries, then obviously nerd.dk is a much simpler way. If you want to ban many countries, or ban different countries on different ports, then the scripted way is better.

There are other scripts that don't require setting up a database. Look in the scripting forum for more. I only offered the one I made and use.

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-20 13:55

... ahhh! Ok, my fault. I thought your script needed nerd.dk ...

Ok, but what your script does is not yet understandable to me ... I'll put it in and see if I can get it to work. Do I have to think about / adjust / blablub?

To the other script (https://hmailserver.com/forum/viewtopic.php?f=9&t=34496) ...
If MaxMind doesn't work (anymore), then you could fall back on IP2LOC or even use both in parallel as redundancy (if A says is bad and B says is bad, then is bad. If A says something different than B, then not quite like that angry, ...)

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-20 14:00

M*I*B wrote:
2021-10-20 13:55
Do I have to think about / adjust / blablub?
That is one of the minimum requirements.

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-20 14:15

Here's a good one that does not require any database.

https://hmailserver.com/forum/viewtopic ... 20&t=26672

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-20 14:34

... will check that script out too ...

But with MySQL and PHP I'm ok so far.

And I have try to let your Script from GitHub running... There are so many Errors and red lines that I don't know to start...
Shure... I have create a Database and a user with full rights on that DB. But the Script crash with ...

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.
20.10.2021 14:21:40 : [ERROR] : Unable to drop/create database tables : Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
20.10.2021 14:21:40 : [ERROR] : Quitting Script
There are many more, also that the script can't handle a file due it is used by an other process (häää???). And yes, I have called the script out of a PS- shell with admin rights diect in that folder
If you like I can post the whole output ...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-21 01:41

M*I*B wrote:
2021-10-20 14:34

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.
20.10.2021 14:21:40 : [ERROR] : Unable to drop/create database tables : Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
20.10.2021 14:21:40 : [ERROR] : Quitting Script
You probably don't have .net connector installed. Or typo in your credentials. Or the mysql connection requires ssl, or any number of things.
There are many more, also that the script can't handle a file due it is used by an other process (häää???).
This is because the script creates a debug log then finishes. Then another instance attempts to write to the same file, but it's locked by the previous instance. The only fix is to close the powershell command window to unlock the file. It's annoying. The debug log has the current date in the file name. If you change that to date+time, then every instance of running the script creates a new file (new filename) instead of attempting to access the previous one.

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-21 01:43

M*I*B wrote:
2021-10-20 14:34
... will check that script out too ...
That might be your best bet. It definitely works.

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-23 11:17

... ok ... I'm lost a little ...

In the evening I will try to implement the script proposed by palinka at https://hmailserver.com/forum/viewtopic ... 20&t=26672.
Regardless of this, there are several construction sites that I am currently working on:

1. SpamAssassin training
2. Blocking "bad" resp. unused countries
3. Prevention of hacking attacks on mail accounts

At point 1 I'm still at. There is still a lack of understanding in some areas. But I am currently collecting spam from all over the world (currently massive scam regarding "Sparkasse").

Point 2 is partially met with the above script. I think, however, that I will also write a PHP script which will write the "good" countries from the imported table from IP2LOC into the database of HMS instead of "0.0.0.0 - Internet". The easiest way to do this is to define an array in the script in which the country code of good countries are noted, empty the table "hm_securityranges", rewrite localhost as a fixed value and then the areas of the good countries.
Thus, the "good" countries are authorized to broadcast, the "bad" countries have no access at all and do not have to be explicitly blocked.
Opinions?

Regarding point 3, I haven't really thought of anything, except for the previous procedure with Auto-BAN ...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-23 12:18

M*I*B wrote:
2021-10-23 11:17
I think, however, that I will also write a PHP script which will write the "good" countries from the imported table from IP2LOC into the database of HMS instead of "0.0.0.0 - Internet". The easiest way to do this is to define an array in the script in which the country code of good countries are noted, empty the table "hm_securityranges", rewrite localhost as a fixed value and then the areas of the good countries.
Directly manipulating the database is NOT advisable. Its probably going to end up badly. Believe me, we've seen that many times here.

You should slow down. Work on one task at a time until its working without errors, then move on to the next one. There are plenty of really good spam fighting scripts here. Use them. If you have questions, post them on the thread pertaining to that particular script. Do that until it works.

You don't need to add thousands of IP ranges to get geoip blocking working. Just use the scripts provided.

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-23 13:32

Writing direct to the table is not a problem for me. I do it since I drive HMS. If you ok with SQL and some important things (copy table to backup-table, work with backup-table, stop HMS, backup whole HMS-DB, rename active to old, backup to active, start HMS and see ...) then you are on the save side. (My)SQL and PHP I'm much more familiar then HMS and all this stuff around ...
You should slow down.
... I don't know how :roll: HMS and all around is only 2-3% of my daily tasks since... let me think about... maybe 30 years? If all go well end of this year maybe it's the 1st time my wife, my daughter and I will flight to our really 1st Holiday to MX/Cancun (and that is also coupled with a upcoming big task to clear some things up) ...

Ok, for now I have to climb our Roof to fix some brocken roof tiles, install a new SAT-disc and also a magnetic loop antenna for shortwave...
If you don't read me anymore you know I'm falling down and die :lol:

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-23 18:11

palinka wrote:
2021-10-23 12:18
M*I*B wrote:
2021-10-23 11:17
I think, however, that I will also write a PHP script which will write the "good" countries from the imported table from IP2LOC into the database of HMS instead of "0.0.0.0 - Internet". The easiest way to do this is to define an array in the script in which the country code of good countries are noted, empty the table "hm_securityranges", rewrite localhost as a fixed value and then the areas of the good countries.
Directly manipulating the database is NOT advisable. Its probably going to end up badly. Believe me, we've seen that many times here.

You should slow down. Work on one task at a time until its working without errors, then move on to the next one. There are plenty of really good spam fighting scripts here. Use them. If you have questions, post them on the thread pertaining to that particular script. Do that until it works.

You don't need to add thousands of IP ranges to get geoip blocking working. Just use the scripts provided.
Nah ... :mrgreen:

Latest edition of my AutoBan function (MySQL/MariaDB edition).

Code: Select all

Function AutoBan(sIPAddress, sReason, iDuration, sType) : AutoBan = False
    '
    '   sType can be one of the following;
    '   "yyyy" Year, "m" Month, "d" Day, "h" Hour, "n" Minute, "s" Second
    '
    Dim oApp : Set oApp = CreateObject("hMailServer.Application")
    Call oApp.Authenticate(ADMIN, PASSWORD)
    Dim strSQL, strDate, SQLDate, oDB : Set oDB = GetDatabaseObject
    strDate = DateAdd(sType, iDuration, Now())
    strSQL = "INSERT INTO hm_securityranges (rangepriorityid, rangelowerip1, rangeupperip1, rangeoptions, rangename, rangeexpires, rangeexpirestime)" & _
             " VALUES (20, INET_ATON('" & sIPAddress & "'), INET_ATON('" & sIPAddress & "'), 0, '(" & sReason & ") " & sIPAddress & "', 1, STR_TO_DATE('" & strDate & "', '%d-%m-%Y %H:%i:%s'))" & _
             " ON DUPLICATE KEY UPDATE rangeexpirestime = STR_TO_DATE('" & strDate & "', '%d-%m-%Y %H:%i:%s');"
    Call oDB.ExecuteSQL(strSQL)
    AutoBan = True
    oApp.Settings.SecurityRanges.Refresh
    Set oApp = Nothing
    Result.Value = 1
End Function

Function GetDatabaseObject()
    Dim oApp : Set oApp = CreateObject("hMailServer.Application")
    Call oApp.Authenticate(ADMIN, PASSWORD)
    Set GetDatabaseObject = oApp.Database
    Set oApp = Nothing
End Function
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-23 18:56

SorenR wrote:
2021-10-23 18:11
Nah ... :mrgreen:
Not everyone is a 9th degree spamjitsu black belt master... :mrgreen:

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-24 10:46

:mrgreen: :lol:
palinka wrote:
2021-10-23 18:56
SorenR wrote:
2021-10-23 18:11
Nah ... :mrgreen:
Not everyone is a 9th degree spamjitsu black belt master... :mrgreen:

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-24 10:58

SorenR wrote:
2021-10-23 18:11
Latest edition of my AutoBan function (MySQL/MariaDB edition).
... what exactly does it do? How does this work?

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-24 13:24

M*I*B wrote:
2021-10-24 10:58
SorenR wrote:
2021-10-23 18:11
Latest edition of my AutoBan function (MySQL/MariaDB edition).
... what exactly does it do? How does this work?
It is a boolean function to "manually" ban an IP address. You can see the banned IP address in IP Ranges.

Code: Select all

If AutoBan(oClient.IPAddress, "This is a SPAM address", 14, "d") = True Then Eventlog.Write( "Found a spammer " & oClient.IPAddress & " and blocked it" )
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-24 13:43

... so that do the same as the implemented AutoBan function?
Sorry that I ask, but I still don't understand ^^

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-24 15:18

M*I*B wrote:
2021-10-24 13:43
... so that do the same as the implemented AutoBan function?
Sorry that I ask, but I still don't understand ^^
It is a function that allows you to script your own terms for banning spammers.

Stellen Sie sich vor, Sie möchten alle Server mit "Santa-Claus" im Domainnamen bannen.
hMailServer kann das nicht, aber wenn Sie den Domänennamen vom verbindenden Server isolieren können, können Sie Function AutoBan aufrufen, um hMailServer anzuweisen, den Server zur AutoBan-Liste hinzuzufügen.
Viel einfacher, als eigene Blacklists führen zu müssen. ;-)

Jep, German was on the curriculum when I went to school some 45 years ago. A bit rusty though :wink: And I drive a classic Audi so I need to speak the lingo.
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-24 18:40

... hey! Dein Deutsch ist doch sehr gut. :D

Ok, now I get it. It's like a domain-name driven blacklist ?!

So where are the bad domain- names stored?

That what I understand is that the function is called with 4 parameters (sIPAddress, sReason, iDuration, sType) and then it writes that stuff in the right format in the table hm_securityranges.
But from where is the function called and from where are the given parameters?

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-24 18:51

M*I*B wrote:
2021-10-24 18:40
... hey! Dein Deutsch ist doch sehr gut. :D

Ok, now I get it. It's like a domain-name driven blacklist ?!

So where are the bad domain- names stored?

That what I understand is that the function is called with 4 parameters (sIPAddress, sReason, iDuration, sType) and then it writes that stuff in the right format in the table hm_securityranges.
But from where is the function called and from where are the given parameters?
Eventhandlers.vbs ...

An example ... All connection to TCP/IP Ports > 25. Email clients sending and receiving emails from locations outside Denmark, Greenland and Faroe Islands are banned since I have no users there ;-)

Code: Select all

    '
    '   GeoLite ....
    '
    Dim strGeo : strGeo = GeoLite(oClient.IPAddress)
    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
        Result.Value = 1
        If AutoBan(oClient.IPAddress, "GeoLite " & strGeo & " " & strPort, 3, "d") Then _
            EventLogX.Write( LPad("* GeoLite *", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
        Call idsAdd(oClient.IPAddress, 0)
        Disconnect(oClient.IPAddress)
        Set EventLogX = Nothing
        Exit Sub
    Else
        EventLogX.Write( LPad("< GeoLite >", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
    End If
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-24 19:14

And now we're back to the brining of the thread. :D

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-24 19:20

... and what's wrong with that? :mrgreen:

Ok, step by step...

I need...

* the GeoLite thing
* the AutoBan thing
... both to copy into the Eventhandler.vbs (???)

... and that's it?

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-24 20:47

That's it.

And reload scripts or restart hmailserver.

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-25 08:42

SorenR wrote:
2021-10-24 18:51

An example ... All connection to TCP/IP Ports > 25. Email clients sending and receiving emails from locations outside Denmark, Greenland and Faroe Islands are banned since I have no users there ;-)

Code: Select all

    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
Ok, then I have to change the CountryCodes except VOID (stands for what?) to i.e. (DE|GB|US|MX|VOID) and enter my login on both scripts?

Meanwhile I have backup all and copy the both scripts at the end of the EventHandler. Restarting HMS creates the following in the error-log:

Code: Select all

"ERROR"	6516	"2021-10-25 08:53:54.862"	"Script Error: Source: Kompilierungsfehler in Microsoft VBScript - Error: 800A040F - Description: Ungültige 'exit'-Anweisung - Line: 426 Column: 6 - Code: 	Exit Sub"
"ERROR"	6516	"2021-10-25 08:53:54.862"	"Severity: 2 (High), Code: HM5016, Source: ScriptServer::LoadScripts, Description: File: C:\Program Files (x86)\hMailServer\Events\EventHandlers.vbs[nl]Script Error: Source: Kompilierungsfehler in Microsoft VBScript - Error: 800A040F - Description: Ungültige 'exit'-Anweisung - Line: 426 Column: 6 - Code: 	Exit Sub"
Line 426 is out of GeoLite:

Code: Select all

	Set EventLogX = Nothing
	Exit Sub   <--- that create the shown error
Else
Last edited by M*I*B on 2021-10-25 08:55, edited 1 time in total.

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 11:44

M*I*B wrote:
2021-10-25 08:42
SorenR wrote:
2021-10-24 18:51

An example ... All connection to TCP/IP Ports > 25. Email clients sending and receiving emails from locations outside Denmark, Greenland and Faroe Islands are banned since I have no users there ;-)

Code: Select all

    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
Ok, then I have to change the CountryCodes except VOID (stands for what?) to i.e. (DE|GB|US|MX|VOID) and enter my login on both scripts?
VOID is necessary. The geoip function can produce no results due to error/no data/network issues, etc. You can't match (or notmatch) nothing, so "VOID" takes the place of nothing. You will want to process mail that you're not sure of its origin because of the possibility of false positives.

Meanwhile I have backup all and copy the both scripts at the end of the EventHandler. Restarting HMS creates the following in the error-log:

Code: Select all

"ERROR"	6516	"2021-10-25 08:53:54.862"	"Script Error: Source: Kompilierungsfehler in Microsoft VBScript - Error: 800A040F - Description: Ungültige 'exit'-Anweisung - Line: 426 Column: 6 - Code: 	Exit Sub"
"ERROR"	6516	"2021-10-25 08:53:54.862"	"Severity: 2 (High), Code: HM5016, Source: ScriptServer::LoadScripts, Description: File: C:\Program Files (x86)\hMailServer\Events\EventHandlers.vbs[nl]Script Error: Source: Kompilierungsfehler in Microsoft VBScript - Error: 800A040F - Description: Ungültige 'exit'-Anweisung - Line: 426 Column: 6 - Code: 	Exit Sub"
Line 426 is out of GeoLite:

Code: Select all

	Set EventLogX = Nothing
	Exit Sub   <--- that create the shown error
Else
This is because you put the code in the wrong location. It should be located within an hmailserver event. Move geoip testing to within Sub OnClientConnect.

https://www.hmailserver.com/documentati ... ce_scripts
Order of Execution

OnClientConnect (oClient)

spam tests >> DNSBlackilists, HELOhost, MXRecords, SPF

OnSMTPData (oClient, oMessage)

spam tests >> SURBL, DKIM, Greylisting, integrated SpamAssasin

OnAcceptMessage (oClient, oMessage)

OnDeliveryStart (oMessage)

integrated anti-virus check, Global rules

OnDeliverMessage (oMessage)

OnDeliveryFailed (oMessage, sRecipient, sErrorMessage) - if applicable

Message delivered to recipeint(s), Account level rules

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-25 12:02

Ahhh... ok...

Code: Select all

Sub OnClientConnect(oClient)
'
'   GeoLite ....
'
	Dim strGeo : strGeo = GeoLite(oClient.IPAddress)
	If Not Lookup("(DE|US|DK|GB|VOID)", strGeo) And (oClient.Port > 25) Then
		Result.Value = 1
		If AutoBan(oClient.IPAddress, "GeoLite " & strGeo & " " & strPort, 3, "d") Then _
			EventLogX.Write( LPad("* GeoLite *", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
		Call idsAdd(oClient.IPAddress, 0)
		Disconnect(oClient.IPAddress)
		Set EventLogX = Nothing
		Exit Sub
	Else
		EventLogX.Write( LPad("< GeoLite >", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
	End If
End Sub
Right place? If so then the error still exist ...

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-25 12:04

VOID is when there is no resolution. The database is not perfect ;-)

Code: Select all

'******************************************************************************************************************************
'********** GeoLite lookup (MySQL)                                                                                   **********
'******************************************************************************************************************************
Private Const DBDRVR     = "MySQL ODBC 5.3 Unicode Driver"
Private Const DBSERVER   = "localhost"
Private Const DBPORT     = "3306"
Private Const DBEXTRA    = "#DB ON HMAILSERVER#"
Private Const DBUID      = "#DB USER ON HMAILSERVER#"
Private Const DBPW       = "#DB USER PASSWD#"

'   geocountry  CREATE TABLE geocountry (
'               network_start_integer bigint(20) NOT NULL DEFAULT '0',
'               network_last_integer bigint(20) NOT NULL DEFAULT '0',
'               geoname_id varchar(20) NOT NULL DEFAULT '',
'               registered_country_geoname_id varchar(20) DEFAULT NULL,
'               represented_country_geoname_id varchar(20) DEFAULT NULL,
'               is_anonymous_proxy varchar(1) DEFAULT NULL,
'               is_satellite_provider varchar(1) DEFAULT NULL,
'               PRIMARY KEY (network_start_integer,network_last_integer),
'               KEY network_start_integer (network_start_integer),
'               KEY network_last_integer (network_last_integer)
'             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


'   geolocation  CREATE TABLE geolocation (
'                geoname_id varchar(20) NOT NULL DEFAULT '',
'                locale_code varchar(192) DEFAULT NULL,
'                continent_code varchar(2) DEFAULT NULL,
'                continent_name varchar(192) DEFAULT NULL,
'                country_iso_code varchar(2) DEFAULT NULL,
'                country_name varchar(192) DEFAULT NULL,
'                is_in_european_union varchar(1) DEFAULT NULL,
'                PRIMARY KEY (geoname_id)
'              ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Function GeoLite(strIP)
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        GeoLite = "VOID"
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id" & _
                              " WHERE network_start_integer <= INET_ATON('" & strIP & "') AND network_last_integer >= INET_ATON('" & strIP & "')" & _
                              " GROUP BY country_iso_code ORDER BY country_iso_code DESC;")
    Do Until oRecord.EOF
        If (oRecord("country_iso_code") <> "") Then
            If strData = "" Then
                strData = oRecord("country_iso_code")
            Else
                strData = strData & "," & oRecord("country_iso_code")
            End If
        End If
        oRecord.MoveNext
    Loop
    If (Trim(strData) <> "") Then
        GeoLite = strData
    Else
        GeoLite = "VOID"
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function
file: IMPORT_MM.CMD

Import MaxMind GeoIPLite ... I run this from my Backup MX and import it into my hMailServer database.
Backup MX uses MariaDB 64-bit, hMailServer uses MySQL 32-bit so they are sort of the same.

I use curl to fetch files over https. https://curl.se/download.html Windows version is all the way down the bottom :mrgreen:

Code: Select all

@echo off

echo START %DATE% %TIME%  1>>import.log 2>&1
echo.  1>>import.log 2>&1

set PATH=%PATH%;"C:\MariaDB\Bin";"C:\Program Files\7-zip"
set IKEY=#MAXMIND LICENSE KEY#
set IUSR=#DB USER ON HMAILSERVER#
set IPAS=#DB USER PASSWD#
set DATA=#DB ON HMAILSERVER#
set HOST="192.168.0.5"
set PORT=3306

curl -G https://download.maxmind.com/app/geoip_download -d "edition_id=GeoLite2-Country-CSV" -d "license_key=%IKEY%" -d "suffix=zip" -o GeoLite2-Country-CSV.zip  >>import.log
7z e -y GeoLite2-Country-CSV.zip *\GeoLite2-Country-Blocks-IPv4.csv *\GeoLite2-Country-Locations-en.csv   1>>import.log 2>&1

:country
fc GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv >NUL && goto location || goto newcountry  1>>import.log 2>&1

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range  1>>import.log 2>&1
copy /y GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv  1>>import.log 2>&1
mysqlimport -h %HOST% -P %PORT% -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoCountry.csv  1>>import.log 2>&1

:location
fc GeoLite2-Country-Locations-en.csv GeoLocation.csv >NUL && goto same || goto newlocation  1>>import.log 2>&1

:newlocation
copy /y GeoLite2-Country-Locations-en.csv GeoLocation.csv  1>>import.log 2>&1
mysqlimport -h %HOST% -P %PORT% -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoLocation.csv  1>>import.log 2>&1
goto exit

:same
goto exit

    SELECT * FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id
    WHERE network_start_integer <= INET_ATON('8.8.8.8') AND INET_ATON('8.8.8.8') <= network_last_integer;

Table       Create Table
----------  -----------------------------------------------------------------
geocountry  CREATE TABLE geocountry (
              network_start_integer bigint(20) NOT NULL DEFAULT '0',
              network_last_integer bigint(20) NOT NULL DEFAULT '0',
              geoname_id varchar(20) NOT NULL DEFAULT '',
              registered_country_geoname_id varchar(20) DEFAULT NULL,
              represented_country_geoname_id varchar(20) DEFAULT NULL,
              is_anonymous_proxy varchar(1) DEFAULT NULL,
              is_satellite_provider varchar(1) DEFAULT NULL,
              PRIMARY KEY (network_start_integer,network_last_integer),
              KEY network_start_integer (network_start_integer),
              KEY network_last_integer (network_last_integer)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Table        Create Table
-----------  ---------------------------------------------------
geolocation  CREATE TABLE geolocation (
               geoname_id varchar(20) NOT NULL DEFAULT '',
               locale_code varchar(192) DEFAULT NULL,
               continent_code varchar(2) DEFAULT NULL,
               continent_name varchar(192) DEFAULT NULL,
               country_iso_code varchar(2) DEFAULT NULL,
               country_name varchar(192) DEFAULT NULL,
               is_in_european_union varchar(1) DEFAULT NULL,
               PRIMARY KEY (geoname_id)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


:exit
echo END %DATE% %TIME%  1>>import.log 2>&1
echo.  1>>import.log 2>&1
exit 0
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 12:11

Or... The same ^^ (but slightly better :D ) in powershell. https://github.com/palinkas-jo-reggelt/GeoLite2SQL

It sends an email when its finished to let you know if it was successful. :mrgreen:

Its like the war of the currents... :lol:

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

Re: Using ip2location to block whole countrys ?

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

palinka wrote:
2021-10-25 12:11
Or... The same ^^ (but slightly better :D ) in powershell. https://github.com/palinkas-jo-reggelt/GeoLite2SQL

It sends an email when its finished to let you know if it was successful. :mrgreen:

Its like the war of the currents... :lol:
My code don't need to send emails :mrgreen:
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 12:26

SorenR wrote:
2021-10-25 12:15
palinka wrote:
2021-10-25 12:11
Or... The same ^^ (but slightly better :D ) in powershell. https://github.com/palinkas-jo-reggelt/GeoLite2SQL

It sends an email when its finished to let you know if it was successful. :mrgreen:

Its like the war of the currents... :lol:
My code don't need to send emails :mrgreen:
Trust, but verify. :lol:

Anyway, who doesn't like to receive email? Isn't that the whole reason we're here at hmailserver.com?

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-25 16:55

Right now I'm busy getting my house ready for winter...

House is made of concrete blocks and painted with a limestone based paint so you can't just splash anything on the walls... You have to scrape all the loose "paint" off, fill all the cracks and broken corners and repaint the house with a limestone based paint. Limestone based paint is basic so rubber gloves and protective glasses... :roll:

Big cracks are drilled out and filled with a concrete glue, smaller cracks are filled with a sand based outdoor plaster.

Fortunately I only have to do this once every 5 years to maintain the climate shield of the house. We are using paint with limestome from Saint-Astier, Dordogne in France. Probably about 30-35 liters for both buildings to have two coats of paint ...
Attachments
IMG_20211025_163227_small.jpg
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-25 19:09

palinka wrote:
2021-10-25 12:11
Or... The same ^^ (but slightly better :D ) in powershell. https://github.com/palinkas-jo-reggelt/GeoLite2SQL
... noooooppppeee ...
On my system your script don't work... But you know that...
I can't connect to database (but that works well manualy) and there are many problems due locked files...

Some to read for you again 8) It's a 1st run after deleting all createt folder and files the script create:

Code: Select all

    Verzeichnis: C:\Program Files (x86)\hMailServer\Events\Script-Created-Files


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       25.10.2021     19:06              0 DebugLog.log
-a----       25.10.2021     19:06              0 EmailBody.txt
25.10.2021 19:06:43 : GeoIP Update Start
25.10.2021 19:06:43 : ----------------------------
25.10.2021 19:06:43 : Checking for script update at GitHub
25.10.2021 19:06:43 : [ERROR] Obtaining GitHub version : Try 1 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
25.10.2021 19:06:43 : [ERROR] Obtaining GitHub version : Try 2 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
25.10.2021 19:06:44 : [ERROR] Obtaining GitHub version : Try 3 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
25.10.2021 19:06:44 : [ERROR] Obtaining GitHub version : Try 4 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
25.10.2021 19:06:44 : [ERROR] Obtaining GitHub version : Try 5 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
25.10.2021 19:06:44 : [ERROR] Version test failed : Could not obtain either GitHub nor local version information
25.10.2021 19:06:44 : ----------------------------
25.10.2021 19:06:44 : Deleting old files
25.10.2021 19:06:44 : ----------------------------
25.10.2021 19:06:44 : Downloading MaxMind data
25.10.2021 19:06:45 : MaxMind data successfully downloaded in 1 second
25.10.2021 19:06:46 : MaxMind data successfully unzipped in less than 1 second
25.10.2021 19:06:46 : [ERROR] : Unable to rename locations CSV : Das Umbenennen ist nicht möglich, da das Element unter "C:\Program Files (x86)\hMailServer\Events\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv" nicht vorhanden ist.
25.10.2021 19:06:46 : [ERROR] : Quitting Script
25.10.2021 19:06:46 : GeoIP update finished
Out-File : Der Prozess kann nicht auf die Datei "C:\Program Files (x86)\hMailServer\Events\Script-Created-Files\DebugLog.log" zugreifen, da sie von einem anderen Prozess verwendet wird.
In C:\Program Files (x86)\hMailServer\Events\GeoLite2SQL-Functions.ps1:3 Zeichen:70
+ ... e -f G) : $DebugOutput" | Out-File $DebugLog -Encoding ASCII -Append}
+                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], IOException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

25.10.2021 19:06:47 : ----------------------------
25.10.2021 19:06:47 : Counting database records for comparison
25.10.2021 19:06:47 : [ERROR] DATABASE ERROR : Unable to run query : SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'geocountry' Ausnahme beim Aufrufen von "Open" mit 0 Argument(en):  "Authentication to host 'localhost' for user 'geoip' using method 'mysql_native_password' failed with message: Access denied for user 'geoip'@'localhost' (using password: YES)"
25.10.2021 19:06:47 : No database records to count
25.10.2021 19:06:47 : ----------------------------
25.10.2021 19:06:47 : Counting CSV records for comparison
25.10.2021 19:06:48 : Counted 354.884 IPv4 records in new CSV in less than 1 second
25.10.2021 19:06:48 : ----------------------------
25.10.2021 19:06:48 : Converting CSV
25.10.2021 19:06:49 : Country IP CSV successfully converted to integer-range in 1 second
25.10.2021 19:06:49 : ----------------------------
25.10.2021 19:06:49 : Drop and recreate database tables
25.10.2021 19:06:49 : [ERROR] DATABASE ERROR : Unable to run query :
                DROP TABLE IF EXISTS geocountry;
                CREATE TABLE geocountry (
                        network_start_integer BIGINT,
                        network_last_integer BIGINT,
                        geoname_id BIGINT,
                        registered_country_geoname_id BIGINT,
                        represented_country_geoname_id BIGINT,
                        is_anonymous_proxy TINYINT,
                        is_satellite_provider TINYINT,
                        KEY geoname_id (geoname_id),
                        KEY network_start_integer (network_start_integer),
                        PRIMARY KEY network_last_integer (network_last_integer)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8
         Ausnahme beim Aufrufen von "Open" mit 0 Argument(en):  "Authentication to host 'localhost' for user 'geoip' using method 'mysql_native_password' failed with message: Access denied for user 'geoip'@'localhost' (using password: YES)"
25.10.2021 19:06:49 : [ERROR] DATABASE ERROR : Unable to run query :
                DROP TABLE IF EXISTS geolocations;
                CREATE TABLE geolocations (
                        geoname_id BIGINT,
                        locale_code TINYTEXT,
                        continent_code TINYTEXT,
                        continent_name TINYTEXT,
                        country_code TINYTEXT,
                        country_name TINYTEXT,
                        is_in_european_union TINYINT,
                        KEY geoname_id (geoname_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8
         Ausnahme beim Aufrufen von "Open" mit 0 Argument(en):  "Authentication to host 'localhost' for user 'geoip' using method 'mysql_native_password' failed with message: Access denied for user 'geoip'@'localhost' (using password: YES)"
25.10.2021 19:06:49 : Database tables successfully dropped and created
25.10.2021 19:06:49 : ----------------------------
25.10.2021 19:06:49 : Import country IP information
C:\Program Files\MariaDB\bin\mysqlimport.exe: Error: 2002 Can't connect to MySQL server on 'localhost' (10061)
25.10.2021 19:06:51 :  country IP records imported in 2 seconds
25.10.2021 19:06:51 : ----------------------------
25.10.2021 19:06:51 : Import country name information
C:\Program Files\MariaDB\bin\mysqlimport.exe: Error: 2002 Can't connect to MySQL server on 'localhost' (10061)
25.10.2021 19:06:53 :  country name records imported in 2 seconds
25.10.2021 19:06:53 : ----------------------------
25.10.2021 19:06:53 : [ERROR] record count mismatch:
25.10.2021 19:06:53 :  records imported to database
25.10.2021 19:06:53 : 354.884 records in MaxMind CSV
25.10.2021 19:06:53 : Completed update in 10 seconds
25.10.2021 19:06:53 : GeoIP update finished
Out-File : Der Prozess kann nicht auf die Datei "C:\Program Files (x86)\hMailServer\Events\Script-Created-Files\DebugLog.log" zugreifen, da sie von einem anderen Prozess verwendet wird.
In C:\Program Files (x86)\hMailServer\Events\GeoLite2SQL-Functions.ps1:3 Zeichen:70
+ ... e -f G) : $DebugOutput" | Out-File $DebugLog -Encoding ASCII -Append}
+                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], IOException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand


M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-25 20:09

SorenR wrote:
2021-10-25 12:04
file: IMPORT_MM.CMD

Import MaxMind GeoIPLite ... I run this from my Backup MX and import it into my hMailServer database.
Backup MX uses MariaDB 64-bit, hMailServer uses MySQL 32-bit so they are sort of the same.

I use curl to fetch files over https. https://curl.se/download.html Windows version is all the way down the bottom :mrgreen:
That works well! Table are filled now... I have use a own db for that called geoip and not tables inside the HMS- DB.

The big question that now comes up is, how and who call the data out of the two tables now? Is that the GeoLite- snippet what I have implement under "Sub OnClientConnect(oClient)"? There I can't find the hint to the database where to look up...

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-25 22:14

:evil: :evil: :evil: :evil: :evil: :evil:

Code: Select all

"ERROR"	4940	"2021-10-25 22:11:02.901"
"Script Error: Source: Laufzeitfehler in Microsoft VBScript - Error: 800A000D - Description: Typenkonflikt: 'GeoLite' - Line: 412 Column: 14 - Code: (null)"
... comes from ...

Code: Select all

'
'   GeoLite ....
'
	Dim strGeo : strGeo = GeoLite(oClient.IPAddress)
This kill my last nerves... really... :evil:

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 23:11

SorenR wrote:
2021-10-25 16:55
Right now I'm busy getting my house ready for winter...

House is made of concrete blocks and painted with a limestone based paint so you can't just splash anything on the walls... You have to scrape all the loose "paint" off, fill all the cracks and broken corners and repaint the house with a limestone based paint. Limestone based paint is basic so rubber gloves and protective glasses... :roll:

Big cracks are drilled out and filled with a concrete glue, smaller cracks are filled with a sand based outdoor plaster.

Fortunately I only have to do this once every 5 years to maintain the climate shield of the house. We are using paint with limestome from Saint-Astier, Dordogne in France. Probably about 30-35 liters for both buildings to have two coats of paint ...
Make sure you "sound" all around the cracks for hollow spots with a hammer. I wouldn't inject any glue. I'd remove any unbonded stucco.

Cool roof...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 23:22

M*I*B wrote:
2021-10-25 19:09

Code: Select all

25.10.2021 19:06:43 : [ERROR] Obtaining GitHub version : Try 1 : Obtaining version number: Die Anfrage wurde abgebrochen: Es konnte kein geschützter SSL/TLS-Kanal erstellt werden..
This function uses Invoke-WebRequest to get the version number from github. That should be available in all versions of powershell. I think its related to your file permissions issue - powershell can't access the version text file in the script folder. See below.

Code: Select all

25.10.2021 19:06:46 : [ERROR] : Unable to rename locations CSV : Das Umbenennen ist nicht möglich, da das Element unter "C:\Program Files (x86)\hMailServer\Events\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv" nicht vorhanden ist.
This is a permissions issue. Are you running the powershell console as administrator? If yes, then move the script to a location outside of C:\Program Files (x86). I use C:\scripts.

Code: Select all

25.10.2021 19:06:47 : [ERROR] DATABASE ERROR : Unable to run query : SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'geocountry' Ausnahme beim Aufrufen von "Open" mit 0 Argument(en):  "Authentication to host 'localhost' for user 'geoip' using method 'mysql_native_password' failed with message: Access denied for user 'geoip'@'localhost' (using password: YES)"
Did you install mysql dot net connector?

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-25 23:32

M*I*B wrote:
2021-10-25 20:09
SorenR wrote:
2021-10-25 12:04
file: IMPORT_MM.CMD

Import MaxMind GeoIPLite ... I run this from my Backup MX and import it into my hMailServer database.
Backup MX uses MariaDB 64-bit, hMailServer uses MySQL 32-bit so they are sort of the same.

I use curl to fetch files over https. https://curl.se/download.html Windows version is all the way down the bottom :mrgreen:
That works well! Table are filled now... I have use a own db for that called geoip and not tables inside the HMS- DB.

The big question that now comes up is, how and who call the data out of the two tables now? Is that the GeoLite- snippet what I have implement under "Sub OnClientConnect(oClient)"? There I can't find the hint to the database where to look up...
Look up ↑

https://hmailserver.com/forum/viewtopic ... 83#p233983

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-26 09:18

Did you install mysql dot net connector?
Yes
powershell console as administrator?
Yes, ever. And... "C:\Program Files (x86)\hMailServer\Events\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv" is right there and also touchable manually for copy/move/delete out of the same shell. It's spooky like I say here: https://hmailserver.com/forum/viewtopic ... 19#p234019

Code: Select all

Look up ↑
I have, trust me, I have. Show me where the script is that look into the db/table where the geoip- data resist.

Regardless of the two current tasks, I generally see a problem in this forum: There is no structure whatsoever. In one or sometimes several treads, various scripts are scattered wildly, some of which are mutually dependent in order to get something to work.
It's easy for you. It works for you. But you cannot, for example, convey to me which scripts and which basic conditions are necessary. And with my bad English I have no chance of looking for the right script parts together, even different threads and many different versions ...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-26 12:22

M*I*B wrote:
2021-10-26 09:18

Code: Select all

Look up ↑
I have, trust me, I have. Show me where the script is that look into the db/table where the geoip- data resist.
....
It's easy for you.
I made it easy for you as well - I gave you the link directly to the post!

https://hmailserver.com/forum/viewtopic ... 83#p233983

Code: Select all

'******************************************************************************************************************************
'********** GeoLite lookup (MySQL)                                                                                   **********
'******************************************************************************************************************************
Private Const DBDRVR     = "MySQL ODBC 5.3 Unicode Driver"
Private Const DBSERVER   = "localhost"
Private Const DBPORT     = "3306"
Private Const DBEXTRA    = "#DB ON HMAILSERVER#"
Private Const DBUID      = "#DB USER ON HMAILSERVER#"
Private Const DBPW       = "#DB USER PASSWD#"

'   geocountry  CREATE TABLE geocountry (
'               network_start_integer bigint(20) NOT NULL DEFAULT '0',
'               network_last_integer bigint(20) NOT NULL DEFAULT '0',
'               geoname_id varchar(20) NOT NULL DEFAULT '',
'               registered_country_geoname_id varchar(20) DEFAULT NULL,
'               represented_country_geoname_id varchar(20) DEFAULT NULL,
'               is_anonymous_proxy varchar(1) DEFAULT NULL,
'               is_satellite_provider varchar(1) DEFAULT NULL,
'               PRIMARY KEY (network_start_integer,network_last_integer),
'               KEY network_start_integer (network_start_integer),
'               KEY network_last_integer (network_last_integer)
'             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


'   geolocation  CREATE TABLE geolocation (
'                geoname_id varchar(20) NOT NULL DEFAULT '',
'                locale_code varchar(192) DEFAULT NULL,
'                continent_code varchar(2) DEFAULT NULL,
'                continent_name varchar(192) DEFAULT NULL,
'                country_iso_code varchar(2) DEFAULT NULL,
'                country_name varchar(192) DEFAULT NULL,
'                is_in_european_union varchar(1) DEFAULT NULL,
'                PRIMARY KEY (geoname_id)
'              ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Function GeoLite(strIP)
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        GeoLite = "VOID"
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id" & _
                              " WHERE network_start_integer <= INET_ATON('" & strIP & "') AND network_last_integer >= INET_ATON('" & strIP & "')" & _
                              " GROUP BY country_iso_code ORDER BY country_iso_code DESC;")
    Do Until oRecord.EOF
        If (oRecord("country_iso_code") <> "") Then
            If strData = "" Then
                strData = oRecord("country_iso_code")
            Else
                strData = strData & "," & oRecord("country_iso_code")
            End If
        End If
        oRecord.MoveNext
    Loop
    If (Trim(strData) <> "") Then
        GeoLite = strData
    Else
        GeoLite = "VOID"
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function
The above function gets the country information from the database.

Your code that will call the function to get the country info so you can evaluate it against the current client connection: https://hmailserver.com/forum/viewtopic ... 82#p233982

Code: Select all

Sub OnClientConnect(oClient)
'
'   GeoLite ....
'
	Dim strGeo : strGeo = GeoLite(oClient.IPAddress)
	If Not Lookup("(DE|US|DK|GB|VOID)", strGeo) And (oClient.Port > 25) Then
		Result.Value = 1
		AutoBan(oClient.IPAddress, "GeoLite " & strGeo & " " & strPort, 3, "d")
		' If AutoBan(oClient.IPAddress, "GeoLite " & strGeo & " " & strPort, 3, "d") Then _
			' EventLogX.Write( LPad("* GeoLite *", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
		' Call idsAdd(oClient.IPAddress, 0)
		' Disconnect(oClient.IPAddress)
		' Set EventLogX = Nothing
		Exit Sub
	' Else
		' EventLogX.Write( LPad("< GeoLite >", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
	End If
End Sub
I commented out several lines that won't work for you unless you have also installed those functions. Look in the "Hardening hMailServer" thread for IDS, Disconnect and EventLogX. I also changed autoban to just calling it. I'm not sure why Soren set up the IF statement, because if an IP has already been banned, it won't even get as far as Sub OnClientConnect.

IDS and disconnect.exe are 2 very excellent functions.

Below is disconnect (I'm posting because its a simple one).

Code: Select all

Function Disconnect(sIPAddress)
	With CreateObject("WScript.Shell")
		.Run """C:\hMailServer\Events\Disconnect.exe"" " & sIPAddress & "", 0, True
	End With
End Function
You need to get disconnect.exe here: https://d-fault.nl/files/Disconnect.zip

Unzip and place disconnect.exe somewhere. You don't need to install anything. It runs when called from Function Disconnect. It disconnects whatever IP you tell it to, so BE CAREFUL not to disconnect yourself!

At the top of Sub OnClientConnect, add the following to prevent that:

Code: Select all

	REM - Exclude Backup-MX & local LAN from test
	If (Left(oClient.IPAddress, 11) = "192.168.10.") Then Exit Sub  ' local LAN
	If oClient.IPAddress = "127.0.0.1" Then Exit Sub                ' localhost
As to IDS, its very useful, but more complicated, so you'll have to read more about that on the "Hardening" thread. Read first, then ask questions. :D Basically what it does is it records the IP for every client connection. Then if the client successfully sends a message, it deletes the IP record from the database. If no message is sent, it the IP stays. The next time this happens for the same IP, the counter goes up by 1. This happens until the counter reaches 3. Then we assume the IP is a spammer. Then the next time the IP attempts to connect, it gets rejected. You have to set up a database and drop in code in several places. Read first. :D

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-26 12:43

Ok... Now I got it to this point ...
However, the section is available in the backup. I suspect that I somehow accidentally kicked this section out again. I am ashamed * sniff *

Now I have insert and edit the credentials and restart HMS. The error message is gone but replaced by an other one...

Code: Select all

"ERROR"	4260	"2021-10-26 12:37:45.994"
"Script Error: Source: Microsoft OLE DB Provider for ODBC Drivers - Error: 80004005 - Description: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben - Line: 274 Column: 4 - Code: (null)"
Line 274 is on my side...

Code: Select all

oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
I have reinstall the ODBC- driver but that change anything. Or may I have install the wrong driver? Where you grab yours?

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-26 13:03

Translated: The data source name was not found and no default driver was specified

When dealing with ODBC, its critical that the DSN EXACTLY (verbatim) matches your setup.

ODBC ships with windows. Click start > type "ODBC" and setup hmailserver database for ODBC. You may have to install ODBC connector for your database.

I use MariaDB: https://mariadb.com/kb/en/mariadb-connector-odbc/

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-10-26 13:10

palinka wrote:
2021-10-26 12:22
I'm not sure why Soren set up the IF statement, because if an IP has already been banned, it won't even get as far as Sub OnClientConnect.
Ah... The BAN is not effective until the connection has been broken and depending on how you code stuff you may end up calling AutoBan multiple times. Also if you get multiple simultaneous connections from the same host you still only need to BAN one IP address.

It does not have a practical meaning other than to save CPU cycles by not repeating stuff you don't need :mrgreen:
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-10-26 13:23

SorenR wrote:
2021-10-26 13:10
palinka wrote:
2021-10-26 12:22
I'm not sure why Soren set up the IF statement, because if an IP has already been banned, it won't even get as far as Sub OnClientConnect.
Ah... The BAN is not effective until the connection has been broken and depending on how you code stuff you may end up calling AutoBan multiple times. Also if you get multiple simultaneous connections from the same host you still only need to BAN one IP address.

It does not have a practical meaning other than to save CPU cycles by not repeating stuff you don't need :mrgreen:
Right! I totally forgot that. Actually that is the main purpose of disconnect.exe. Even then I've documented literally simultaneous connections from the same IP (to the millisecond in my logs) where one connection gets booted and the other evades filtering.

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-26 13:25

palinka wrote:
2021-10-26 13:03
Translated: The data source name was not found and no default driver was specified

When dealing with ODBC, its critical that the DSN EXACTLY (verbatim) matches your setup.

ODBC ships with windows. Click start > type "ODBC" and setup hmailserver database for ODBC. You may have to install ODBC connector for your database.

I use MariaDB: https://mariadb.com/kb/en/mariadb-connector-odbc/
OHHHH SHITT
Ok, that explain much! I haven't know how to handle that. This information I just don't know before... I will try...

EDIT:
I have download the x64 driver for MariaDB and set it for User-DSN and also System-DSN, I also have set the login to the MariaDB admin "root". Connection works while click test.
The Database to use I have left empty.. Is that right? Or have I set for every database a own DSN?

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-10-26 14:06

Ok, the error still exist:

Code: Select all

"ERROR"	6704	"2021-10-26 13:59:55.919"	"Script Error: Source: Microsoft OLE DB Provider for ODBC Drivers - Error: 80004005 - Description: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben - Line: 274 Column: 4 - Code: (null)"

Off topic:
It is possible to use an "include (file)" inside vbs?
From my view it will help me to hold functions and subs together that are for one thing. I.e. that I can create a file "geolite.inc" where all code are in for that and I just notice in eventhandlers "include = ./geolite.inc"... like this?
Attachments
Bild_2021-10-26_140624.png

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-07 19:09

Can we start over?

Meanwhile I have again more than 2000 AutoBAN, except those ranges I have manually entered :roll:

Pls. take a look at https://lite.ip2location.com/database-download at the 1st download option "IP-COUNTRY". That's right the base I like to use.

One way is to block all unwanted ranges but I think it's better to go the other way and only allow the ranges I send/got emails from (less dataspace anyway...).
Unfortunately I'm a little rusty when it comes to PHP :oops: Maybe someone can support me in my project? I like to do the following:
  • Import the "IP2LOCATION-LITE-DB1.CSV" in a MySQL- table
    That I have done. I declare the rows as "Start, Stop, Code, Country"
  • Create a PHP that generate a html- table with checkbox-input and a flag of any listet country
    I have an old version of that. Just don't work and I'm not shure why right now...
  • Create a PHP-function that clear the table "hm_securityranges" and import the marked countrys (not to forget to import the hardcoded localhost)


It would be perfect if the countries could not be sorted by alphabet alone, but also grouped by continent. But I still have no idea about ...

Here just the old script (not working right now)

Code: Select all

<!-- Comments at the end of the script -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
	"http://www.w3.org/TR/html4/loose.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
		<meta http-equiv="pragma" content="no-cache" />
		<meta http-equiv="refresh" content="60" />
		<style type="text/css">
			* {font-family: 'Lucida Console', Courier, monospace; font-size:14px; font-color:blue;}
			.tabelle {background-color:gray; border:15px solid darkgray; border-collapse:initial;}
			.tabelle td, .tabelle th {padding:1px; border:0px solid darkgray; width:130px;}
			a:link, a:visited {color:yellow; text-decoration: none;}
			a:hover, a:active {color:yellow; text-decoration: none;}
			.small {font-size: 75%;} .big {font-size: 150%; text-decoration: underline overline wavy blue;} .fat {font-size: 200%; text-decoration: underline overline wavy blue;}
		</style>
		<title>Create Blocklist by Country</title>
	</head>

	<body marginwidth="50" marginheight="10" topmargin="10" leftmargin="10" bgcolor="black" text="gold" link="yellow" alink="yellow" vlink="yellow">
		<br /><br /><br />
		<form action="/action_page.php"	method="get">
<?php
	mysqli_report(MYSQLI_REPORT_ERROR);
	$con = new mysqli('localhost', 'user', 'pass', 'database');
		if($con->connect_errno) {
			echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
		}
		
		$res = $con->query('SELECT Code, COUNT(Code) AS quantity FROM ip2locdb1 GROUP BY Code');

		$col = 1; $count=10; $many=0;
		echo "<table class='tabelle'><tr>";
		while($row = $res->fetch_array(MYSQLI_BOTH)) {
			if ($row['Code'] == '-'){
				$cc = '--';
			} else {
				$cc = $row['Code'];
			}
			echo "<td><table border='2' width='100%'><tr><td align='left'><input type='checkbox' id='f_".$many."' name='f_".$many."' value='".$cc."'>".$cc."</td><td align='right'>".$row['quantity']."</td></tr></table></td>";
			if ($col == $count){
				echo "</tr><tr>"; $col = 0;
			}
			$zaehl[$many] = 0; $col++; $many++;
		}
		if ($col != 1){
			echo "<td align='right' colspan='".($count - $col + 1)."'><table border='1' width='100%'><tr><td>&nbsp;</td></tr></table></td>";
		} 
		echo "</tr></table><br /><br /><br />";

	$con->close();
?>
		<input type="submit">
		</form>
	</body>
</html>


M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-07 20:20

Update:
Cache- error. The script shown are working well ...

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

Re: Using ip2location to block whole countrys ?

Post by palinka » 2021-12-07 22:30

M*I*B wrote:
2021-12-07 20:20
Update:
Cache- error. The script shown are working well ...
Image

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-07 22:36

emoticono-rock-gif.gif
emoticono-rock-gif.gif (624.45 KiB) Viewed 143635 times
That's the result of the script (some things are added like flags):

https://cbxforum.de/block/create.php

Now I like to tick the countrys I like to allow and if I'm finish and klick the SEND- button at bottom there must be a PHP function that create the SQL "SELECT FROM * WHERE (selected countrys)", put all ranges from all selected countrys in an array and write them incl. localhost into hm_securityranges

But with the function I have no idea how to... I never have worked with INPUT stuff and don't know how to give it to the function

EDIT:
I also will write the country code in the description field so that I can also grab all former selected countrys if I call the script, so that all there are in selected again (hope you can follow what I mean)

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-15 22:21

:evil: :evil: :evil: :evil: :evil: :evil: :evil: :evil: :evil: :evil:

Since hours I'm not see the error I place in... A simple PHP SQL I use since I can remember. That works on every other way but not here.
May you may take a look at and let me know what is wrong??? Big point is the error "Unknown column 'AT' in 'where clause'" at the end of the output. But why? The col is "code" hell not AT

Code: Select all

<?php

	include('statics.php'); // Login to DB a.s.o. That works well in any way...
	echo "Tabelle = ".$tab2."<br />";
	
			$mark = fopen($file, "r") or die("Unable to open file!");
			$marked = explode(',',fread($mark,filesize($file)));
			fclose($mark);

	print_r($marked); echo "<br />";

	mysqli_report(MYSQLI_REPORT_ERROR);
	$con = new mysqli($host,$user,$pass,$base);
		if($con->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; }
		
		foreach ($marked as $coco) {
			echo $coco."<br />";
			echo "SELECT * FROM hmail.hm_security_ip2loc WHERE code = `$coco`<br />";
			$res = $con->query("SELECT * FROM hmail.hm_security_ip2loc WHERE code = `$coco`");
			while($row = $res->fetch_assoc()) {
//				$rangelowerip1 = $row['Start'];
//				$rangeupperip1 = $row['Stop'];
//				$rangeoptions = 96203;
//				$rangename = $row['Code']." - ".str_replace('.', '', substr(long2ip($row['Start']),0,7))." ".$row['Country'].", ".$row['Area'].", ".$row['City'];
//				$rangeexpires = 0;
//				$rangeexpirestime = $date->format('Y-m-d H:i:s');

				echo $row['Start']." to ".$row['Stop']."<br />";
				echo $row['Code']." ".$row['Country'].", ".$row['Area'].", ".$row['City'];"<br />";
			}
		}
		
		$con->close();
?>
Output:

Code: Select all

Tabelle = hm_security_ip2loc
Array ( [0] => AT [1] => AU [2] => BA [3] => BE [4] => BG [5] => CA [6] => CH [7] => CZ [8] => DE [9] => DK [10] => FI [11] => FR [12] => GB [13] => GR [14] => HU [15] => IE [16] => IM [17] => IT [18] => LT [19] => LV [20] => MT [21] => MX [22] => NL [23] => PL [24] => PT [25] => RO [26] => SE [27] => SI [28] => US [29] => VI )
AT
SELECT * FROM hmail.hm_security_ip2loc WHERE code = `AT`

Warning: mysqli::query(): (42S22/1054): Unknown column 'AT' in 'where clause' in C:\Users\Public\_WWX_\blockrange\test.php on line 19

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\Users\Public\_WWX_\blockrange\test.php:20 Stack trace: #0 {main} thrown in C:\Users\Public\_WWX_\blockrange\test.php on line 20
Bild_2021-12-15_212525.png
Table structure hm_security_ip2loc

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

Re: Using ip2location to block whole countrys ?

Post by SorenR » 2021-12-15 22:30

Code: Select all

SELECT * FROM hmail.hm_security_ip2loc WHERE code = `AT`
Wrong quotes?

` = 0x60
' = 0x27
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-15 22:33

nope ... Unfortunately not. I have try all possible ways also without and in combination...
The big thing is that this query works well on other places and other tables...

Some lines before I ask the same table like ...

Code: Select all

$res = $con->query('SELECT *, COUNT(Code) AS quantity FROM '.$tab2.' GROUP BY Code');
That works well, also if I change the quotes to other ones or leave them away...

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2021-12-15 23:08

Ok... now it works... and I have absolutely no idea why :evil: :evil: :evil:

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2022-08-18 19:12

palinka wrote:
2021-10-19 17:05
Have a look at this: viewtopic.php?f=9&t=34496

And this: https://github.com/palinkas-jo-reggelt/GeoLite2SQL
Ok, I think I give it up in any way with HMS. I have now start over from scratch with GeoLite2SQL and I'm not able to create a single run without errors. PowerScript is one of the biggest bullshit I've ever seen and try to working with. I'm tired to spend hour by hour, day by day, week by week trying to let any PS running without any shitty errors, reading hundreds of threads about the same topic and try to translate and understand the written.

Right now I'm totally frustrated and I'm thinking about just dropping all that shit. There are certainly providers to which I can redirect my domains and where I don't have to worry about anything except paying a few dollars on time. I think the lifetime that I gain and can spend with my family and my satisfying hobbies is worth the few bucks...

User avatar
RvdH
Senior user
Senior user
Posts: 2317
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Using ip2location to block whole countrys ?

Post by RvdH » 2022-08-19 00:06

Good choice, running your own mailserver is clearly nothing for you, the lack of even the most basic know-how is shocking, nonexistent...bye, bye
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

M*I*B
Senior user
Senior user
Posts: 324
Joined: 2008-12-30 19:09

Re: Using ip2location to block whole countrys ?

Post by M*I*B » 2022-08-21 22:54

Thanks for the final insult...

You really are a seldom stupid asshole. Such arrogant and self-confident motherfuckers should best be sorted out at birth. Piss off!

Locked