Home › Forums › BlockHosts

Store blocked hosts into MySQL before pruning

Mon, 2007–Aug–27

I'd like to store blocked hosts into MySQL before pruning.

MySQLdb is installed as well, but I'm not familiar with python programming.

How can I put the list of blocked hosts into MySQL query before BlockHosts updates the hosts lists?

Any help would be appreciated.

Thanks,
Istvan

‹ Blockhost 2.05 Blockhosts doesn't run automatically from /etc/hosts.allow ›
Mon, 2007-08-27 07:13 — ac

things needed

This would require a lot of additional steps too:

1) define the database schema.
Would probably need to store IP address, time last updated, optionally the local sever IP address or some identification (if multiple hosts will use a single DB to store IP addresses, this may be useful)

2) have tools to create and manage the database - may be purging once in a while.

3) and then edit the blockhosts.py script to add or update the blocked IPs in the database. The list "blocked" contains all blocked address, so the logic would check each "blocked" IP address - if not in database, then add it. If already in database, then nothing to do.

4) Instead of editing blockhosts.py, another option is to write a script that runs every 8 hours (any duration less than AGE_THRESHOLD - which is by default 12 hours - after which time blocked IP may be removed by blockhosts.py) - and this script would just scan /hosts.allow, parse the blocked hosts area, and use those IP addresses to add to the database. This would leave blockhosts.py unchanged (easier upgrades). And this script can be created following the existing "bhrss.py" example.

I do not know MySQL, but the above steps should be easy to clobber together from examples on the web.
Also: I presume you need this in the database because there is some other application that would be reading this list - what is the intended purpose to storing these addresses in a database?

Mon, 2007-08-27 09:31 — Istvan (not verified)

How edit blockhosts.py for MySQL query?

Thanks for your quick reply.

The final end purpose is the next: getting blocked hosts by all aged periods from database and putting them into a web page with PHP and parsing IP's by MaxMind on the fly. If blocked hosts are already stored in database, than I can do these tasks as well.

1) The single database schema:

# mysql -u root -p
mysql> CREATE DATABASE blockhosts;
USE blockhosts;
CREATE TABLE bhosts (
id int(11) NOT NULL auto_increment,
blocked_ips varchar(16) NOT NULL default '0',
prune_time datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) TYPE=MyISAM;
quit;

2) Planned MySQL query in python:

import MySQLdb
db = MySQLdb.connect(host="localhost", user="user_name", passwd="user_password",db="blockhosts")
cursor = db.cursor()
cursor.execute("""INSERT INTO bhosts (blocked_ips, prune_time) VALUES ("%s",now())""",(blocked)),
cursor.close()
db.close()

3) This is the step what is not clean for me: where need I put the above code into blockhosts.py? I tried to write into different places but all of them gave syntax error.

4) There is a good idea to solve it with a standalone script scanning hosts.allow (in my case - Fedora 4 - hosts.deny) by cron job, but it would be much more elegant storing data into database directly by python anyway.

Thanks,
I.

Mon, 2007-08-27 21:23 — Istvan (not verified)

Just I see

Just I see there is absolutely unnecessary to manipulate blockhosts.py for this task.
It's simple and more than enough with PHP and cron.

Thanks,
I.

Sat, 2007-09-01 14:25 — Istvan (not verified)

Real sample to show blocked hosts

I made a real sample to show actually blocked hosts. Visit and download it from here.

I.

Sun, 2007-09-02 11:54 — ac

good stuff

I'll create a link in the contributed utilities for this, looks interesting.

There is a package that does something similar - except it does not have MySQL storage, but it does use Google Maps for a mashup, here's the link:
intrusion-source-and-location-mashup

Sun, 2007-09-02 13:06 — Istvan (not verified)

All right

All right but I have to say I changed my original opinion: this small collection does not use MySQL, because it's not necessary to save blocked hosts for a long time. In this way we can check the contents of hosts.allow (hosts.deny) at realtime only.
I.

Tue, 2007-09-04 15:46 — Istvan (not verified)

A faster method published

A faster method - with PHP(CLI), MySQL & cron - published on the same link above.

Tue, 2007-09-04 20:35 — ac

Created contrib book page for this tool

To collect all tools in one page, this is now available at:
Show blocked hosts on web