0

I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it.

so the idea is, I want to check these records and change these bad words with a couple of stars (like ****) the number of stars is fixed and not dependant on words length.

and I have a bad words table called slang.

so what I have so far is like this:

I have two trigger for each table (blog_post and image), one for update and one for insert. so the bad words will filtered out on insert or update. the content of each trigger is the same as below:

BEGIN
  DECLARE i INT;
  DECLARE s VARCHAR(1000);
  DECLARE r VARCHAR(1000);
  SET i = 1;
  SET s = '';
  REPEAT
    SET s = (
      SELECT REPLACE(split, COALESCE(slang, ''), '***') FROM
        (SELECT strSplit(new.text, ' ', i) AS split) s
        LEFT JOIN slang w ON s.split = w.slang
      LIMIT 1
      );
    SET r = CONCAT_WS(' ', r, s);
    SET i = i + 1;
    UNTIL s IS NULL
  END REPEAT;
  SET new.text = r;
END

the strSplit procedure is defined like so (based on a StackOverflow or maybe a StackExchange question I don't remember it):

BEGIN
  DECLARE output VARCHAR(1000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END

so I want to make a trigger to update the blog posts and images text field each time we add or edit a record from slang table. so if I add someSlang into slang table, all images and blog posts that contain someSlang in their text field should get updated and place **** for each instance of someSlang.

I'll appreciate any help and hints.

1
  • It's trivial to bypass your technique by using zero-width characters btw.
    – Dai
    Nov 14, 2021 at 7:07

1 Answer 1

0

The slangs table might have a few hundred entries. Whereas the blog_posts and image table might have a lot more data(in the range of millions depending on the traffic). So if an insert or update triggers an update in the other two tables, it might create an unwanted spike in database activity. A better approach would be to plan a Scheduler to do this task.

5
  • you mean like cronjobs and php? that would be a good idea but i think that would make a lot of update queries per record? Mar 24, 2019 at 5:48
  • And that is worse why? You seem to think "ok, many updates bad" totally ignoring that if you have a million blog posts and use this trigger, you may block the db for minutes. Using a scheduler and partial processing in a loop - with a lot of smaller updates - is WAY better than dealing with monster updates in a live application. Obviously it does need to be evaluated. Also you REALLY have a logical problem in that your filtering can not be undone AND is substring based. That SCREAMS "i killed my data by admin error", particularly if you go by substring and find partial word matches where...
    – TomTom
    Jun 7, 2021 at 10:55
  • 1
    ...a part of the word is a slang, but not as part of a different word. Example? SEX - not a nice word maybe. Except it is ALSO part of Essex, which happens to be the name of a region. Oh, you split by " " - that means "and it is sex, except" is not found as the word here would be found as "sex,", not "sex". Heck, you do not even find "'sex'" (see the ' around?) with the strsplit approach - so quotes... are out... The whole thing is WAY more complicated than the edge case trigger likes.
    – TomTom
    Jun 7, 2021 at 10:58
  • @TomTom Would you agree that attempting to write a naughty word filter like the OP is describing is a fool's errand?
    – Dai
    Nov 14, 2021 at 7:13
  • Yes. I would say it is extremely hard to pull off as a major project OUTSIDE of SQL (particularly given how SQL is EXTREMELY poor in terms of really complex string manipulation), and any attempt to do anything really meaningful in SQL is indeed imho a fools errand. There are way to many edge cases and SQL is a particularly bad language to handle this type of work.
    – TomTom
    Nov 15, 2021 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.