Iptables и интернет кафе

#!/usr/bin/php
<?php
$db = mysql_connect(...)
    or die('connect to database failed');
mysql_select_db('wifi')
    or die('db not found');
    
    
$ip = @$argv[1];
if (@mysql_result(mysql_query("SELECT COUNT(*) FROM `DenyList` WHERE clientip = ".$ip." LIMIT 1"),0) > 0    ){
    print "ERR\n";
} else {
    print "OK\n";
}
mysql_close();
?>

chown nobody:nogroup check.php

#!/bin/sh
rm -f /wifi/tmp/access.log
cp /var/log/squid/access.log /wifi/tmp/access.log
cat /dev/null > /var/log/squid/access.log
php /wifi/scripts/squid-to-mysql.php
echo "lstat for this access.log:"
ls -l /wifi/tmp/access.log
mv  /wifi/tmp/access.log /wifi/tmp/access.log.last

#!/usr/bin/php
<?php
$db = mysql_connect()
    or die('connect to database failed');
mysql_select_db('wifi')
    or die('db not found');
$settings = array();
$result = mysql_query("SELECT * FROM `Config`");    
while($row = mysql_fetch_array($result)) {
    $settings[$row['name']] = $row['value'];
}
$settings['traffic'] = $settings['traffic']*1024*1024;
$filename = '/wifi/tmp/access.log';
$file = fopen($filename, 'r');
while(!feof($file)) {
    $line = fgets($file, 4096);
    $line_array = preg_split("/[\s,]+/", $line);
    
    $t1 = preg_split("/[\/]/", $line_array[8]);
    if ($t1[1] == "127.0.0.1")  continue;
    $t1 = preg_split("/[\/]/", $line_array[8]);
    if ($t1[1] == "192.168")  continue;
    $t1 = preg_split("/[\/]/", $line_array[3]);
    if ($t1[0] == "TCP_DENIED")  continue;
    
    $date = preg_split("/[.]/", $line_array[0]);
    $peerip = preg_split("/[\/]/", $line_array[8]);
        
    mysql_query("INSERT INTO `Traffic` (`date`, `clientip`, `resultcode`, `bytes`, `url`, `peerip`, `mimetype`) VALUES('".$date[0]."', INET_ATON('".$line_array[2]."'), '".$line_array[3]."', '".$line_array[4]."', '".$line_array[4]."', INET_ATON('".$peerip[1]."'), '".$line_array[9]."')");
    
    mysql_query("INSERT INTO `Sessions` SET clientip = INET_ATON('".$line_array[2]."'), time=0, status = 1, bytes=".$line_array[4].", lasttime = '".$date[0]."' ON DUPLICATE KEY UPDATE bytes = bytes + ".$line_array[4].", lasttime = '".$date[0]."'");
    
}
fclose($file);
mysql_query("UPDATE `Sessions` SET time = time + 1");
mysql_query("UPDATE `Sessions` SET status = 0, time = 0, bytes = 0 WHERE (time > '".$settings['limittime']."' OR bytes > '".$settings['traffic']."') AND status = 1");
mysql_query("UPDATE `Sessions` SET status = 1 WHERE time > '".$settings['waittime']."' AND status = 0");
mysql_query("INSERT INTO `DenyList` (`clientip`) SELECT clientip  FROM `Sessions` WHERE status = 0");
$time = time();
$lasttime = $settings['droptime']*60;
mysql_query("DELETE FROM `DenyList` WHERE clientip IN (SELECT clientip FROM `Sessions` WHERE status = 1 OR  (".$time." - lasttime) > ".$lasttime.")");
mysql_query("DELETE FROM `Sessions` WHERE  (".$time." - lasttime) > ".$lasttime." "); 
$result = mysql_query("SELECT clientip FROM `DenyList`");
exec("/sbin/iptables -t nat -F blacklist");
exec("/sbin/iptables -F droplist");
while($row = mysql_fetch_array($result)) {
    exec("/sbin/iptables -t nat -A blacklist  -s ".long2ip($row['clientip'])." -p tcp --dport 80 -j REDIRECT --to-port 80");
    exec("/sbin/iptables -A droplist  -s ".long2ip($row['clientip'])." -p tcp --dport 3128 -j REJECT");
}
mysql_close();
?>

CREATE TABLE IF NOT EXISTS `Config` (
  `name` varchar(250) NOT NULL,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE IF NOT EXISTS `DenyList` (
  `clientip` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`clientip`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE IF NOT EXISTS `Sessions` (
  `ids` int(10) unsigned NOT NULL,
  `clientip` int(10) unsigned NOT NULL,
  `time` int(3) NOT NULL,
  `bytes` int(10) NOT NULL,
  `status` int(1) unsigned NOT NULL,
  `lasttime` varchar(11) NOT NULL,
  PRIMARY KEY  (`ids`),
  KEY `date` (`clientip`,`time`,`bytes`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE IF NOT EXISTS `Traffic` (
  `idt` int(10) unsigned NOT NULL auto_increment,
  `date` varchar(11) NOT NULL,
  `clientip` int(10) unsigned NOT NULL,
  `resultcode` varchar(250) NOT NULL,
  `bytes` int(10) unsigned NOT NULL,
  `url` text NOT NULL,
  `peerip` int(10) unsigned NOT NULL,
  `mimetype` varchar(250) NOT NULL,
  PRIMARY KEY  (`idt`),
  KEY `date` (`date`,`clientip`,`resultcode`,`bytes`,`peerip`,`mimetype`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251;