« Previous entry | Next entry » Browse > Snippets

Skip to comments (9) Blocking PeerGuardian IPs using MySQL
Posted by Niek on Oct 22 2005 @ 14:47  :: 4318 unique visits

This post is based on a snippet I posted a while ago, PeerGuardian to iptables converter.

This is a different version, which puts all the PG2 blocklist entries in a MySQL database. Very handy to block suspicious IPs in your webapp, for example. It's also way faster than the slow IPtables lookups.

First, create a "blocklist" table in your database:
CODE: OTHER
CREATE TABLE `blocklist` (
  `ip_start` int(10) unsigned NOT NULL,
  `ip_end` int(10) unsigned NOT NULL,
  KEY `ips` (`ip_start`,`ip_end`)
)


Then, use the following script to generate the SQL import script:
CODE: BASH
#!/bin/sh

if [ "$#" -ne 1 ]; then
  echo "Usage: $0 <url to blocklist>"
  echo "Example: $0 http://www.bluetack.co.uk/config/level1.txt"
  exit;
fi

echo "Fetching the latest blocklist, please wait..."
wget -q $1 -O /tmp/pg2mysql.list

# Fix for UTF-8 environments
iconv -f latin1 -t utf-8 /tmp/pg2mysql.list > /tmp/pg2mysql.list.utf8
mv /tmp/pg2mysql.list.utf8 /tmp/pg2mysql.list

echo "Blocklist fetched, converting to MySQL import script..."
echo "TRUNCATE TABLE blocklist;" > /tmp/pg2mysql.sql
sed "s/(.*):(.*)-(.*)/INSERT INTO blocklist (ip_start, ip_end) VALUES (INET_ATON('2'), INET_ATON('3'));/" /tmp/pg2mysql.list | tr -d 'r' >> /tmp/pg2mysql.sql

echo "Done, do not forget to import the /tmp/pg2mysql.sql script"


And finally import it in your database:
CODE: OTHER
mysql database_name < /tmp/pg2mysql.sql


Example of the usage in PHP:
CODE: PHP
$ip = ip2long($_SERVER['REMOTE_ADDR']);
if($ip < 0) $ip += 0xffffffff;

$query = 'SELECT * FROM blocklist WHERE ip_start <= '.$ip.' and ip_end >= '.$ip;
$result = mysql_query($query) or die('Error in SQL query: '.mysql_error());
if(mysql_num_rows($result) > 0)
  echo 'You are in the blocklist!';
else
  echo 'Your IP is trusted...';

9 comments posted so far
Add your own »

1. On Oct 22 2005 @ 17:05 English_Man wrote:

Works great! (now the bugs are fixed, hehehe)

E_Man

2. On Oct 26 2005 @ 01:04 guest wrote:

I really love MySQL and all, and have never really used PostgreSQL at all but it occurs to me that this:
http://www.postgresql.org/docs/7.3/interactive/datatype-net-types.html
means that PostgreSQL would be more suited to this task.

3. On Nov 19 2005 @ 18:09 English_Man wrote:

Support has now been addded to XBTT. However a few minor changes need to be made to make the above script work.

Create this tabke instaed.

CODE: SQL
CREATE TABLE xbt_deny_from_hosts
(
  begin int NOT NULL,
  end int NOT NULL
);


And change line 18 to

CODE: BASH
sed "s/(.*):(.*)-(.*)/INSERT INTO xbt_deny_from_hosts (begin, end) VALUES


Thats it, works for me.

E_Man

4. On Jan 07 2006 @ 20:32 guest wrote:

This does not seem to work for me

I'm using Debian 3.1 and it gets the list fine and creates the sql file but it does not add INSERT commands anywhere into it.

it adds the TRUNCATE TABLE command to the top of the sql file but then just copies each line from the peerguardian file straight in without adding the INSERT commands so of course sql cannot import it

5. On Feb 02 2008 @ 06:16 guest wrote:

for all who want to get this to work with a php script/cronjob:
copy the code to a file_name.php
chmod 700 file_name.php
crontab -e
add:
43 5 * * 1 /my/path/to/script/file_name.php >/dev/null 2>&1

for weekly execution at 5:43am

http://www.mymfc.com

