Como validar e-mails no SQL Server

Boa Tarde Pessoal,

Em muitas ocasiões observei diversas dúvidas relacionadas a validação de e-mails no SQL Server. Na maioria das vezes essa necessidade está relacionada ao cadastro em tabelas, mas eventualmente aparece como parâmetros de uma SP, functions, etc. A validação de e-mails é algo muito comum e quase sempre a mesma é feita via aplicação. Não há nada de errado em validá-la na aplicação, mas colocar essa validação no banco pode consistir em uma validação extra e sem dúvida contribuir para a produção de dados de qualidade. Normalmente associa-se esse tipo de validação a check constraints e rules. A partir do 2005 existe também o CLR que pode ser utilizado para tal. Serão apresentadas as validações com base em check constraints em TSQL e com o uso do CLR. Para isso, o script abaixo é necessário:

CHECK CONSTRAINTs clássicas para validação de e-mails

A validação de dados através de check constraints é costumeira para pequenas validações. Permitir que o saldo de um depósito bancário seja maior que zero, que a coluna sexo tenha somente os valores M e N ou ainda que a data de envio de um pedido deve ser superior a data de quando ele foi feito são exemplos clássicos de check constraints. A validação de um e-mail também se enquadra nesse tipo de necessidade, mas não é muito fácil de codificá-la com o check constraint. Essa dificuldade inclusive faz com que essa validação acabe não sendo feita ou feita de maneira pouca eficaz.

— Cria uma tabela
CREATE TABLE tbl (Email VARCHAR(100))

— Adiciona uma constraint para validação de e-mail
ALTER TABLE tbl ADD CONSTRAINT ckEmail
CHECK (Email LIKE ‘%@%’
    AND Email LIKE ‘%.%’
    AND Email NOT LIKE ‘%.@%’
    AND Email NOT LIKE ‘%@.%’)

— Tenta cadastra alguns e-mails inválidos
INSERT INTO tbl VALUES (‘em@.com’)
INSERT INTO tbl VALUES (‘g.@com’)
INSERT INTO tbl VALUES (‘em@il’)

— Cadastra alguns e-mails "inválidos"
INSERT INTO tbl VALUES (‘em@il..com’)
INSERT INTO tbl VALUES (‘@il.’)
INSERT INTO tbl VALUES (‘.m@’)

As três primeiras inserções são inválidas, pois, possuem o arroba ao lado de um ponto ou ainda a ausência de um desses elementos. Isso no entanto, não impede que outros e-mails potencialmente inválidos sejam cadastrados. A dificuldade em elaborar essa validação consiste fundamentalmente em elaborar as regras e não definí-las. Há outras regras que não foram consideradas como o tamanho mínimo do nome do domínio, a obrigatoriedade do arroba vir antes do ponto ou nenhum desses caractéres iniciar o e-mail. Procurando um pouco na NET, encontrei no site vyaskn.tripod.com que mostra uma implementação de validação de e-mails via check constraint. Segue uma adaptação:

— Retira a Constraint Anterior
ALTER TABLE tbl DROP CONSTRAINT ckEmail

— Deleta os registros
DELETE FROM tbl

— Adiciona uma nova constraint para validação de e-mail
ALTER TABLE tbl ADD CONSTRAINT ckEmail
CHECK (

    — Não é possível ter espaços
    CHARINDEX(‘ ‘,LTRIM(RTRIM([Email]))) = 0

    — O arroba não pode ser o primeiro caractér
    AND LEFT(LTRIM([Email]),1) <> ‘@’

    — O ponto não pode ser o primeiro caractér
    AND RIGHT(RTRIM([Email]),1) <> ‘.’

    — Deve haver um ponto após o arroba
    AND CHARINDEX(‘.’,[Email],CHARINDEX(‘@’,[Email])) – CHARINDEX(‘@’,[Email]) > 1

    — Apenas um arroba por e-mail é permitido
    AND LEN(LTRIM(RTRIM([Email]))) – LEN(REPLACE(LTRIM(RTRIM([Email])),‘@’,)) = 1

    — O nome do domínio deve finalizar com dois caractéres
    AND CHARINDEX(‘.’,REVERSE(LTRIM(RTRIM([Email])))) >= 3

    — Não é possível haver padrões como .@, e .. (o @. já foi garantido)
    AND (CHARINDEX(‘.@’,[Email]) = 0 AND CHARINDEX(‘..’,[Email]) = 0)
)

