iplog.txt
Logs the IP's users have visited the site from, and last time they visited using each IP. Can be used to detect people sharing ip or that have shared in past and present.
############ MySQL #############
CREATE TABLE `iplog` (
`userid` int(10) unsigned NOT NULL,
`ip` varchar(15) NOT NULL,
`last_visit` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`username` varchar(40) NOT NULL,
PRIMARY KEY (`userid`,`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
############ bittorrent.php ##############
function loggedinorreturn() {
global $CURUSER;
if (!$CURUSER) {
...
} else
{
mysql_query("REPLACE INTO iplog (userid,username,ip) values ('".$CURUSER['id']."','".$CURUSER['username']."','".getip()."')");
}
}
############ list ips shared with two or more users ##############
select ip,num from (select ip,count( ip ) as num from iplog group by ip) as z where num >= 2 order by num desc;
############ get a list of users who share(d) ip(s) with now banned users
############ temporary table needed to lower time from minutes to seconds in execution time
create temporary table bannedips select distinct iplog.ip from iplog left join users on (iplog.userid = users.id) where users.enabled="no";
select iplog.ip,iplog.username,iplog.userid,users.downloaded,users.uploaded,(users.uploaded/users.downloaded) as ratio,users.last_access,users.added
from users
left join iplog on (iplog.userid=users.id)
where users.enabled="yes" and iplog.ip in (
select ip from bannedips
) order by iplog.ip;
drop temporary table bannedips;