Mysql bad word filter

PHP
1

Hi,

I have two tables

texts


+-----+--------------+-------------------+--------------+-------+---------+---------+-------------+---------------------+
| id  | sender       | content           | inNumber     | email | credits | forward | messagesent | datereceived  |
+-----+--------------+-------------------+--------------+-------+---------+---------+-------------+---------------------+

banned
+-----+------+
| id  | word |
+-----+------+
| 115 | bad word |

What I would like help with is if the content in texts col contains a bad word from banned table and word col then it will update the message sent col to 1

this will ensure that if a bad word is sent in then the SMS is classed as displayed and will not be displayed.

2

Something like this should work. (not tested)

<?php
	$badwords = array();
	$sql = "SELECT word FROM banned";
	$result = mysql_query($sql) or die(mysql_error());
	while($row = mysql_fetch_array($result)){
		$badwords[] = $row['word'];
	}
	
	$records = array();
	$sql = "SELECT id,content FROM texts WHERE messagesent = '0'";
	$result = mysql_query($sql) or die(mysql_error());
	while($row = mysql_fetch_array($result)){
		$records[$row['id']] = $row['content'];
	}
	
	
	foreach($records as $record_id => $content){
		$badfound = array();
		$words = explode(" " ,$content);
		foreach($words as $word){
			$word = strtolower($word);
			if(in_array($word,$badwords)){
				$badfound[] = "found";
			}		
		}
		if(count($badfound)>=1){
			$sql = "UPDATE texts SET messagesent = '1' WHERE id = '$record_id'";
			$result = mysql_query($sql) or die(mysql_error());		
		}	
	}
?>
3

You are a legend it has worked a treat.

Major thank you

4

A few additions/changes to the above, if I may


<?php
$records = array();
$sql = "SELECT id,content FROM texts WHERE messagesent = '0'";
$result = mysql_query($sql) or die('Error fetching text messages');
while($row = mysql_fetch_array($result)){
    $records[$row['id']] = $row['content'];
}

if(count($records)){
    $badwords = array();
    $sql = "SELECT word FROM banned";
    $result = mysql_query($sql) or die('Error fetching banned words');
    while($row = mysql_fetch_array($result)){
        $badwords[] = strtolower($row['word']);
    }
    foreach($records as $record_id => $content){
        $badfound = false;
        $words = array_map('trim', explode(' ', strtolower($content)));
        foreach($words as $word){
            if(in_array($word,$badwords)){
                $badfound = true;
                break;
            }       
        }
        if($badfound===true){
            $sql = "UPDATE texts SET messagesent = '1' WHERE id = '$record_id'";
            $result = mysql_query($sql) or die('Error update text message');
        }   
    }
}
?>

The changes are:

  • Only load the bad words if there are texts to be checked. If there are no messages loading the bad words is a waste of time.
  • Convert bad words to lower case so the comparison is completely case insensive
  • Don’t die(mysql_error()), since that can potentially give away too much information for hackers (table names, field names, etc).
  • Trim all words after explode()ing the text to avoid checking if there are spaces in the bad words (not needed, and you have a huge problem if a space were ever to end up in there)

In addition:

  • Besides setting messagesent=1, I would also set message_blocked_for_banned_word=1 or something, so you can see that the message wasn’t actually sent and why (you don’t show this to the user, but it might be relevant to the admin(s) of the sit
  • mysql_* is deprecated. You should switch to either mysqli_ or PDO (the latter is preferred). Also switch to prepared statements to prevent SQL Injection
5

Thank you I will be changing to pdo when I’ve got my head around it but this is brilliant