php/mysql - comparing strings to see if an email is banned or not

[theMAD2]

Hi

I'm trying to figure out the best way of checking an email address against a ban list which may have whole email addresses or just domains. I was thinking take all the bans from my banned email table, fetch them into an array, and then use a php function to compare the two strings and see if the banlist rows are contained within the email address entered. I've not done much work with strings, would preg_match be the best option for this?

Any help appreciated
Cheers
Dan

 

[Beansprout]

Do it in SQL

$conn = mysql_connect(blah);

$sql = "SELECT email from table WHERE email LIKE '%$email%'";

$res = mysql_query($sql, $conn);

if (mysql_num_rows($res) != 1) {
echo "No e-mail matches!"
}

To check domains, use explode() to cut out everything before the @ then you're left with the right-hand side of the address (ie the domain) and you can do another query like the above to see if you get any rows returned

 

[theMAD2]

Do it in SQL

$conn = mysql_connect(blah);

$sql = "SELECT email from table WHERE email LIKE '%$email%'";

$res = mysql_query($sql, $conn);

if (mysql_num_rows($res) != 1) {
echo "No e-mail matches!"
}

To check domains, use explode() to cut out everything before the @ then you're left with the right-hand side of the address (ie the domain) and you can do another query like the above to see if you get any rows returned

That's similar to what I have, but it's the domain checking bit I'm having trouble with. It's working if a whole address is banned but not the domain only. So far I'm thinking something like this:

$email = "[email protected]";
$ban = "bar.net";
if (stripos($email,$ban)) {;
	print "Email is banned";
	} else {
	print "Email is ok";
	};

Maybe if I put that in a loop fetching the rows from the ban list. Not sure if that's the right function to use lol

 

[Sic]

are you banning domains and emails? i take it they're in different tables. could do something like:

$email = $_REQUEST['email'];

//database stuff

$query = mysql_query(SELECT * FROM banned_list);
$num = mysql_num_rows($query);

for ($i=0; $i<$num; $i++)
{
$bannedEmails[$i] = mysql_fetch_array($query);
}

foreach ($bannedEmails as $emailCheck)
{
if (stristr($emailCheck, $email) == TRUE)
{
echo "oops, naughty email address";
}
else
{
echo "your email's all good";
}
}

that should work

 

[Beansprout]

Maybe if I put that in a loop fetching the rows from the ban list. Not sure if that's the right function to use lol

You don't need to - you can use SQL's LIKE facility to search for matches. '%' is a wildcard, so if you're looking to check a domain only then you can split off the domain from the address using PHP's explode() function (separate at the @) and then put the domain into, say, $domain, then do:

$sql = "SELECT email from table WHERE email LIKE '%$domain'";

No need for a wildcard (%) at the end because the domain must be the end of the address

Then you just check how many rows you got given...for a domain, you'll need to check that the number of rows isn't greater than 0, and for an e-mail you can check that you return only one row (assuming your database is normalised and e-mail addresses aren't repeated, otherwise, you'll need to check for the same as the domain - that you don't have any rows returned - a row returned means that entry is on the ban list.)

 

[theMAD2]

You don't need to - you can use SQL's LIKE facility to search for matches. '%' is a wildcard, so if you're looking to check a domain only then you can split off the domain from the address using PHP's explode() function (separate at the @) and then put the domain into, say, $domain, then do:

$sql = "SELECT email from table WHERE email LIKE '%$domain'";

No need for a wildcard (%) at the end because the domain must be the end of the address

Then you just check how many rows you got given...for a domain, you'll need to check that the number of rows isn't greater than 0, and for an e-mail you can check that you return only one row (assuming your database is normalised and e-mail addresses aren't repeated, otherwise, you'll need to check for the same as the domain - that you don't have any rows returned - a row returned means that entry is on the ban list.)

That seems to have done it! This is the final code for the email check, it just checks to see if it's banned or not but I'll use the logic for the actual test.

<?php
include_once "config.php";

$address = explode('@',$_POST['q']);

$email = $_POST['q'];
$domain = $address[1];

//Connect to the database and reference to $link
$link = @mysql_connect(DB_HOST,DB_USER,DB_PASS)
		OR die('I cannot conenct to the database: ' . mysql_error());

//Select the database we want to use
		@mysql_select_db(DB_NAME)
		OR die('Could not select the database: ' . mysql_error());

//If there is a search entered, add it to the query
if (!empty($_POST['q'])) {
	$query = "SELECT * FROM banlist WHERE email LIKE '%" . $_POST['q'] . "%'";

//Run the search
$result = @mysql_query($query)
			OR die('Could not perform the query because: ' . mysql_error());	

print "<p>'" . $_POST['q'] . "' ";

if (mysql_num_rows($result) == 0) {
	$domainquery = "SELECT * FROM banlist WHERE email LIKE '%" . $domain . "'";
	$result = @mysql_query($domainquery)
			OR die('Could not perform the query because: ' . mysql_error());
			
	if (mysql_num_rows($result) == 0) {
		print "is <b>not banned</b>.</p>";
		} else {
		print "is <b>banned</b>.</p>";
		};
		
	} else {
	print "is <b>banned</b>.</p>";
	};
}

?>

Thanks again for all your help