Wyzalacz do walicacji adresu IP

0

Napisałem wyzwalacz w MySQL odpowiadający za walidację po stronie MySQL adresu IP oraz sprawdzanie czy nie został zbanowany
Cel:
sprawdzenie aby dodawany adres IP był walidowany na poziomie bazy danych

  1. sprawdzenie czy adres IP jest już dodany do bazy danych
  2. sprawdzenie czy adres IP jest poprawnym adresem IP
    Walidacja dla IPv4

Wyzwalacz działał dla MS SQL obecnie przepisuję wszystko do MySQL i nie wiem jakie są różnice
Błąd:
Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.
Kod:

create table `banned_type`
(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`value` varchar(128),
	PRIMARY KEY (`id`)
);

insert into `banned_type`(`value`)
	values ('e-mail');
insert into `banned_type`(`value`)
	values ('domain');
insert into `banned_type`(`value`)
	values ('keyword');
insert into `banned_type`(`value`)
	values ('IPv4_address');
insert into `banned_type`(`value`)
	values ('IPv6_address');

create table `banned`
(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`value` nvarchar(320) not null,
	`type` int not null,
	`add_date` datetime not null DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`type`) REFERENCES `banned_type`(`id`)
);

insert into `banned`(`value`,`type`)
	values('10minut.xyz','2');
insert into `banned`(`value`,`type`)
	values('10minut.com.pl','2');
insert into `banned`(`value`,`type`)
	values('xoixa.com','2');
insert into `banned`(`value`,`type`)
	values('10minutmail.pl','2');
insert into `banned`(`value`,`type`)
	values('10mail.org','2');
insert into `banned`(`value`,`type`)
	values('10minutemail.net','2');
insert into `banned`(`value`,`type`)
	values('10minutemail.com','2');
insert into `banned`(`value`,`type`)
	values('dropmail.me','2');
insert into `banned`(`value`,`type`)
	values('minuteinbox.com','2');
insert into `banned`(`value`,`type`)
	values('20minutemail.com','2');

DELIMITER $$
create trigger `banned_value_validation`
AFTER INSERT  on `banned`
FOR EACH ROW
begin
	declare IPv4 varchar(15);
    if EXISTS(select * from `inserted` where `inserted`.`type`=4)
    then
		set IPv4=CAST(
		(
			select `inserted`.`banned_value`
			from `inserted`
			where `inserted`.`type`=4
		)as char(15));
        #sprawdzenie czy dany adres już nie jest zbanowany
        if EXISTS (
				select *
				from 
				`inserted` 
				where `inserted`.`value` in 
				(
					select `value`
					from `banned`
					where `banned`.`id` not in 
					(
						select `inserted`.`id`
						from `inserted`
					)
				)
			)
            then
				#select "Invalid IPv4 address (IPv4 was banned)";
				ROLLBACK;
		end if;
		
        if IPv4 like '%_.%_.%_.%_'
			and IPv4 not like '%.%.%.%.%'
			and IPv4 not like '%`^0-9.`%'
			and IPv4 not like '%`0-9``0-9``0-9``0-9`%'
			and IPv4 not like '%`3-9``0-9``0-9`%'
			and IPv4 not like '%2`6-9``0-9`%'
			and IPv4 not like '%25`6-9`%'
		Then
			set IPv4 = IPv4;
			#select "CORRECT IPv4 address";
		else
			#select "Invalid IPv4 address (IPv4 format error)";
			ROLLBACK;
		end if;
    end if;
END$$
0

jak nie rozumiesz komunikatu po angielsku Explicit or implicit commit is not allowed in stored function or trigger. to wklejasz do tłumacza i masz Jawne lub niejawne zatwierdzenie nie jest dozwolone w przechowywanej funkcji lub wyzwalaczu. A jak dalej nie rozumiesz (chociaż wersję pl ciężej zrozumieć) to ja nie mam pojęcia jak to napisać "bardziej zrozumiale"

0

Dziękuję bardzo, hejt i niemiłe komentarze zawsze mile widziane

Dlaczego w takim razie to nie działa i co należy zrobić aby zaczęło działać?

0

no ale czego nie rozumiesz?? MYSQL NIE POZWALA NA ZATWIERDZANIE TRANSAKCJI W TRIGGERACH LUB PROCEDURACH Koniec, kropka. To są dwie RÓŻNE bazy i nie da się przenieś tego jeden do jeden

0

Nie ma tutaj tabeli inserted (jest new) oczywiście dziękuję za podpowiedź, bardzo mi pomogła :)
W jaki sposób podczas walidacji mogę zrobić ROLLBACK jeżeli okaże się, że adres IP istnieje lub jest nie poprawny?
Czy w prawidłowy sposób odwołuję się do tabeli NEW?
Zamiast pisać kilka komentarze wytknij błędy i powiedz co zrobiłem nie tak i jak mogę to poprawić
Hejt i niemiłej komentarze nadal mile widziane

DELIMITER $$
create trigger `banned_value_validation`
AFTER INSERT  on `banned`
FOR EACH ROW
begin
	declare IPv4 varchar(15);
    if EXISTS(select * from `new` where `new`.`type`=4)
    then
		set IPv4=CAST(
		(
			select `new`.`value`
			from `new`
			where `new`.`type`=4
		)as char(15));
        #sprawdzenie czy dany adres już nie jest zbanowany
        if EXISTS (
				select *
				from `new` 
				where `new`.`value` in 
				(
					select `value`
					from `banned`
					where `banned`.`id` not in 
					(
						select `new`.`id`
						from `new`
					)
				)
			)
            then
				#select "Invalid IPv4 address (IPv4 was banned)";
                SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 was banned)";
				#ROLLBACK;
		end if;
		
        if IPv4 like '%_.%_.%_.%_'
			and IPv4 not like '%.%.%.%.%'
			and IPv4 not like '%`^0-9.`%'
			and IPv4 not like '%`0-9``0-9``0-9``0-9`%'
			and IPv4 not like '%`3-9``0-9``0-9`%'
			and IPv4 not like '%2`6-9``0-9`%'
			and IPv4 not like '%25`6-9`%'
		Then
			set IPv4 = IPv4;
			#select "CORRECT IPv4 address";
		else
			SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 format error)";
			#select "Invalid IPv4 address (IPv4 format error)";
			#START TRANSACTION ;
			#ROLLBACK;
            #COMMIT;
		end if;
    end if;
END$$


insert into `banned`(`value`,`type`)
values('192.168.0.1','4');
1
  1. jeśli chcesz nie dopuścić do dodania rekordu to wyzwalacz powinien być BEFORE - przy AFTER jest już "po ptokach" (MSSQL nie ma BEFORE)
  2. źle - nie robisz select na new lub old - po prostu się do nich odwołujesz https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
  3. żeby przerwać np. dodawanie rekordu możesz rzucić wyjątkiem SIGNAL sqlstate ... https://dev.mysql.com/doc/refman/8.0/en/signal.html i obsłużyć po stronie aplikacji
0

Dziękuję za pomoc, napisałem wyzwalacz
Pojawił się problem z walidacją adresu IP
Korzystałem wcześniej (do MSSQL) z tego: http://loudelement.com/isvalidip_function_mssql.k
Podjąłem próbę automatycznej konwersji kodu: http://www.sqlines.com/online

Korzystając z like:
https://www.w3schools.com/sql/sql_like.asp
https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like

Jak napisać walidację aby nie dochodziło do przekroczenia wartości 255?

DELIMITER $$
create trigger `banned_value_validation`
BEFORE INSERT  on `banned`
FOR EACH ROW
begin

	declare IPv4 varchar(15);
    if (NEW.type=4)
    then
		set IPv4=CAST(NEW.`value` as char(15));
		#if IPv4 REGEXP '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'
        #then
        IF IPv4 like '%.%.%.%'
   			and IPv4 not like '%.%.%.%.%'
   			and IPv4 not like '%`^0-9.`%'
   			and IPv4 not like '%`0-9``0-9``0-9``0-9`%'
   			and IPv4 not like '%`3-9``0-9``0-9`%'
   			and IPv4 not like '%2`6-9``0-9`%'
   			and IPv4 not like '%25`6-9`%'
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 was banned)";
			end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 format error)";
		end if;
	end if;
END$$

Testy:

insert into `banned`(`value`,`type`)
values('192.168.0.1','4');
insert into `banned`(`value`,`type`)
values('192.5348561','4');
insert into `banned`(`value`,`type`)
values('127.0.0.1','4');
insert into `banned`(`value`,`type`)
values('127.734.1.673','4');
1

masz gotowe funkcje https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet-aton sprawdzić czy zwraca coś innego niż null albo dedykowana https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_is-ipv4 analogiczne są dla IP v6

0

Poprawiłem wyzwalacz
Funkcja IS_IPV4 sprawdzi czy jest to adres IPv4 i jeżeli będzie puste pole zwróci błąd
Czy mógłby kolega abrakadaber który bardzo mi pomógł podczas pisania "shakować" moje rozwiązanie generując test który sprawi że mój wyzwalacz nie zadziała?

DELIMITER $$
create trigger `banned_value_validation`
BEFORE INSERT  on `banned`
FOR EACH ROW
begin
	declare IPv4 varchar(15);
    declare IPv6 varchar(45);
    if (NEW.type=4)
    then
		set IPv4=CAST(NEW.`value` as char(15));
		#if IPv4 REGEXP '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'
        #then
        IF IS_IPV4(IPv4)=True
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 was banned)";
			end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 format error)";
		end if;
	end if;
	
    if (NEW.type=5)
    then
		set IPv6=CAST(NEW.`value` as char(45));
		#if IPv4 REGEXP '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'
        #then
        IF IS_IPV6(IPv4)=True
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid IPv6 address (IPv6 was banned)";
			end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid IPv6 address (IPv6 format error)";
		end if;
	end if;
