1

I have a few tables in SQL that require content filtering, primarily for profanity. I want to allow my application(s) to insert data they want and have the server replace any profanity with asterisks such that I do not need to implement filtering on a variety of platforms.

I know triggers could be used for future, however, I am trying to determine the most efficient way to complete this task.

Here are some details: There are 2 tables I need to ensure has content filtering as they are public facing: feedback and users. Here are the particular fields:

Table -> Fields

Feedback -> Subject, Message Users -> Firstname, Lastname, Alias

I am relatively new to MySQL and know that having a table of values to replace may be the easiest-to-modify option.

My question is:

How would I join 2 tables and replace particular chars with asterisks using key words located in a third table?

I have these queries so far to locate the columns of interest, just not sure how to incorporate the replacement function and the ability to check both at the same time:

SELECT u.firstname, u.lastname, u.username FROM users u, feedback f, terms t;

SELECT f.subject, f.message FROM feedback f;

Jstngoulet
  • 1,055
  • 7
  • 12
  • ah an oldie but a goodie: https://stackoverflow.com/questions/273516/how-do-you-implement-a-good-profanity-filter – xQbert Aug 18 '17 at 23:24
  • thanks @xQbert, I saw that, however, I do not have access to the PHP code and want to keep this directly within the SQL Server as directed.. PHP would be so much easier. – Jstngoulet Aug 18 '17 at 23:30
  • Function call in trigger before update that uses a regular expression your word list and recursion to replace known offenders you could track offending user and require approvals for their posts over time when exceeding thresholds – xQbert Aug 18 '17 at 23:32

1 Answers1

1

You are better off creating a new column (named alias or similar) and storing values with asterisks in there than writing a SELECT query and performing find-replace. Following are the advantages:

  • Handling this scenario in trigger means you will only perform this operation when a record gets inserted or updated, whereas in SELECT query, each read will need replacing.
  • You can't really use join here because (a) each value of feedback and user table needs to be compared with all the values of terms table and (b) this needs to be performed for all the columns that might contain these words. So, it's more of a use case for cursor than join.
aschultz
  • 1,658
  • 3
  • 20
  • 30
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • I would first want to change out the existing values, then incorporate a trigger, if possible. Would the new column be in the terms table (so the terms table would supply the term and replacement text in two different columns? – Jstngoulet Aug 21 '17 at 00:18