CODEWITHSUNDEEP
mysqlregex
Aakash Sahai
Aakash Sahai

Reputation: 4004

Combination of SUBSTR & REGEXP match on other table multiple values in MySQL

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 [email protected] 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

Upvotes: 1

Views: 445

Answers (3)

Nick
Nick

Reputation: 147216

This trigger should do what you want. It uses an EXISTS check to see if the email or message is like a word in the profanity table:

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

Note that your regex is wrong, MySQL regexes do not require delimiters, so I have removed them. Plus you have too many optional parts in the URL regex so it matches a plain string. I've removed some of that. Also I've enhanced the error message to include the bad values.

Demo on dbfiddle

Upvotes: 0

Artog
Artog

Reputation: 1142

Using count and concat can probably achieve what you want. I have no way of testing the actual trigger, but please let me know if it works:

TRIGGER:
DELIMITER $$
CREATE TRIGGER trg_contact_insert_profanity_check BEFORE INSERT ON contacts
FOR EACH ROW BEGIN
IF (
   (
       select count(*) 
       from profanity p
       where new.email like concat('%',p.word,'%')
          or new.message like concat('%',p.word,'%')
   ) > 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Database Error: BAD REQUEST";
END IF;
END;$$

Made a fiddle to test it out

Upvotes: 0

being_ethereal
being_ethereal

Reputation: 825

Following works:

TRIGGER:
DELIMITER $$
CREATE TRIGGER trg_contact_insert_profanity_check BEFORE INSERT ON contacts
FOR EACH ROW BEGIN
IF ((new.email REGEXP (SELECT GROUP_CONCAT(word SEPARATOR '|') FROM profanity)) OR (new.message
REGEXP (SELECT GROUP_CONCAT(word SEPARATOR '|') 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;$$

For optimization, I would suggest create a variable and assign the output of SELECT GROUP_CONCAT(word SEPARATOR '|') FROM profanity before for loop & use that variable inside the loop.

Upvotes: 1

Related Questions