CODE: OTHER
#!/usr/bin/php5
<?
    $mysql_host = "localhost";
    $mysql_user = "xxxx";
    $mysql_pass = "xxxx";
    $mysql_db = "xxxx";

    $out = `wget -q http://www.bluetack.co.uk/config/level1.gz -O file.gz`;
    $out = `gzip -d file.gz`;
   
    if (!($dblink = @mysql_connect($mysql_host, $mysql_user, $mysql_pass)))
    {
        die();
    }
    mysql_select_db($mysql_db)
        or die();

    mysql_query("TRUNCATE TABLE xbt_deny_from_hosts;") or mysql_error();
   
    $handle = fopen ("file", "r");
    while (!feof($handle))
    {
    $buffer = fgets($handle, 4096);
        preg_match('/(.*):(.*)-(.*)/', $buffer, $t);
        if(count($t) == 4)
        {
            $sql = "INSERT INTO xbt_deny_from_hosts (begin, end) VALUES ('" . ip2long($t[2]) . "', '" . ip2long($t[3]) . "');\n";
            mysql_query($sql) or mysql_error();
        }
    }
    fclose ($handle);
    mysql_close();
    $out = `rm file`;
?>

6. On May 19 2009 @ 09:46 guest wrote:

Find louis vuitton handbag and louis  Louis Vuitton Handbags , louis vuitton wallet and louis vuitton purse items on Louis Vuitton Store Browse a huge selection of LV Handbags Louis Vuitton is luxury gifts, French fashion, the replica Louis Vuitton Store is woman best friend. With Louis Vuitton Discover real Louis Vuitton bags, Vuitton accessories and the latest handbag lines. Join the biggest active Louis Vuitton enthusiast community on the web today

UGGs is a brand that is all about luxury and comfort for everyday life. Only the finest quality materials are used to create UGG Boots. Provide UGG Women Boots,UGG Man Boots,UGG Kids Boots.
UGG Australia is the largest distributor of Grade-A sheepskin.
Find Women's UGGs, Men's UGG Boots, and Kids UGGs on Sale all made with ... UGG Store include Discount UGG  UGG Classic Tall,UGG Classic Short

Louis Vuitton is luxury gifts, French fashion, the replica Louis Vuitton Handbag is woman best friend.Monogram Groom.
Offers Discount Louis Vuitton handbags and Louis Vuitton bags and all other designer handbags,free global fast shipping,low price and top quality.Monogram Jokes,Monogram Suede cheap Louis Vuitton
Louis Vuitton.

Looking For Gucci Shoes ? Gucci Store provide gucci Mens shoes,gucci Womens shoes
Wonderful Gucci shoes sale Gucci men's shoes and Gucci women's shoes at discount Gucci Shoes prices.
cheap gucci Shoes
Gucci Shoes and gucci clothing Spring - Summer 2009, Prada Shoes and prada clothing from the Latest Collection 2009 and Dolce Gabbana Clothing 2009
Gucci Loafers
Gucci Sneakers

UGGs
Louis Vuitton Handbags
Gucci Shoes
Louis Vuitton
UGG Boots
Louis Vuitton Handbags
gucci shoes
Monogram Groom
Discount Louis Vuitton
UGG Boots
Louis Vuitton handbags

7. On Jun 25 2009 @ 04:16 guest wrote:

DVD Audio Ripper
Free DVD Audio Ripper
DVD Audio Ripper for Mac
DVD to MP4 Converter
DVD to MP4 Converter for Mac
DVD to iPod Converter
free DVD to iPod Converter
DVD to iPod Converter for Mac
DVD Ripper
Diablo 2 CD Key
Diablo 2 CD Keys
Free DVD Ripper
DVD Ripper for Mac
NFL Jerseys
Scoccer Jerseys
Hockey Jerseys
MLB Jerseys
NHL Jerseys
Nike Sneakers

8. On Jul 14 2009 @ 04:23 guest wrote:

buy wow gold
my wow power leveling
buy wow gold
good wow power leveling
BUY wow gold
my wow power leveling
CHEAP rs gold
cheap wow power leveling
CHEAPEST lotro gold
MY aion gold
buy wow gold
cheap wow gold
CHEAPEST wow gold

9. On Jul 14 2009 @ 05:53 guest wrote:

AVI to DVD Converter,AVI to DVD Creator,iPhone Ringtone Maker for Mac,AVI Converter OS X,VOB Converter OS X,AVCHD Video Converter,FLV Converter,PowerPoint Converter,AVCHD Converter,Blue-Ray ripper,Rip Blue Ray,FLV to MOV Mac,VOB to DVD,HD Video Converter,iPod Playlist Transfer

Add a new comment

Name:
Password: (leave empty for anonymous comment)
 
View formatting tags Comment: