SQL Email Ban List

[swizzles]

 :) Hi All

This was originally posted by cddvdheaven, thought those still using this can utilise this:

"Posted 16 December 2005 - 12:35 AM
Ok Got bored of seeing the same old users coming back and back again after getting banned for leeching the hell out of the site before there banned again.

To make it harder we now keep a log of all emails used on Disabled accounts !!!!! I know some people are going to hate that ????

The script log's all email address data from disabled accounts before the accounts are deleted from the trackers user table.

This way the takesignup script can also check against ex Banned users of the site rather then just current users. Staff can also enter email addresses or Free email providers domains into the ban list as well as they are found.This way any email addresses reported on other sites such a TPG can also be added to the banned list with ease by other staff rather then the Hard coded email blacklist in takesignup.php


WARNING I'M NOT A GOOD CODER SO THERE MAYBE ERROR'S AND AS IT USES THE CLEANUP SCRIPT AND DELETES USER DATA PLEASE BACK UP USER DATA FIRST

Add SQL Table

Code Select Expand


CREATE TABLE `email_bans` (
�`id` int(10) unsigned NOT NULL auto_increment,
�`userid` int(11) default '0',
�`added` datetime NOT NULL default '0000-00-00 00:00:00',
�`addedby` int(10) unsigned NOT NULL default '0',
�`comment` varchar(255) NOT NULL default '',
�`mail_domain` varchar(255) default NULL,
�PRIMARY KEY �(`id`)
) TYPE=MyISAM AUTO_INCREMENT=1;



Make file Called emailban.php

Code Select Expand


<?

require "include/bittorrent.php";

dbconn(false);

loggedinorreturn();

if (get_user_class() < UC_MODERATOR)
die;

$remove = $HTTP_GET_VARS['remove'];
if (is_valid_id($remove))
{
mysql_query("DELETE FROM email_bans WHERE id=$remove") or sqlerr();
write_log("Email Ban $remove was removed by $CURUSER[id] ($CURUSER[username])");
}

if ($HTTP_SERVER_VARS["REQUEST_METHOD"] == "POST" && get_user_class() >= UC_ADMINISTRATOR)
{
$mail_domain = trim($HTTP_POST_VARS["mail_domain"]);
$comment = trim($HTTP_POST_VARS["comment"]);
if (!$mail_domain || !$comment)
stderr("Error", "Missing form data.");
$mail_domain= sqlesc($mail_domain);
//if ($first == -1 || $last == -1)
//stderr("Error", "Bad IP address.");
$comment = sqlesc($comment);
$added = sqlesc(get_date_time());
mysql_query("INSERT INTO email_bans (added, addedby, mail_domain, comment) VALUES($added, $CURUSER[id], $mail_domain, $comment)") or sqlerr(__FILE__, __LINE__);
write_log("Ban $mail_domain was added by $CURUSER[id] ($CURUSER[username])");
header("Location: $BASEURL$HTTP_SERVER_VARS[REQUEST_URI]");
die;
}

ob_start("ob_gzhandler");


stdhead("Email Address OR Domain Bans");

if (get_user_class() >= UC_MODERATOR)
{
print("<h2>Add email Ban</h2>\n");
print("<table border=1 cellspacing=0 cellpadding=5>\n");
print("<form method=post action=emailban.php>\n");
print("<tr><td class=rowhead>Mail Address OR Domain To Ban</td><td><input type=text name=mail_domain size=40></td>\n");
print("<tr><td class=rowhead>Comment</td><td><input type=text name=comment size=40></td>\n");
print("<tr><td colspan=2><input type=submit value='Okay' class=btn></td></tr>\n");
print("</form>\n</table>\n");
}

$res2 = mysql_query("SELECT count(id) FROM email_bans") or die(mysql_error());
$row = mysql_fetch_array($res2);

$url = " .$_SERVER[PHP_SELF]?";
$count = $row[0];
$perpage = 40;
list($pagertop, $pagerbottom, $limit) = pager($perpage, $count, $url);


print("<h1>Current email Bans ($count)</h1>\n");