END$$
0

MAsz złe założenia triggera.
Wstawiłbym zbanowane IP, albo niepoprawne z type=3, a następnie zrobiłbym update typu :)

0

MAsz złe założenia triggera. - dlaczego?
Wstawiłbym zbanowane IP, albo niepoprawne z type=3, a następnie zrobiłbym update typu :)
Czy bez tego wyzwalacz działa?
Chciałem zrobić odpowiednik switch-case i zwracać kody błędów aby było wiadomo o co chodzi

0
Marcin Marcin napisał(a):

Czy mógłby kolega abrakadaber który bardzo mi pomógł podczas pisania "shakować" moje rozwiązanie generując test który sprawi że mój wyzwalacz nie zadziała?

wystarczy wstawić rekord z type != 4 lub 5 :P
tu if EXISTS ( select id, value, type from banned where banned.value = NEW.value) zamiast pobierać pola możesz zrobić if EXISTS ( select 1 from banned where banned.value = NEW.value)

0

Jest to celowe ponieważ jak mam adres e-mail chcę sprawdzać także czy istnieje zbanowana domena dla tego adresu e-mail
Nie pokazałem całego kodu

DELIMITER $$

DROP FUNCTION IF EXISTS IS_EMAIL $$
CREATE FUNCTION IS_EMAIL(email varchar(320)) RETURNS bool
BEGIN
	IF email is null
    THEN
		RETURN FALSE;
    END IF;
    IF email NOT REGEXP '^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9_\-]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\.[a-zA-Z]{2,4}$'
    THEN
		RETURN FALSE;
	ELSE
		RETURN TRUE;
	END IF;
END;$$

DELIMITER $$

DROP FUNCTION IF EXISTS GET_DOMAIN_FROM_EMAIL $$
CREATE FUNCTION GET_DOMAIN_FROM_EMAIL(email varchar(320)) RETURNS varchar(320)
BEGIN
	IF IS_EMAIL(email)=True
    THEN
		RETURN SUBSTRING(email,POSITION("@" IN email)+1,LENGTH(email));
	ELSE
		SIGNAL sqlstate '45001' set message_text = "Invalid e-mail address (e-mail format error)";
	END IF;
END;$$

DELIMITER $$
create trigger `banned_value_validation`
BEFORE INSERT  on `banned`
FOR EACH ROW
begin
	declare MAIL varchar(320);
    declare DOMAIN varchar(320);
    declare KEYWORD varchar(320);
    declare IPv4 varchar(15);
    declare IPv6 varchar(45);
	if (NEW.type=1)
    then
		set MAIL=CAST(NEW.`value` as char(320));
        IF IS_EMAIL(MAIL)=True
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid e-mail address (e-mail was already banned)";
			end if;
            IF EXISTS (select `id`,`value`,`type` from `banned` where `banned`.`value`=2 and `banned`.`value`=GET_DOMAIN_FROM_EMAIL(MAIL))
            THEN
				SIGNAL sqlstate '45001' set message_text = "Invalid e-mail address (Domain from e-mail was already banned)";
            end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid e-mail address (e-mail format error)";
		end if;
	end if;
    if (NEW.type=2)
    then
		if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
		then
			SIGNAL sqlstate '45001' set message_text = "Invalid domain. (Domain was already banned)";
		end if;
	end if;
    	if (NEW.type=3)
    then
		set KEYWORD=CAST(NEW.`value` as char(320));
		if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
		then
			SIGNAL sqlstate '45001' set message_text = "Invalid keyword. (Keyword was already banned)";
		end if;
	end if;
    if (NEW.type=4)
    then
		set IPv4=CAST(NEW.`value` as char(15));
        IF IS_IPV4(IPv4)=True
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 was banned)";
			end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid IPv4 address (IPv4 format error)";
		end if;
	end if;
	
    if (NEW.type=5)
    then
		set IPv6=CAST(NEW.`value` as char(45));
        IF IS_IPV6(IPv4)=True
 		THEN
			if EXISTS ( select `id`,`value`,`type` from `banned` where `banned`.`value` = NEW.`value`)
			then
				SIGNAL sqlstate '45001' set message_text = "Invalid IPv6 address (IPv6 was banned)";
			end if;
        ELSE
 			SIGNAL sqlstate '45001' set message_text = "Invalid IPv6 address (IPv6 format error)";
		end if;
	end if;

END$$

Zarejestruj się i dołącz do największej społeczności programistów w Polsce.

Otrzymaj wsparcie, dziel się wiedzą i rozwijaj swoje umiejętności z najlepszymi.

Utwórz konto