SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql bad word filter

    Hi,

    I have two tables

    texts
    Code:
    +-----+--------------+-------------------+--------------+-------+---------+---------+-------------+---------------------+
    | 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.
    Last edited by ScallioXTX; Aug 31, 2013 at 10:46.

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    733
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Something like this should work. (not tested)
    PHP Code:
    <?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. #3
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are a legend it has worked a treat.

    Major thank you

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,080
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    A few additions/changes to the above, if I may

    PHP Code:
    <?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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you I will be changing to pdo when I've got my head around it but this is brilliant


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •