9 Replies Latest reply: Jul 20, 2010 12:37 PM by insuractive RSS

    Message Profanity Sniffer

    EwokStud Community Member

      I've got an internal bulletin board and a guestbok for my CF site.  I established a table in my Access database called smut, which also contains other choice words.  The list is growing, however, I wish to filter out messages containing anything in the database smut table.  I also wish to create another listing (template) displaying the messages with the unwanted terms.  Though, this is more an SQL thing, what can CF do?

       

      Now, using the CFIF, it would be quite laborious to have a CFIF instance for each unwanted term.  CFLOOP would require the listing be delimited in the LIST attribute for CFLOOP, which negates the use of the database smut table, right.  How would you suggest referencing the smut table to use for filtering in CF?

       

      Doug

        • 1. Re: Message Profanity Sniffer
          EwokStud Community Member

          Addendum:

          I've got this (but the database table list of profanity is over 100+ flagged terms:

           

          <CFIF form.gb_entry_m CONTAINS "jerk" Or
          form.gb_entry_m CONTAINS "idiot" Or
          form.gb_entry_m CONTAINS "stupid" Or
          form.gb_entry_m CONTAINS "pig*" Or
          form.gb_entry_m CONTAINS "****">
          <HTML>
          <HEAD>
          <TITLE>OOPS</TITLE>
          </HEAD>
          <BODY>
          <H2>You have not worded your message in an acceptable manner.  Please re-word your message!</H2>
          <P>
          <A HREF="gb_nw.cfm"><B>BACK</B></A>
          </BODY>
          </HTML>
          </CFIF>

          <!--- when all the form data is good --->
          <CFQUERY NAME="q_ww_gb_nw" DATASOURCE="9130.ww">
          INSERT INTO gb_entries
          (gb_entry_dt,
          gb_entry_nm_f,
          gb_entry_nm_l,
          gb_entry_nm_l_dspl_x,
          gb_entry_tce,
          gb_entry_tce_dspl_x,
          gb_entry_cy,
          gb_entry_stt,
          gb_entry_m)
          VALUES (#form.gb_entry_dt#,'#form.gb_entry_nm_f#','#form.gb_entry_nm_l#',#form.gb_entry_nm_l_dsp l_x#,'#form.gb_entry_tce#',
          #form.gb_entry_tce_dspl_x#,'#form.gb_entry_cy#','#form.gb_entry_stt#','#form.gb_entry_m#')
          </CFQUERY>
          <!---
          <CFQUERY NAME="q_ww_gb_ud_dt" DATASOURCE="9130.ww">
          SELECT gb_entries.*
          FROM gb_entries
          WHERE #call_number#=gb_entryID
          </CFQUERY>--->

          <HTML>
          <HEAD>
          <TITLE>New Guestbook Entry Added!</TITLE>
          <CFOUTPUT QUERY="incl_css_bd">#incl_code#</CFOUTPUT>
          </HEAD>
          <BODY>
          <TABLE
            WIDTH="100%"
            HEIGHT="100%"
            BORDER="0">
          <TR>
            <TD ALIGN="center" VALIGN="top" CLASS="pagetitle">
            Guestbook
            </TD></TR><TR><TD CLASS="pagesubtitle" VALIGN="middle" ALIGN="center">
            Thanks for Signing the Guestbook!
            </TD>
          </TR>
          </TABLE>
          </BODY>
          </HTML>

          • 2. Re: Message Profanity Sniffer
            Dan Bracuk Community Member

            I'd do the laborious thing you are trying to avoid.

            • 3. Re: Message Profanity Sniffer
              insuractive Community Member

              What about something like this:

               

              1) Retrieve your database of profanity as a query (qBadWords):

               

              SELECT searchword

              FROM badwords

               

               

              2) Check against your form field:

               

              <cfset bValidationPassed = true>

              <cfloop query="qBadWords">

                   <cfif FindNoCase(searchword, Form.gb_entry_m)>

                        <cfset bValidationPassed  = false>

                        <cfbreak>

                   </cfif>

              </cfloop>

               

              3) If validation passes, great.  If not, display your message

               

              For a smaller collection of words, regular expressions might be a good fit - but probably not when you're dealing with 100+ records.

              • 4. Re: Message Profanity Sniffer
                EwokStud Community Member

                I did this: (see end of this post for my input.)

                 

                --------------------  begin gb_entry_verify.cfm template ---------------

                <CFQUERY NAME="q_profanity" DATASOURCE="ww">
                SELECT term
                FROM profanity;
                </CFQUERY>

                 

                <cfset bValidationPassed = true>
                <cfloop query="q_profanity">
                     <cfif FindNoCase(term, form.gb_entry_m)>
                          <cfset bValidationPassed  = false>
                          <cfbreak>
                     </cfif>
                </cfloop>

                 

                <HTML>
                <HEAD>
                <TITLE>New Guestbook Entry Added!</TITLE>
                <CFOUTPUT QUERY="incl_css_bd">#incl_code#</CFOUTPUT>
                </HEAD>
                <BODY>
                Your entry has been added.

                <BR>

                <A HREF="gb_entry_rvw.cfm?call_number=#gb_entryID#">Review Your Entry Now!</A>
                </BODY>
                </HTML>

                --------------------  end gb_entry_verify.cfm template ---------------

                 

                The above was used in the page which follows the "Add an Entry" page, named gb_nw.cfm; the "check" page is the page that follows, which is gb_entry_verify.cfm.

                 

                I created a new entry for my guestbook, as though I was a visitor.  In the textarea field, I typed "****" and the verification page did not detect the entry.

                Of course I have the field "term" in the table "profanity".

                 

                I think I need two templates, or two HTML resulting sets of code.  In other words a "thank you" template and an "else" template (the oops code).

                 

                Please help here.  So far, I believe your cfloop may work, but without the opposing result templates, there's no way to tell.

                 

                Doug

                • 5. Re: Message Profanity Sniffer
                  insuractive Community Member

                  The code I suggested results in a variable either being set to true or false.  Based on the outcome of that variable, you should be able to display the correct template to your users:

                   

                  <!--- query & loop goes here --->

                   

                  <!--- No profanity --->

                  <cfif bValidationPassed  eq true>

                   

                     <!--- insert submission --->

                     <!--- display confirmation to user --->

                   

                  <!--- uh oh, profanity detected --->

                  <cfelse>

                   

                     <!--- do whatever you are going to do when profanity is detected: send email, display message --->

                   

                  </cfif>

                  • 6. Re: Message Profanity Sniffer
                    EwokStud Community Member

                    I am almost there.  With a little insight into the code you provided me

                    , I came up with this:

                    <CFQUERY NAME="q_profanity" DATASOURCE="9130.ww">
                    SELECT term
                    FROM profanity;
                    </CFQUERY>

                    <HTML>
                    <HEAD>
                    <TITLE>New Guestbook Entry</TITLE>
                    </HEAD>
                    <BODY>
                    <TABLE
                      WIDTH="100%"
                      HEIGHT="100%"
                      BORDER="0">
                    <TR>
                      <TD ALIGN="center" VALIGN="middle">

                    <cfset bValidationPassed = true>
                    <cfloop query="q_profanity">
                         <cfif FindNoCase(term, form.gb_entry_m)>
                       You have included a term or phrase which is unacceptable to our records in your guestbook message.         <cfset bValidationPassed  = false>
                              <cfbreak>
                         </cfif>
                    </cfloop>
                      Your guestbook message has been saved.

                     

                    <BR>
                    <A HREF="gb_entry_rvw.cfm?call_number=#gb_entryID#">Review Your Entry Now!</A>
                    </TD>
                    </TR>
                    </TABLE>
                    </BODY>
                    </HTML>

                     

                    It's just swapping out the message centered on teh page, but I get "Your message has been saved." in addition to the "You have included..." message.

                     

                    I know I'm close - I just need to have only one statment or the other display.

                     

                    Doug

                    • 7. Re: Message Profanity Sniffer
                      insuractive Community Member

                      Close, but I can spot a couple things wrong with the logic there right off the bat:

                       

                      1) Your code is missing the INSERT statement in order to add the data to your database (on success) - without it your user's post will never make it into your system

                      2) You need to make the message that appears after the validation runs conditional on the results of the validation.  As you've coded it now, your "success" message is hard coded so that it appears regardless of whether the validation has passed or not.

                       

                      Try the following blueprint:

                       

                      <!--- Set Default Values --->

                      <cfset bValidationPassed = true>
                      <cfset sMessage= "">

                      <!--- Retrieve Words --->

                      <CFQUERY NAME="q_profanity" DATASOURCE="9130.ww">
                      SELECT term
                      FROM profanity;
                      </CFQUERY>

                       

                      <!--- Detect the use of stored words --->

                      <cfloop query="q_profanity">
                           <cfif FindNoCase(term, form.gb_entry_m)>
                                 <cfset bValidationPassed  = false>
                                <cfbreak>
                           </cfif>
                      </cfloop>

                      <!--- Conditional logic depending on whether profanity term was detected --->

                      <cfif bValidationPassed eq true>

                           <!--- No profanity: add post --->

                           <CFQUERY NAME="q_AddRecord" DATASOURCE="9130.ww">

                             [INSERT SQL HERE]

                           </CFQUERY>


                           <!--- Set message  text --->

                           <cfsavecontent variable="sMessage ">

                           <p>Your guestbook message has been saved.</p>

                       

                           <!--- you will need to determine the new entry ID in the Add Record SQL above --->

                           <p><A HREF="gb_entry_rvw.cfm?call_number=#gb_entryID#">Review Your Entry Now!</A></p>

                           </cfsavecontent>

                       

                      <cfelse>

                           <!--- If profanity detected, show error text --->

                           <cfset sMessage = "You have included a term or phrase which is unacceptable to our records in your guestbook message. ">

                      </cfif>

                       

                      <!--- Generate output HTML --->

                      <HTML>
                      <HEAD>
                      <TITLE>New Guestbook Entry</TITLE>
                      </HEAD>
                      <BODY>

                      <!--- display message --->

                      <cfoutput>#sMessage#</cfoutput>

                      </BODY>
                      </HTML>

                      • 8. Re: Message Profanity Sniffer
                        EwokStud Community Member

                        Thank you very much!  The "blueprint" was superb!

                         

                        You mentioned installing the gb_entryID into the SQL to enable the person to review their entry.  I copy and pasted a snippet from your blueprint, because I tried the where clasue and it didn't work.

                         

                             <CFQUERY NAME="q_AddRecord" DATASOURCE="9130.ww">
                        INSERT INTO gb_entries
                        (gb_entry_dt,
                        gb_entry_nm_f,
                        gb_entry_nm_l,
                        gb_entry_nm_l_dspl_x,
                        gb_entry_tce,
                        gb_entry_tce_dspl_x,
                        gb_entry_cy,
                        gb_entry_stt,
                        gb_entry_m)
                        VALUES (#form.gb_entry_dt#,'#form.gb_entry_nm_f#','#form.gb_entry_nm_l#',#form.gb_entry_nm_l_dsp l_x#,'#form.gb_entry_tce#',
                        #form.gb_entry_tce_dspl_x#,'#form.gb_entry_cy#','#form.gb_entry_stt#','#form.gb_entry_m#')

                        WHERE call_number="gb_entryID";
                             </CFQUERY>

                             <!--- Set message text --->
                             <cfsavecontent variable="sMessage">
                             <p>Your guestbook entry has been saved.</p>

                             <!--- you will need to determine the new entry ID in the Add Record SQL above --->
                             <p><A HREF="gb_entry_rvw.cfm?call_number=#gb_entryID#">Review Your Entry Now!</A></p>
                             </cfsavecontent>

                        • 9. Re: Message Profanity Sniffer
                          insuractive Community Member

                          There is no WHERE clause in a regular INSERT statement.  What you need to do is:

                           

                          1) Perform your insert statement as you normally would

                          2) Once you have inserted your new record, select the ID for that record and return it to your CF application so you can use it in your link

                           

                          Something like this:

                           

                          <CFQUERY NAME="q_AddRecord" DATASOURCE="9130.ww">

                          <!--- Insert statement --->
                          INSERT INTO gb_entries
                          (gb_entry_dt,
                          gb_entry_nm_f,
                          gb_entry_nm_l,
                          gb_entry_nm_l_dspl_x,
                          gb_entry_tce,
                          gb_entry_tce_dspl_x,
                          gb_entry_cy,
                          gb_entry_stt,
                          gb_entry_m)
                          VALUES (#form.gb_entry_dt#,'#form.gb_entry_nm_f#','#form.gb_entry_nm_l#',#form.gb_entr y_nm_l_dspl_x#,'#form.gb_entry_tce#',
                          #form.gb_entry_tce_dspl_x#,'#form.gb_entry_cy#','#form.gb_entry_stt#','#form.gb_ entry_m#')

                           

                          <!--- Get the ID associated with inserted record --->

                          SELECT @@identity as 'inserted_id'

                          </CFQUERY>

                           

                          There are a number of different methods for determining the ID of a recently added record.  @@identity will work if you are working on a SQL Server DB backend.  Below is a link that outlines some of the functionality you have available to use on SQL Server:

                           

                          http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_cur rent-retrieve-last-inserted-identity-of-record/

                           

                          Afterwards, you can reference the result of @@identity statement: q_AddRecord.inserted_id

                           

                          If you are not on a SQL Server box, I'm sure there are other similar functions in other DB software.  A quick google search should turn up the right one.  If all else fails, you can always resort to:

                           

                          SELECT max(gb_entry_id) 'inserted_id'

                          FROM gb_entries
                          WHERE gb_entry_dt = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#form.gb_entry_dt#">

                           

                          Notice the use of the <cfqueryparam> tag above.  I would highly recommend that you start incorporating that tag in your query SQL.  It will provide you some additional protection against SQL injection attacks as well as a slight speed boost to your queries.

                           

                          Once you've determined the newly inserted ID in your database, you can use that variable in your Link as the value of your call_number URL variable.