[Tutorial] GeoIP and handling rangebans with MySQL
#1

This tutorial will explain how you can set up a GeoIP system without relying on external services that usually involve HTTP(). The only drawback to this method is that you will periodically (every few months or so) need to update the IP data to be, well, up-to-date.

Warning: Advanced. I don't recommend you attempting this if you're just starting with Pawn and/or MySQL. It gets really technical at some points.

This system can also be used, however, to easily instate range bans that use CIDR ranges. Most systems that I have seen so far store the IP address in its human readable string representation (e.g. 127.0.0.1) which kind of limits the ways you can instate bans. You can ban 127.0.0.* which will ban 127.0.0.0 through 127.0.0.255 or you can ban 127.0.*.* which will ban 127.0.0.0 through 127.0.255.255. But what if a particular ISP has been assigned (for example) 127.0.248.0 through 127.0.255.255? Oops. How are we going to bans this range? 127.0.248.* is not enough and 127.0.*.* is way too much. This is even more prevalent with ranges that extend past the x.x.*.* boundary. For example, a range in Australia ranges from 1.40.0.0 through 1.44.255.255. You can't possibly ban 1.*.*.* because that would also include ranges from many different countries in Asia.

Preparing the data
For this system we will use the MaxMind Legacy GeoLite database. Download the CSV/zip for GeoLite country and unpack it somewhere. We are going to prepare this file for insertion into MySQL. Use a text editor that has proper support for regular expressions such as Notepad++ or Sublime. You really don't want to prepare this file by hand, trust me. Note for Notepad++ users: close any other open documents and then restart Notepad++ before you start the next part, I have experienced glitchy behavior and even an access violation crash.

Now open the CSV file in the text editor. You will notice that it is more than 106 thousand lines long. This is the reason you don't want to do this by hand. Open the search and replace window, which is usually the most easily accessed by pressing CTRL-H. Make sure that the option to use a regular expression is enabled. In the Find box type (or copy):
Code:
"[\.\d]+","[\.\d]+","(\d+)","(\d+)","(.+)",".+"
and in the replace box type (or copy):
Code:
\($1, $2, '$3'\),
Once you have done that, click Replace All. Warning: because of the size of the file this may take several minutes and it may look like the application hangs or crashes. This is normal. Don't do anything in the application while it's busy replacing or you might risk actually crashing the application.

Once the replacing is done you should end up with something like this:



