Ban certain sites from submitting...

[m37h0d]

Hello phpbuilders!

I was wondering how I would do this.. I am making a site where users can submit things off their sites, to make a long story short, I would like to ban certain sites from being able to submit.

There is a URL field on the submission page, so when they enter certain wildcard names in the URL field, I would like it to display "your site has been banned".

Say I banned google.com, how could I make it so it still said they were banned if they put in http://www.google.com, or www.google.com/blahblahblah?

This is the basic layout of my query:

$query = mysql_query("SELECT * FROM sitebans WHERE url LIKE '%$surl%'");
$myNum = mysql_num_rows($query);
if ($myNum > 0) {
die("Your site is banned!");
}

$surl is the sites URL coming from the submission form. This does not work though, because if I ban google.com, and they put in www.google.com, it will do this...
SELECT * FROM sitebans WHERE url LIKE www.google.com

and since I banned "google.com" it will return no rows..

Please help me with this, I really need to get this working 🙁

Thanks!

[dalecosp]

Well, MySQL has a "REGEXP" that might help, but IANAE on SQL, so YMMV....

🙂

[m37h0d]

Didn't understand a word you said, if you could explain a little it'd help me alot, thanks man!

[bpat1434]

IANAE = I Am No Expert
YMMV = Your Mileage May Vary (a.k.a. I don't know if it will work)

As to answer the question: The regex would look something like:

'.*$surl.*'

but I'm not 100% sure....

I have found a work-around:

$surl = $_POST['url'];

$query = "SELECT * FROM `m37h0d`";
$result = mysql_query($query) or die('mySQL Error:<br>'.mysql_error());

while($row = mysql_fetch_array($result))
{
	if(strpos($surl, $row['url']) !== FALSE)
	{
		$found = 1;
		break;
	}
	else
	{
		$found = 0;
	}
}

if($found === 1)
{
	echo '<h2 style="border: 2px groove #8e2323; padding: 5px 25px; background-color: #eeb4b4; color: #000; font-weight: bold; font-size: large;">Site Banned!</h2>';
}
else
{
	echo '<h2 style="border: 2px groove #308014; padding: 5px 25px; background-color: #b4eeb4; color: #000; font-weight: bold; font-size: large;">Site Allowed!</h2>';
}

It's a bit cumbersome, but it does what you want. I'm not fluent in mySQL's regex, but I couldn't get it to work your way. Honestly, RegEx in mySQL seems to be for you specifying a specific expression, not for looking for an expression built the way you need. Sorry....

[dalecosp]

m37h0d wrote:

Didn't understand a word you said, if you could explain a little it'd help me alot, thanks man!

You mean the abbreviations, or the advice?

$qry="select foo from bar where baz REGEXP 'garply'";

It's just slightly more powerful than LIKE, and should cause "www.google.com" and "google.com" to both appear to match your rule. Notice I say "should", I've not tried it myself (or, "YMMV" 😃 ).

[bpat1434]

Dale:
I tried last night, and you can't run a regex on a returned row. The way the regex should work is this:
SELECT 'Banned' FROM table WHERE submitted_url REGEXP '(.*)current_row/i'

Unfortunately, you can't do it or, I don't think you can. mySQL manual doesn't say anything about it. So it seems the only way to go would be to do the loop in my post...

Unless Sxooter has something to add... maybe I'll pm him...

[dalecosp]

😕

mysql> insert into sitebans values('google.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from sitebans;
+------------+
| name       |
+------------+
| google.com |
+------------+
1 row in set (0.00 sec)

mysql> select * from sitebans where name regexp "google";
+------------+
| name       |
+------------+
| google.com |
+------------+
1 row in set (0.00 sec)

mysql> 

The only issue I see is that the OP would want to, somehow, isolate the 2nd level domain, or the top and second-level, in the event of a larger URI string.

Of course, if you can simply pull the top and 2nd level out of the URI in PHP, then the LIKE should work in SQL just as well as the REGEXP... so, it might be better to advise that most of the work be done in PHP, anyhow.

Plate's awful full today, though...

[Sxooter]

Please note that I'm using MySQL 5.0.19 in ansi mode, where the || operator is the SQL concatenation operator. If you're running a version where it's not, there's some switch that will make it run in ansi mode. It's in the docs, you can look it up, or substitute the concat function. How's this for a self contained example:

create table regtest (id int primary key, reg text);
insert into regtest values (1,'abc');
select id from regtest where 'ab' like '%'||reg||'%';
Empty set (0.00 sec)
select id from regtest where 'abcd' like '%'||reg||'%';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

[Sxooter]

p.s. I think you can use the same trick with regex to build a regex string, but I haven't tested it.

[bpat1434]

Thanks Sxooter!! I knew you'd have a more enlightened answer than I would have!!

I just tested this (with the old CONCAT() function) and it seems to work:

<?php

if(isset($_POST) && !empty($_POST['url']))
{
	$conn = mysql_connect('localhost', 'username', 'password');
	mysql_select_db('database', $conn);

$surl = $_POST['url'];

//$query = "SELECT * FROM `m37h0d`";
$query = "SELECT * 
FROM `table_name`
WHERE '".$surl."' LIKE CONCAT('%', url, '%')";
$result = mysql_query($query) or die('mySQL Error:<br>'.mysql_error());

if(mysql_num_rows($result) >0)
{
	echo '<h2 style="border: 2px groove #8e2323; padding: 5px 25px; background-color: #eeb4b4; color: #000; font-weight: bold; font-size: large;">Site Banned!</h2>';
}
else
{
	echo '<h2 style="border: 2px groove #308014; padding: 5px 25px; background-color: #b4eeb4; color: #000; font-weight: bold; font-size: large;">Site Allowed!</h2>';
}

// For debugging purposes
// echo '<p style="border: 2px groove #000; background-color: #a4d3ee; padding: 5px 10px; color: #000; font-weight: normal; font-size: small;">', nl2br($query), '</p>';
}

?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Check to see if your URL is banned!!<br>
URL: <input type="text" name="url"><br>
<input type="submit" name="submit" value="Check">
</form>

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL auto_increment,
  `url` mediumtext NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `table_name` (`id`, `url`) VALUES (1, 'google.com'),
(2, 'altavista.com');