— Tenta cadastra alguns e-mails inválidos
INSERT INTO tbl VALUES (‘em@.com’)
INSERT INTO tbl VALUES (‘g.@com’)
INSERT INTO tbl VALUES (‘em@il’)
INSERT INTO tbl VALUES (‘em@il..com’)
INSERT INTO tbl VALUES (‘@il.’)
INSERT INTO tbl VALUES (‘.m@’)
INSERT INTO tbl VALUES (‘em@il.b’)

Após a mudança e aumento de validações, a check constraint apresentada foi capaz de rejeitar todos os e-mails inválidos que pensei.

O uso do CLR para validação de e-mails

Um recurso muito utilizado para validar e-mails nas linguagens de programação é o uso de expressões regulares. Alguns SGBDs como o Oracle, o DB2 e o MySQL possuem implementações de expressões regulares no seu próprio dialeto SQL. O TSQL possui algum suporte a expressões regulares no uso de check constraints e do operador LIKE, mas ainda não há um objeto ou função para utilização de expressões regulares de forma nativa. Com o advento do CLR é possível superar essa limitação. O código a seguir cria uma função para testar uma string em relação a uma expressão regular de validação de e-mail.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class Validacoes
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fnValidaEmail(SqlString Email)
    {
        // Cria um objeto de expressões regulares para validar e-mail
        Regex re = new Regex(@"^(([^<>()[\]\\.,;:\s@\""]+"
      + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@"
      + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
      + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+"
      + @"[a-zA-Z]{2,}))$");

        // Realiza um teste na validação da expressão
        return new SqlBoolean(re.IsMatch(Email.ToString()));
    }
};

Para que o código possa ser utilizado é necessário compilá-lo. Para não haver dependência do Visual Studio, o procedimento para compilar esse código envolve os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como fValidarEmail.cs em algum diretório (ex: G:\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /out:G:\CLR\fValidarEmail.dll /target:library G:\CLR\fValidarEmail.cs (supondo que o arquivo esteja em G:\CLR\)
  • O próximo passo é registrar o ASSEMBLY para que o código possa ser utilizado e posteriormente a criação do novo tipo de dados

O registro do Assembly permite criar uma nova function com base no método fnValidaEmail e testá-la. Se o retorno for igual a 1 então o e-mail é válido, se for igual a zero o e-mail é inválido.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY ValidacaoEmail FROM ‘G:\CLR\fValidarEmail.dll’

— Cria uma função em CLR para validar E-Mail
CREATE FUNCTION dbo.fnValidarEmail (@Email NVARCHAR(100))
RETURNS BIT
AS EXTERNAL NAME [ValidacaoEmail].Validacoes.fnValidaEmail

— Testa alguns e-mails
SELECT dbo.fnValidarEmail(‘e@mail.valido.br’) As EmailValido
SELECT dbo.fnValidarEmail(‘e@mail_invalido’) As EmailInvalido

Se a função é capaz de validar e-mails, o passo natural é utilizá-la em uma check constraint (dessa vez baseada em CLR e não em T-SQL)

— Retira a Constraint Anterior
ALTER TABLE tbl DROP CONSTRAINT ckEmail

— Adiciona uma nova constraint para validação de e-mail
ALTER TABLE tbl ADD CONSTRAINT ckEmail
CHECK (dbo.fnValidarEmail (Email) = 1)

— Tenta cadastra alguns e-mails inválidos
INSERT INTO tbl VALUES (‘em@.com’)
INSERT INTO tbl VALUES (‘g.@com’)
INSERT INTO tbl VALUES (‘em@il’)
INSERT INTO tbl VALUES (‘em@il..com’)
INSERT INTO tbl VALUES (‘@il.’)
INSERT INTO tbl VALUES (‘.m@’)
INSERT INTO tbl VALUES (‘em@il.b’)

E qual é o melhor ? TSQL ou CLR ?

Se há duas alternativas para se fazer a mesma coisa é natural se perguntar qual delas é a melhor. Não há uma resposta padrão para escolher sempre entre o TSQL ou o CLR (até porque se houvesse apenas um dos dois estaria sendo utilizado). Normalmente o TSQL é recomendado para operações que envolvam muita recuperação, manipulação e gravação de dados enquanto o CLR é ótimo para operações que envolvam cálculos complexos. No caso do e-mail sou adepto do uso do CLR por duas razões: ele possui um suporte bem melhor para esse tipo de validação e se utilizado dentro de uma function pode ser reaproveitado para outras check constraints, para instruções SELECT, etc. Não seria possível obter esses benefícios apenas com o TSQL.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s