We have now extracted the numerical IP addresses (more on that further down) and the ISO country codes and parsed them into an SQL query (or rather, soon to be query). You can find more information on regular expression on the Internet. Now the only thing we need to do is complete our query. First replace the trailing comma at the end of the file with a semicolon. Then scroll to the top (or press CTRL-Home) and write at the top of the file:
PHP Code:
INSERT INTO list_ipv4 (range_startrange_endcountry_codeVALUES 
Save the file with a .sql extension anywhere you can find it. We will use this later.

Preparing the tables
We will first need to prepare two new tables. The first one we will call list_countries and it will evidently store a list of all the ISO two letter country codes and their names. In the interest of saving time, I have already prepared this. The SQL script can be found here: http://pastebin.com/YFCZ7Tzq. Run it against your database.

The second one we will call list_ipv4 and it will of course store the list of IP ranges we prepared. Create the table:
PHP Code:
CREATE TABLE list_ipv4 (
  
range_start INT UNSIGNED NOT NULL,
  
range_end INT UNSIGNED NOT NULL,
  
country_code CHAR(2NOT NULL,
  
banned INT UNSIGNED NOT NULL DEFAULT 0,
  
PRIMARY KEY (range_startrange_endcountry_code),
  
CONSTRAINT fk_ipv4_countrycode
    FOREIGN KEY 
(country_code)
    
REFERENCES list_countries (country_code)
    
ON DELETE RESTRICT
    ON UPDATE RESTRICT
); 
Importing the data
Now for importing the main bulk of our data, use the file we prepared earlier. Importing it may be tricky, given its size. You may not be able to use phpMyAdmin due to the size restriction on file uploads. Importing with the command line is probably the fastest, but this can be quite daunting and if you don't feel comfortable doing that then you may use another client such as MySQL Workbench (and the latter one may be your only choice if you're on shared host, because you don't have CLI access). For importing with the command line you can follow this video tutorial of mine (with my awesome voice ): https://*********/wCydBvnP5as?t=1m11s (skip the create database part)

Usage
Once you have done all this, we can finally start actually using it. One thing I have mentioned, and which is also visible in the screenshot, is numerical IP addresses. Why numerical? First: because it is faster. Any RDBMS is much faster with numbers than it is with strings. Secondly, it allows allows you to use the BETWEEN keyword (example below). You can convert from string representation (as returned by GetPlayerIp) to numerical representation by using the inbuilt INET_ATON() function, and you can convert from the numerical representation to the string representation by using the also inbuilt INET_NTOA() function. (https://dev.mysql.com/doc/refman/5.0...tion_inet-aton)

This is a simple snippet from my own code that shows a connecting player's IP address and country to all online admins:
PHP Code:
public OnPlayerConnect(playerid)
{
    
GetPlayerName(playeridgPlayerInfo[playerid][pName], MAX_PLAYER_NAME);
    
GetPlayerIp     (playeridgPlayerInfo[playerid][pIP]    , 16);
    
    new 
mysqlquery[160];
    
mysql_format(dbmysqlquerysizeof(mysqlquery), "SELECT c.country_name FROM list_ipv4 i JOIN list_countries c ON i.country_code = c.country_code WHERE INET_ATON('%e') BETWEEN range_start AND range_end;"gPlayerInfo[playerid][pIP]);
    
mysql_tquery(dbmysqlquery"GetCountryName""d"playerid);

PHP Code:
public GetCountryName(playerid)
{
    new 
country[44] = "Unknown";
    
    if(
cache_get_row_count(db))
    {
        
cache_get_row(00countrydb);
    }
    
    new 
msg[144];
    
format(msgsizeof(msg), "Join: %s (%d) [%s / %s] [%d / %d]"gPlayerInfo[playerid][pName], playeridgPlayerInfo[playerid][pIP], countryIter_Count(Player), MAX_PLAYERS);
    
SendAdminMessage(1COLOR_GRAYmsg);

That will show something similar to: Join: Vince0789 (3) [127.0.0.1 / Belgium] [5 / 100]

Banning
You may have noticed that I have purposefully added a "banned" column to the list_ipv4 table. Initially all values in that column are 0, which means no range is banned. If you set that value to 1 (or a Unix timestamp if you're interested in the time the ban was set) you can mark that range as being banned. You can then fetch the 'banned' value when a player connects (using a similar system as outlined above), and boot him out if the value is anything other than 0. It may be a good idea to mark all A1 (anonymous proxy) ranges as banned to begin with.
Reply
#2

Neat tutorial - you never fail to properly write a tutorial.
Reply
#3

A.W.E.S.O.M.E thanks for this awesome tutorial and greatly detailed
Reply
#4

Wow, that's amazing, I've always wanted to find a proper way to make a system like this.
Reply
#5

Awesome Vince
Reply
#6

Awesome !

hehe, i thought ur voice would be deeper xD.

i subscribed c:
Reply
#7

Is there any way to upload that size of data in Database vince ? in localhost or on host ?? i always get mysql server connection gone away
Reply
#8

Oh. It keeps tricking me into thinking that the data is inserted when it's not.

http://prntscr.com/876gzo

http://prntscr.com/876h6s

Heh. I'll deal with that.
Reply
#9

Works fine for me:

Perhaps you did something wrong importing?
Reply
#10

Quote:
Originally Posted by Vince
View Post
Works fine for me:

Perhaps you did something wrong importing?
Works fine for me too, the only issue I came across I had to increase the max packet size when executing the queries. Also, maybe you should suggest how often we update the database.
Reply
#11

I'm having this issue:


Could it be something wrong with the table charset?

Tried executing less rows and it worked, more than 10k lines won't execute and there is no limit for lines either.
Reply
#12

I have done something similar to this for our GeoIP system except it uses a python script to create a SQLite database instead.
Reply
#13

Quote:
Originally Posted by PaulDinam
Посмотреть сообщение
I'm having this issue:


Could it be something wrong with the table charset?

Tried executing less rows and it worked, more than 10k lines won't execute and there is no limit for lines either.
You need to alter your mysql max_packet_size.

Also is there any way to get the city from this or alter it to produce city too?
Reply
#14

If the original GeoIP data from MaxMind includes it you can alter the regEx function as needed so it includes the city information, you'd also need to alter the table aswell as the SQL query.
Reply
#15

Whenever player connect to my server it shows unknown country

PHP код:
Join:  JeaSon(0) [ / Unknown] [30
Reply
#16

It won't work if you are connecting locally to your own server, because your IP will be a private IP (127.0.0.1 or 192.168.x.x most likely), which is not included in the database. If you so wish, you can add those manually, using any of the user-assigned country codes.

PHP код:
INSERT INTO list_countries (country_codecountry_nameVALUES ('XP''Private Network'); 
PHP код:
INSERT INTO list_ipv4 (range_startrange_endcountry_codeVALUES
(INET_ATON('127.0.0.0'), INET_ATON('127.255.255.255'), 'XP'),
(
INET_ATON('10.0.0.0'), INET_ATON('10.255.255.255'), 'XP'),
(
INET_ATON('172.16.0.0'), INET_ATON('172.31.255.255'), 'XP'),
(
INET_ATON('192.168.0.0'), INET_ATON('192.168.255.255'), 'XP'); 
Reply
#17

Quote:
Originally Posted by Vince
Посмотреть сообщение
It won't work if you are connecting locally to your own server, because your IP will be a private IP (127.0.0.1 or 192.168.x.x most likely), which is not included in the database. If you so wish, you can add those manually, using any of the user-assigned country codes.

PHP код:
INSERT INTO list_countries (country_codecountry_nameVALUES ('XP''Private Network'); 
PHP код:
INSERT INTO list_ipv4 (range_startrange_endcountry_codeVALUES
(INET_ATON('127.0.0.0'), INET_ATON('127.255.255.255'), 'XP'),
(
INET_ATON('10.0.0.0'), INET_ATON('10.255.255.255'), 'XP'),
(
INET_ATON('172.16.0.0'), INET_ATON('172.31.255.255'), 'XP'),
(
INET_ATON('192.168.0.0'), INET_ATON('192.168.255.255'), 'XP'); 
I've added the banned field and set A1 banned to 1, can you alter the query to check for this? I know regular queries, but this is weird as they're joined or some how.
Reply
#18

vince i connectd on my hosted server my ip was

182.182.*.*
Reply
#19

@Vince
Do you think using SQL_CACHE in queries makes sense?

Код:
SELECT SQL_CACHE * FROM `accounts`;
?
Reply
#20

I've never used this before. Yet my research shows that query caching is enabled by default so this shouldn't be necessary.
Reply


Forum Jump:


Users browsing this thread: 2 Guest(s)