MYSQL: IF having OR condition & REGEXP match

Question

Goal: To block inserts into database table using trigger having multiple conditions

Description: Trying to block lots of irrelevant entries in contact table. I have created a profanity table having lot of bad/swear/dirty words and a regular expression filtering URL. If any entry comes to DB having these bad words or URL then it should not be inserted.

Analysis: Searched many different solution over SO and could be duplicate but didn't found any answer having multiple condition and regexp in a single trigger condition. Also, not able to create a successful trigger using those answers. While creation, it is throwing error. Please help, thanks in advance.

TABLE COLUMNS EMAIL, MESSAGE

TRIGGER

DELIMITER $$
CREATE TRIGGER trg_contact_insert_profanity_check
BEFORE INSERT ON contacts.c
FOR EACH ROW
BEGIN
IF (c.email,c.message IN (SELECT * FROM profanity) OR (c.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";
ENDIF;
END;$$

ERROR

    MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as c
FOR EACH ROW
BEGIN
IF (c.email,c.message IN (SELECT * FROM profanity) OR' at line 2

Shouldn't there be `BEFORE INSERT ON contacts c` (space instead of dot)? — Pavel Třupek, Jul 26 '19 at 08:32
Why is there an alias anyway? I'm not sure that is supported at all after looking at some examples/documentation. — Jens Schauder, Jul 26 '19 at 08:34
Even after removing alias from `BEFORE INSERT ON contacts c` and `ÌF` conditions it is not working as shown below: `... BEFORE INSERT ON contacts FOR EACH ROW BEGIN IF (email,message IN (SELECT * FROM profanity) OR (message REGEXP '/(http|https|ftp|ftps)?(\:\/\/)?[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/' )) ...` MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '; END' at line 9 — Aakash Sahai, Jul 26 '19 at 09:20
Found the issue in my SQL, END IF is not having space. But whenever I am inserting a new row(even from phpmyadmin) it is throwing error. Email field does not exist. `#1054 - Unknown column 'email' in 'IN/ALL/ANY subquery'` — Aakash Sahai, Jul 26 '19 at 16:46

score 0 · Answer 1 · answered Jul 26 '19 at 19:16

Finally got the final and correct version of trigger after debugging lot of possibilities and digging SO answers. Was expecting a quick response as it was not that SCI-FI question that other developers were unable to crack.

Mistake 1: Space between ENDIF. It should be END IF.

Mistake 2: Missing new.column from trigger definition;

CORRECT VERSION:

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

MYSQL: IF having OR condition & REGEXP match

1 Answers1

Linked