• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Message Profanity Sniffer

New Here ,
Jul 18, 2010 Jul 18, 2010

Copy link to clipboard

Copied

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

Views

15.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Advocate , Jul 20, 2010 Jul 20, 2010

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

...

Votes

Translate

Translate
New Here ,
Jul 18, 2010 Jul 18, 2010

Copy link to clipboard

Copied

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 "ass*">
<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_dspl_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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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 "shit" 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

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_dspl_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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

LATEST

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_current-retri...

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 18, 2010 Jul 18, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation