CODEWITHSUNDEEP
phpmysql
Cam
Cam

Reputation: 15244

Banning by IP with php/mysql

I want to be able to ban users by IP. My idea is to keep a list of IP's as rows in an BannedIPs table (the IP column would be an index).

To check users' IP's against the table, I will keep a session variable called $_SESSION['IP'] for each session. If on any request, $_SESSION['IP'] doesn't match $_SERVER['REMOTE_ADDR'], I will update $_SESSION['IP'] and check the BannedIPs table to see if the IP is banned. (A flag will also be saved as a session variable specifying whether or not the user is banned)

Here are the things I'm wondering:

  1. Does that sound like a good strategy with regards to speed and security (would someone be able to get around the IP ban somehow, other than changing IP's, edit: or using a proxy)?
  2. What's the best way to structure a mysql query that checks to see if a row exists? That is, what's the best way to query the db to see if a row with a certain IP exists (to check if it's banned)?
  3. Should I save the IP's as integers or strings?

Note that...

Upvotes: 3

Views: 4273

Answers (5)

Andrew Moore
Andrew Moore

Reputation: 95434

First, I would recommend not to use MySQL and PHP to do this. Apache has a Deny directive to ban IP addresses and in the end will be better than a homegrown solution.

You could easily create a backend to view & edit an .htaccess file with all those entries.

Here is a example banning 2 IP address (the Google DNS servers for those wondering) and showing a custom page if banned...

Order Allow,Deny
Deny from 8.8.8.8
Deny from 8.8.4.4
Allow from all

ErrorDocument 403 /access_is_denied.htm

This functionality is provided by mod_authz_host in Apache 2.2. More information about each directive can be found in Apache's mod_authz_host Documentation Page.

That being said:

  1. Why have $_SESSION['IP'] at all when the user's IP is always available in $_SERVER['REMOTE_ADDR']? The only reason I can see is to save a trip to the database... But then if you add that IP to your ban list, he will still have access until his session expires.

  2. If you really want to still use a database, store the IP addresses as INT (or 2 BIGINT columns if you want to support IPv6). They take less room than strings and are faster to compare.

As for the security of it... They could bypass the ban by going through a proxy. Spoofing IP addresses in packets is completely useless if you expect to receive a response.

Upvotes: 6

Oli
Oli

Reputation: 239968

  1. http://www.rubyrobot.org/article/protect-your-web-server-from-spambots

    The idea is you store the bans in a database from PHP (or other) but you don't do any filtering with PHP. An extra DB hit every request can be the tipping point from fast to sluggish.

    The bit that does the blocking is the kernel-level firewall, iptables. This gets updated by another script that just reads from the database. You could do something similar with something as simple as a CSV and skip database contact altogether if you wanted to.

    Anyway, IPTables is much more efficient than a full-on DB query.

  2. Make sure you escape the IP input if you use SQL. I might be paranoid but I've seen all sorts of nonsense drip through $_SERVER['REMOTE_ADDR'].

  3. Strings. They're not numbers, they're an arrangement of four numbers. And IPv6 is hex. A string covers all bases with a tiny processing cost.

You should also be aware that server vars can lie. Read: Getting The Real IP Of Your Users

Upvotes: 1

favo
favo

Reputation: 5458

10.000 entries are an easy game for MySQL. Just set the index right and there will be no problem with this amount.

Maybe you should think about a white-listing instead of a black-listing, this could reduce your list of IP addresses?

Your strategy works as long as the user is not behind a rotating proxy. i.e. AOL users accessing websites thru the AOL client will have different IP addresses because their request come from different proxy servers. Normally this isn't a problem.

The IP address of the user is saved within your session ($_SESSION['IP']) which is normally stored on your server and therefor secure enough.

Here is a sample code to check for entries in a table:

$raw = mysql_query ("SELECT ip FROM ip_blacklist WHERE ip = '" . mysql_escape_string(getenv('REMOTE_ADDR')) . "' LIMIT 0,1");
if ( mysql_num_rows ($raw) ) {
 // match found
}
else {
 // no match found
}

You can modify it to search only for a portion of the IP. i.e. instead of 192.168.0.1 you can search for 192.168.0.%:

… WHERE ip LIKE '192.168.0.%' …

This allows you to filter for small networks too instead of defining all the IP addresses.

Using integers is normally the best way, especially if you work with databases and ranges. Strings are better if you want to understand/read your data better.

Upvotes: 1

Femaref
Femaref

Reputation: 61497

  1. Yes, by HTTP header spoofing.
  2. Select Count(*) from bannedIPs where ip = $input, if this returns 0, there is no ban on the IP.
  3. I would use strings.

In general, I would advise you to keep in mind, that your site might be accessed by many computers behind one router, meaning all users will show up with the same ip. You ban one, you ban them all. I would resolve to banning by email and other means. Also, I would say, that people up to harming your page will not be kept away by ip bans or other means. With this in mind, always protect yourself against the usual treats like XSS, sql injection etc.

Upvotes: 2

ChrisF
ChrisF

Reputation: 137188

Re your 3rd question:

You probably need to take care of IPv6 as well as IPv4 so your data structure needs to allow for a 128-bit address value.

Upvotes: 1

Related Questions