Início / dba / Perguntas / 62213
Accepted
IT researcher
Asked: 2014-04-02 00:18:29 +0800 CST

Usando EncryptByPassPhrase na condição where

  • 772

Estou usando EncryptByPassPhrase do sql server. Mas eu não sou capaz de usar a condição where corretamente.

DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100))
insert into @login_details(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX'))
insert into @login_details(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY'))
select * from @login_details --this give all rows from table with encrypted values--

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details --this gives decrypted values

Mas na consulta abaixo estou usando a condição where, mas não funciona e resulta em 0 linhas. Por quê?

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where password=EncryptByPassPhrase('12','YYY')

onde a consulta abaixo funciona bem.

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password))='YYY'

Por que há um problema se eu incluir o comando encryptbyphrase na condição where?

sql-server-2008 encryption

1 respostas

  1. Best Answer

    Você precisa usar a condição que funciona. A razão é fácil de demonstrar. Rode isto:

    DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY');
SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x));
GO 5

    Você vê o mesmo resultado todas as vezes? Não. Isso ocorre porque a criptografia adiciona alguma mágica para evitar resultados previsíveis (isso é muito simplificado , mas para o propósito desta pergunta, é assim que o 3DES funciona para produzir esse sintoma).

    Portanto, a maneira de fazer isso é descriptografar primeiro e depois comparar.

    Obviamente, isso mata a sargability na coluna de senha (se estiver indexada), forçando uma verificação completa (se esse for realmente o único predicado). Se você precisar de buscas eficientes nesta coluna (parece improvável que você esteja apenas verificando se a senha corresponde), talvez seja necessário considerar uma coluna computada/persistente/indexada "bloqueada".

    Como um aparte, engraçado que uma pesquisa no Google encryptbypassphrase where clauseatingiu o número 1 como uma resposta do Stack Overflow por mim .

    • 2

relate perguntas