[SOLVED] Preventing certain words being posted?

[prawn_86]

Hi all,

 

I have the following site being attacked by spammers:

www.freefreeads.com

 

I want to know how to make it so that if certain words are typed in they will not appear, it will simply give an error message, or just not appear (error message is optional)

 

Im not sure if this needs to be done through PHP or SQL, but if you need it my PHP code is:

 

<?php
//time to show you what you have in the database
$sql = "SELECT * FROM `test_table` ORDER BY `id` DESC LIMIT 5000;";
$result = mysql_query($sql);
print "			<table border=\"0\">\n";
while ($row = mysql_fetch_assoc($result)){
$title = $row['title'];
$url = $row['url'];
	echo "				<tr>
				<td><a href=\"$url\" target=\"http://www.freefreeads.com\">$title</a></td>
			</tr>
";
}
print "			</table>\n";
?>

 

Any help much appreciated as these spammers are mis-using my free service

[kickstart]

Hi

 

I cannot think of a way of doing this in a single SQL statement. In your other thread I suggested a few other options, and I would encourage you to stop them posting in the first place rather than just suppressing their posts.

 

However, try this. All it is doing is building up a list of like clauses for the SQL for the excluded words (I have assumed a table of excluded words). This will be OK for a limited number of words, but if the number gets too many it will cause problems (ie, not 100% sure on MySQL, but some versions of SQL have a limit of ~255 where clauses). Also take care with the contents of the excluded words table as I have put nothing in there to prevent a potential sql injection attack.

 

<?php

$sql = "SELECT excluded_word FROM `word_exclusion_table`";
$result = mysql_query($sql);
$whereclause = "";
while ($row = mysql_fetch_assoc($result)){
$whereclause .= (($whereclause) ? ' AND ' : ' WHERE ')."title NOT LIKE '%".$row['excluded_word']."%' ";
}


//time to show you what you have in the database
$sql = "SELECT * FROM `test_table` $whereclause ORDER BY `id` DESC LIMIT 5000;";
$result = mysql_query($sql);
print "			<table border=\"0\">\n";
while ($row = mysql_fetch_assoc($result)){
$title = $row['title'];
$url = $row['url'];
	echo "				<tr>
				<td><a href=\"$url\" target=\"http://www.freefreeads.com\">$title</a></td>
			</tr>
";
}
print "			</table>\n";
?>

 

All the best

 

Keith

[elflacodepr]

Apart from filtering words, set a limit time for each post

[prawn_86]

just realised i also have this bit of code on my page:

 

//process any requests they have sent
if (isset($_GET['submit']) && $_GET['submit'] == true){
$title = mysql_real_escape_string($_POST['title']);
$url = mysql_real_escape_string($_POST['url']);
$sql = "INSERT INTO `test_table` VALUES('', '$title', '$url');";
mysql_query($sql);

 

Is this where i should run the above statement (ie checking against the banned words table? Or should it be run in the section i have posted above?

[prawn_86]

I tired the above code that you mentioned Keith and it doesnt seem to work, i have populated the exclusion table with a few wordss, but it still posts them up to my page in the tests i ran  ???

 

<?php

$sql = "SELECT excluded_word FROM `word_exclusion_table`";
$result = mysql_query($sql);
$whereclause = "";
while ($row = mysql_fetch_assoc($result)){
$whereclause .= (($whereclause) ? ' AND ' : ' WHERE ')."title NOT LIKE '%".$row['excluded_word']."%' ";
}

//time to show you what you have in the database
$sql = "SELECT * FROM `test_table` ORDER BY `id` DESC LIMIT 5000;";
$result = mysql_query($sql);
print "			<table border=\"0\">\n";
while ($row = mysql_fetch_assoc($result)){
$title = $row['title'];
$url = $row['url'];
	echo "				<tr>
				<td><a href=\"$url\" target=\"http://www.freefreeads.com\">$title</a></td>
			</tr>
";
}
print "			</table>\n";
?>

[kickstart]

Hi

 

You need to put $whereclause in your second SELECT statement.

 

Ie:-

 

$sql = "SELECT * FROM `test_table` $whereclause ORDER BY `id` DESC LIMIT 5000;";

 

All the best

 

Keith

[prawn_86]

Thanks Keith, your a life saver!

 

I also added an additional line in there so i can build a list up of banned URLs. I just added the same $whereclause but changed title to url and it seems to work fine

 

$sql = "SELECT excluded_word FROM `word_exclusion_table`";
$result = mysql_query($sql);
$whereclause = "";
while ($row = mysql_fetch_assoc($result)){
$whereclause .= (($whereclause) ? ' AND ' : ' WHERE ')."title NOT LIKE '%".$row['excluded_word']."%' ";
$whereclause .= (($whereclause) ? ' AND ' : ' WHERE ')."url NOT LIKE '%".$row['excluded_word']."%' ";
}