SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 18/08/2015, 02:29 PM   #1
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,045
Reputation: 2647
Default GeoIP and handling rangebans with MySQL

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://youtu.be/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.
__________________
Vince is offline   Reply With Quote
Old 18/08/2015, 03:52 PM   #2
Abagail
High-roller
 
Join Date: Dec 2013
Location: GetPlayerPos(playerid, pos); Scripter/Mapper.
Posts: 3,431
Reputation: 406
Default Re: GeoIP and handling rangebans with MySQL

Neat tutorial - you never fail to properly write a tutorial.
Abagail is offline   Reply With Quote
Old 18/08/2015, 04:17 PM   #3
Crystallize
High-roller
 
Join Date: Aug 2013
Posts: 1,554
Reputation: 293
Default Re: GeoIP and handling rangebans with MySQL

A.W.E.S.O.M.E thanks for this awesome tutorial and greatly detailed
Crystallize is online now   Reply With Quote
Old 19/08/2015, 05:31 AM   #4
ThePhenix
Gangsta
 
ThePhenix's Avatar
 
Join Date: May 2012
Posts: 774
Reputation: 109
Default Re: GeoIP and handling rangebans with MySQL

Wow, that's amazing, I've always wanted to find a proper way to make a system like this.
ThePhenix is offline   Reply With Quote
Old 19/08/2015, 10:59 AM   #5
JeaSon
Gangsta
 
JeaSon's Avatar
 
Join Date: Aug 2013
Location: Future
Posts: 565
Reputation: 79
Default Re: GeoIP and handling rangebans with MySQL

Awesome Vince
__________________
Server:23.94.215.9:7777
Social Media:
Other:
My Works
JeaSon is online now   Reply With Quote
Old 19/08/2015, 11:04 AM   #6
Glossy42O
High-roller
 
Join Date: Oct 2014
Location: in my house
Posts: 1,861
Reputation: 231
Default Re: GeoIP and handling rangebans with MySQL

Awesome !

hehe, i thought ur voice would be deeper xD.

i subscribed c:
Glossy42O is offline   Reply With Quote
Old 20/08/2015, 12:29 PM   #7
JeaSon
Gangsta
 
JeaSon's Avatar
 
Join Date: Aug 2013
Location: Future
Posts: 565
Reputation: 79
Default Re: GeoIP and handling rangebans with MySQL

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
__________________
Server:23.94.215.9:7777
Social Media:
Other:
My Works
JeaSon is online now   Reply With Quote
Old 21/08/2015, 01:14 AM   #8
Kar
High-roller
 
Kar's Avatar
 
Join Date: May 2010
Location: Black Asylum
Posts: 3,130
Reputation: 450
Default Re: GeoIP and handling rangebans with MySQL

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.

Last edited by Kar; 21/08/2015 at 11:53 AM.
Kar is offline   Reply With Quote
Old 21/08/2015, 08:04 AM   #9
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,045
Reputation: 2647
Default Re: GeoIP and handling rangebans with MySQL

Works fine for me:

Perhaps you did something wrong importing?
__________________
Vince is offline   Reply With Quote
Old 21/08/2015, 08:56 PM   #10
Kyle
High-roller
 
Kyle's Avatar
 
Join Date: Mar 2009
Location: United Kingdom
Posts: 2,388
Reputation: 310
Default Re: GeoIP and handling rangebans with MySQL

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.
Kyle is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
GEOip Golimad Scripting Help 2 16/02/2014 06:13 PM
[Pedido] Geoip.inc LeandroCQC PortuguÍs/Portuguese 6 28/01/2014 10:04 PM
geoip help F9 Server Support 7 20/02/2012 07:57 PM
geoip.so help F9 Scripting Help 2 19/02/2012 07:21 PM
Rangebans in pawn Ez Help Archive 2 29/03/2009 09:02 PM


All times are GMT. The time now is 01:35 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.