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 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

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, Jul 31 '19 at 07: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, Jul 31 '19 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, Aug 03 '19 at 06:17
Did you try the answer from your first link? `IF EXISTS (SELECT * FROM profanity p WHERE new.email LIKE CONCAT('%', p.word, '%') ) THEN ...` — Paul Spiegel, Aug 07 '19 at 19:31
This might be a bad idea. How many entries do you have in your profanity words table? — wp78de, Aug 09 '19 at 23:14

being_ethereal · Answer 1 · 2019-07-31T12:29:22.610

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 '19 at 12:29

answered Jul 31 '19 at 07:11

being_ethereal

795
7
26

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 `slangword@hotmail.com` email to get inserted. Please help. – Aakash Sahai Jul 31 '19 at 20:20
Other example which should not be inserted are `àbcslang@test.com`, `helloWorld@slang.com`, `myslangemail@gmial.com`, etc. – Aakash Sahai Jul 31 '19 at 20:26
@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`. – being_ethereal Aug 08 '19 at 11:22

score 0 · Answer 2 · answered Aug 07 '19 at 12:56

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

Nick · Answer 3 · 2019-08-08T10:10:08.190

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

`ER_SIGNAL_EXCEPTION: Database Error: BAD REQUEST: email = 'good@good.yes', message = 'good'` — Paul Spiegel, Aug 08 '19 at 09:36
@PaulSpiegel thanks for pointing that out, it's OPs regex for URLs which has too many optional bits. I've updated. — Nick, Aug 08 '19 at 10:10

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

3 Answers3