Postgres 验证电子邮件地址的函数

Question

调用函数来验证电子邮件地址的检查约束对我来说效果不佳。

CREATE OR REPLACE FUNCTION f_IsValidEmail(text) returns BOOLEAN AS 
'select $1 ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
' LANGUAGE sql;



SELECT f_IsValidEmail('[email protected]');

该函数返回 false，这应该是 true。我尝试了其他几个正则表达式，但没有成功。谁能指出这个函数有什么问题吗？

屏幕截图

Answer 1

其中很多答案都接近正确的方式。这些就是我提交的要点。

• 您想要使用域名 - 而不是规则系统。
• 您不想使用正则表达式验证这些电子邮件地址。（2017 年 3 月更新：不再正确）

我在 DBA.StackExchange.com 上展示了两种正确执行此操作的方法。既检查 MX 记录，又使用 HTML5 规范。这是简短而甜蜜的。

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*
如需了解更多信息，我强烈建议您完整阅读答案。在答案中，我还展示了如何通过 Email::Valid 创建 DOMAIN，并解释为什么我不再使用该方法。
 );

SELECT '[email protected]'::email;
SELECT CAST('[email protected]' AS email);

如需了解更多信息，我强烈建议您完整阅读答案。在答案中，我还展示了如何通过 Email::Valid 创建 DOMAIN，并解释为什么我不再使用该方法。

Answer 2

在您投入大量精力之前，您要做的就是确保您没有删除有效的电子邮件地址。对于电子邮件地址中是否可以出现有各种疯狂的规则，如果您弄错了，方向错误，那么拥有完全有效的电子邮件地址的用户可能会被您的系统拒绝。

确定电子邮件地址是否有效的最佳方法是将其用作需要电子邮件的注册过程的一部分。任何其他事情都是劳而无功。

Answer 3

我推荐使用 PL/Perl 和 Email::Address 模块的解决方案。类似以下内容：

CREATE OR REPLACE FUNCTION email_valid(email text) RETURNS bool
LANGUAGE plperlu
AS $
use Email::Address;
my @addresses = Email::Address->parse($_[0]);
return scalar(@addresses) > 0 ? 1 : 0;
$;

另请参阅http://wiki.postgresql.org/wiki/Email_address_parsing。

Answer 4

如果您可以找出一个与电子邮件地址匹配的正则表达式，让您满意，请考虑它作为域而不是检查约束是否更有用。

对于一些陷阱，请参阅 regular-expressions.info。

Answer 5

不要尝试创建正则表达式来验证电子邮件！

众所周知，这是很难实现的。这是一个更好的解决方案：

假设您在数据库主机上安装了 Perl，请使用 CPAN 在同一主机上安装 Email::Valid 模块：

you@localhost$ cpan Email::Valid

然后确保您已安装 PL/Perl。在 psql 中连接到数据库并将 plperlu 添加为语言：（

CREATE EXTENSION plperlu;

请记住，这是一种不受信任的语言，因此您将向数据库提供直接文件访问权限，如果有人将恶意代码插入到数据库中，这可能会带来安全风险但是，您需要在下一步中执行此操作。）

将以下函数添加到数据库中：

CREATE FUNCTION validate_email() RETURNS trigger AS $
  use Email::Valid;
  return if Email::Valid->address($_TD->{new}{email});
  elog(ERROR, "invalid email address $_TD->{new}{email} inserted into $_TD->{table_name}(email)");
  return "SKIP";
$ LANGUAGE plperlu;

将触发器约束添加到列上的表中（假设您的表名为“users”）并且您的专栏名为“电子邮件”）：

CREATE TRIGGER users_valid_email_trig
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE validate_email();

您就完成了！

该解决方案使用 Email::Valid Perl 模块来处理验证，而验证又依赖于正则表达式来确保 RFC 822 合规性。然而，它是一个正则表达式的怪物，所以不要尝试提出你自己的正则表达式。

如果您对启用 plperlu 而不是普通 plperl 感到不舒服，您可以将相关函数移植到数据库中。

Answer 6

对我有用：

psql (9.0.2)
Type "help" for help.

postgres=> CREATE OR REPLACE FUNCTION "f_IsValidEmail"(text) returns BOOLEAN AS
postgres-> 'select $1 ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+
' as result
postgres'> ' LANGUAGE sql;
CREATE FUNCTION
postgres=> commit;
COMMIT
postgres=> SELECT "f_IsValidEmail"('[email protected]');
 f_IsValidEmail
----------------
 t
(1 row)

postgres=>