if ($count == 0)
print("<p align=center><b>Nothing found</b></p>\n");
else
{

echo $pagertop;

print("<table border=1 cellspacing=0 cellpadding=5 width=770>\n");
print("<tr><td class=colhead>Added</td><td class=colhead align=left>Mail Address Or Domain</td>".
"<td class=colhead align=left>Banned By</td><td class=colhead align=left>Comment</td><td class=colhead>Remove</td></tr>\n");

$res = mysql_query("SELECT * FROM email_bans ORDER BY added DESC $limit") or sqlerr();

while ($arr = mysql_fetch_assoc($res))
{
$r2 = mysql_query("SELECT username FROM users WHERE id=$arr[userid]") or sqlerr();
$a2 = mysql_fetch_assoc($r2);
//$arr["mail_domain"] = ($arr["mail_domain"]);
$r4 = mysql_query("SELECT username,id FROM users WHERE id=$arr[addedby]") or sqlerr();
$a4 = mysql_fetch_assoc($r4);

print("<tr><td>$arr[added]</td><td align=left>$arr[mail_domain]</td><td align=left><a href=userdetails.php?id=$a4[id]>$a4[username]"."</a></td><td align=left>$arr[comment]</td><td><a href=emailban.php?remove=$arr[id]>Remove</a></td></tr>\n");
}

print("</table>\n");
echo $pagerbottom;
}


stdfoot();
?>



Add this to takesignup.php
AFTER THIS

Code Select Expand


if (!validemail($email))
        bark("That doesn't look like a valid email address.");




$maildomain = (substr($email, strpos($email, "@")+1));
// check if email addy is already banned Ex User
$a = (@mysql_fetch_row(@mysql_query("select count(*) from email_bans where mail_domain='$email'"))) or die(mysql_error());
if ($a[0] != 0)
�bark("The e-mail address $Email is Banned All Been A Member And Banned.");

// check if email addy is already banned Free Provider
$a = (@mysql_fetch_row(@mysql_query("select count(*) from email_bans where mail_domain='$maildomain'"))) or die(mysql_error());
if ($a[0] != 0)
�bark("The e-mail address $Email is Banned Due To Being A Free Provider Address.");



Add this to cleanup.php
AFTER THIS

Code Select Expand


�mysql_query("DELETE FROM users WHERE id=$arr[id]");
�mysql_query("DELETE FROM messages WHERE receiver=$arr[id]");
�mysql_query("DELETE FROM friends WHERE userid=$arr[id]");
}



//delete Disabled user accounts
� �$msg = sqlesc("Banned User By System");
        $secs = 1*86400;
        $dt = sqlesc(get_date_time(gmtime() - $secs));
        $maxclass = UC_USER;
        $res = mysql_query("SELECT id,email FROM users WHERE enabled='no' AND status='confirmed' AND class <= $maxclass AND last_access < $dt");
if (mysql_num_rows($res) > 0)
{
        while ($arr = mysql_fetch_assoc($res))
        {

mysql_query("INSERT INTO email_bans (added , userid, addedby, comment, mail_domain) VALUES($dt, '$arr[id]', '1', $msg,'$arr[email]')") or sqlerr(__FILE__, __LINE__);
�//mysql_query("INSERT INTO email_bans (ip,email, userid) VALUES('$arr[ip]', '$arr[email]', '$arr[id]')") or sqlerr(__FILE__, __LINE__);
mysql_query("DELETE FROM users WHERE id=$arr[id]");
mysql_query("DELETE FROM messages WHERE receiver=$arr[id]");
mysql_query("DELETE FROM friends WHERE userid=$arr[id]");
}}



I hope someone will find a use for it other then myself ? LOL

Thanks to all the coders that will see there code in these scripts as i said i'm not a coder i pinch bits from here and there untill i get it to work and do what i want..
Thanks to Wilba for the last part I had trouble with in the cleanup script I had a brain block by the end of the night LOL


Any bug's found please help sort please....

This post has been edited by cddvdheaven: 30 December 2005 - 05:37 PM"

I hope peeps find this useful