CODEWITHSUNDEEP
mysqldatabase-triggermysql-regexp
Aakash Sahai
Aakash Sahai

Reputation: 4004

MYSQL: IF having OR condition & REGEXP match

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

Upvotes: 0

Views: 141

Answers (1)

Aakash Sahai
Aakash Sahai

Reputation: 4004

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

Upvotes: 0

Related Questions