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

Question

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

You are underescaping the URL pattern and use delimiters that must not be used. Use REGEXP '(https?|ftps?)?(://)?[a-zA-Z0-9.-]+\.[a-zA-Z]{2,3}' — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 31, 2019 at 7:07
Thanks for that comment. The fix is already in place, copied from wrong DB, will edit the question. we changed [a-zA-Z0-9-.]+ to [-a-zA-Z0-9.]+ and it worked. — Aakash Sahai
– Aakash Sahai, Commented Jul 31, 2019 at 20:23
@Rick, correct. But on first note it should do the basic thing, then it will be evolved further. We are not able to take first step here or basic matching of regexp(irrespective of position) of needle. — Aakash Sahai
– Aakash Sahai, Commented Aug 3, 2019 at 6:17

being_ethereal · Accepted Answer · 2019-07-31 12:29:22Z

1

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.

edited Jul 31, 2019 at 12:29

answered Jul 31, 2019 at 7:11

being_ethereal

8859 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aakash Sahai Over a year ago

It is not working as expected. Before asking this question, I already gave a try to this solution. We have slang as one of the entries in profanity table, but it is still allowing [email protected] email to get inserted. Please help.

Aakash Sahai Over a year ago

Other example which should not be inserted are à[email protected], [email protected], [email protected], etc.

being_ethereal Over a year ago

@AakashSahai I tried this on my machine, it is working. Please check that you are inserting the row & not updating it. If you are updating rows, create a different trigger with BEFORE UPDATE ON.

Artog · Accepted Answer · 2019-08-07 12:56:49Z

0

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

answered Aug 7, 2019 at 12:56

Artog

1,1321 gold badge13 silver badges28 bronze badges

Comments

Nick · Accepted Answer · 2019-08-08 10:10:08Z

0

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

edited Aug 8, 2019 at 10:10

answered Aug 8, 2019 at 2:41

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

Paul Spiegel Over a year ago

ER_SIGNAL_EXCEPTION: Database Error: BAD REQUEST: email = '[email protected]', message = 'good'

Nick Over a year ago

@PaulSpiegel thanks for pointing that out, it's OPs regex for URLs which has too many optional bits. I've updated.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related