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
– Pavel Třupek, Commented Jul 26, 2019 at 8:32
Why is there an alias anyway? I'm not sure that is supported at all after looking at some examples/documentation. — Jens Schauder
– Jens Schauder, Commented Jul 26, 2019 at 8: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
– Aakash Sahai, Commented Jul 26, 2019 at 9: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
– Aakash Sahai, Commented Jul 26, 2019 at 16:46

Aakash Sahai · Accepted Answer · 2019-07-26 19:16:51Z

0

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

answered Jul 26, 2019 at 19:16

Aakash Sahai

4,0027 gold badges29 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

MYSQL: IF having OR condition & REGEXP match

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related