/
is_email.sql
159 lines (151 loc) · 7.71 KB
/
is_email.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
create function public.is_email(email text)
returns boolean
parallel safe
language sql
set search_path = ''
immutable
returns null on null input
cost 10
as
$$
-- https://regex101.com/r/Q4dsL5/14
select regexp_match(email, $regexp$
^
#(?<![-!#$%&'*+/=?^_`{|}~@."\]\\a-zA-Zа-яА-ЯёЁ\d]) #граница начала email для захвата в тексте (здесь не используется)
(?:
[-!#$%&'*+/=?^_`{|}~a-zA-Z\d]+
| [-!#$%&'*+/=?^_`{|}~а-яА-ЯёЁ\d]+
| "(?:(?:[^"\\]|\\.)+)"
)
(?:
\.
(?:
[-!#$%&'*+/=?^_`{|}~a-zA-Z\d]+
| [-!#$%&'*+/=?^_`{|}~а-яА-ЯёЁ\d]+
| "(?:[^"\\]|\\.)+"
)
)*
@
(?:
(?:
(?: #домены 2-го и последующих уровней
(?!-)
(?:
(?:[a-zA-Z\d]|-(?!-)){1,63}
| (?:[а-яА-ЯёЁ\d]|-(?!-)){1,63}
)
(?<!-)
\.
)+
(?: #домен 1-го уровня
[a-zA-Z]{2,63}
| [а-яА-ЯёЁ]{2,63}
)
)
#(?![a-zA-Zа-яА-ЯёЁ\d@]) #граница окончания email для захвата в тексте (здесь не используется)
| (?: #IPv4
(?<!\d)
(?!0+\.)
(?:1?\d\d?|2(?:[0-4]\d|5[0-5]))(?:\.(?:1?\d\d?|2(?:[0-4]\d|5[0-5]))){3}
(?!\d)
)
| \[ #IPv4 в квадратных скобках
(?:
(?<!\d)
(?!0+\.)
(?:1?\d\d?|2(?:[0-4]\d|5[0-5]))(?:\.(?:1?\d\d?|2(?:[0-4]\d|5[0-5]))){3}
(?!\d)
)
\]
)
$
$regexp$, 'sx') is not null;
$$;
comment on function public.is_email(email text) is $$
Проверяет email по спецификации https://en.wikipedia.org/wiki/Email_address с небольшими отклонениями.
В email допускаются только английские и русские слова.
$$;
--TEST
--positive
do $$
begin
--Valid email addresses
assert public.is_email('ПишитеМне@ИвановИван.почта.рф');
assert public.is_email('Иванов.Иван@Санкт-Петербург.онлайн');
assert public.is_email('Ivanov.Иван@москва.ru');
--assert public.is_email('Ivanov.Иван+facebook@москва.ru'); --TODO !!! ???
assert public.is_email('[email protected]');
assert public.is_email('[email protected]');
assert public.is_email('[email protected]');
assert public.is_email('[email protected]');
assert public.is_email('[email protected]');
assert public.is_email('[email protected]'); --one-letter local part
assert public.is_email('"much.more unusual"@example.com');
assert public.is_email('"[email protected]"@example.com');
assert public.is_email('"very.(),:;<>[]\".VERY.\"very@\\ \"very\".unusual"@strange.example.com');
assert public.is_email('[email protected]');
assert public.is_email($email$#!$%&'*+-/=?^_`{}|[email protected]$email$);
assert public.is_email($email$"()<>[]:,;@\\\"!#$%&'*+-/=?^_`{}| ~.a"@example.org$email$);
assert public.is_email('" "@example.org'); --space between the quotes
assert public.is_email('[email protected]'); --see the List of Internet top-level domains
assert public.is_email('[email protected]');
end
$$;
--negative
do $$
begin
--Эти email валидные только среде разработки. В продуктивной среде такие email пользователи применять не могут и поэтому считаются невалидными:
assert not public.is_email('admin@mailserver1'); --local domain name with no TLD
assert not public.is_email('example@localhost'); --sent from localhost
assert not public.is_email('user@com');
assert not public.is_email('user@localserver');
assert not public.is_email('user@[IPv6:2001:db8::1]');
assert not public.is_email('©[email protected]');
end
$$;
--Valid Email address Reason
do $$
begin
assert public.is_email('[email protected]'); --Valid email
assert public.is_email('[email protected]'); --Email contains dot in the address field
assert public.is_email('[email protected]'); --Email contains dot with subdomain
assert public.is_email('[email protected]'); --Plus sign is considered valid character
assert public.is_email('[email protected]'); --Domain is valid IP address
assert public.is_email('email@[123.123.123.123]'); --Square bracket around IP address is considered valid
assert public.is_email('"email"@domain.com'); --Quotes around email is considered valid
assert public.is_email('[email protected]'); --Digits in address are valid
assert public.is_email('[email protected]'); --Dash in domain name is valid
assert public.is_email('[email protected]'); --Underscore in the address field is valid
assert public.is_email('[email protected]'); --.name is valid Top Level Domain name
assert public.is_email('[email protected]'); --Dot in Top Level Domain name also considered valid (use co.jp as example here)
assert public.is_email('[email protected]'); --Dash in address field is valid
end
$$;
--Invalid Email address Reason
do $$
begin
assert not public.is_email('plainaddress'); --Missing @ sign and domain
assert not public.is_email('#@%^%#$@#$@#.com'); --Garbage
assert not public.is_email('@domain.com'); --Missing username
assert not public.is_email('Joe Smith <[email protected]>'); --Encoded html within email is invalid
assert not public.is_email('email.domain.com'); --Missing @
assert not public.is_email('email@[email protected]'); --Two @ sign
assert not public.is_email('[email protected]'); --Leading dot in address is not allowed
assert not public.is_email('[email protected]'); --Trailing dot in address is not allowed
assert not public.is_email('[email protected]'); --Multiple dots
assert not public.is_email('あいうえお@domain.com'); --Unicode char as address
assert not public.is_email('email@domain'); --Missing top level domain (.com/.net/.org/etc)
assert not public.is_email('[email protected]'); --Leading dash in front of domain is invalid
--assert not public.is_email('[email protected]'); --.web is not a valid top level domain
assert not public.is_email('[email protected]'); --Invalid IP format
assert not public.is_email('[email protected]'); --Multiple dot in the domain portion is invalid
assert not public.is_email('Abc.example.com'); --(no @ character)
assert not public.is_email('A@b@[email protected]'); --(only one @ is allowed outside quotation marks)
assert not public.is_email('a"b(c)d,e:f;g<h>i[j\k][email protected]'); --(none of the special characters in this local part are allowed outside quotation marks)
assert not public.is_email('just"not"[email protected]'); --(quoted strings must be dot separated or the only element making up the local part)
assert not public.is_email('this is"not\[email protected]'); --(spaces, quotes, and backslashes may only exist when within quoted strings and preceded by a backslash)
assert not public.is_email('this\ still\"not\\[email protected]'); --(even if escaped (preceded by a backslash), spaces, quotes, and backslashes must still be contained by quotes)
assert not public.is_email('[email protected]'); --(double dot before @)
assert not public.is_email('[email protected]'); -- (double dot after @)
end
$$;