#StackBounty: #mysql #regex Combination of SUBSTR & REGEXP match on other table multiple values in MySQL

Bounty: 50

GOAL: Need a SQL logic for my trigger to block insert of dirty-words(from a table) found anywhere in the passed string.

DESCRIPTION: Few days back I was working on a SQL trigger to achieve this. I was able to complete it but on testing it was found that only exact words are getting matched. Please refer below examples.

PROFANITY TABLE:

| word |

somebadword
somedirtyword
somegarbageword
someslangword

Using the trigger created, if new.email is somebadword or somedirtyword then only it is blocking the insert and throwing signal 45000 error. What I want is that if new.email is testingsomebadword@somedomain.com then also it should throws signal 45000 error. Some substr/regexp search logic need to be integrated.

TRIGGER:
DELIMITER $$
CREATE TRIGGER trg_contact_insert_profanity_check BEFORE INSERT ON contacts
FOR EACH ROW BEGIN
IF ((new.email IN (SELECT word FROM profanity)) OR (new.message
IN (SELECT word FROM profanity)) OR (new.message REGEXP '/(http|https|ftp|ftps)?(://)?[a-zA-Z0-9.-]+.[a-zA-Z]{2,3}(/S*)?/' ))
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Database Error: BAD REQUEST";
END IF;
END;$$

EXPECTATION & REFERENCES:
Gone through various reference to achieve this but no success. Expecting my solution might be combination of REF1+Ref2. Looking for a preferably optimized(by performance) solution:

Ref 1: mysql select in list with regexp/substring (closest to my needs)

Ref 2: MySQL Like multiple values

Ref 3: INSTR

DB Version: 5.7.23


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.