在SQL Server中的加密字段中搜索 [英] Search in encrypted field in SQL server

问题描述

大家好！

我在sqlserver中有一个表，我的表有一个字符串作为字符串，例如FirstName（它只是示例！）。在数据库上插入之前，此字段EncryptByPassPhrase。

Hello everyone!
I have a table in sqlserver, my table has a field as string, for example FirstName (it's just example!). This field "EncryptByPassPhrase" before insert on database.

Before encode: Ali
After  encode: 0x010000001D905174BB7947AE1C600A4AB564A123310F92C21C9A4221

现在我会搜索这个字段，例如我会得到包含字符'Al'的所有字段。

我可以解码这个字段并搜索如下：

Now i would search on this field for example i would get all fields that contains charcters 'Al'.
I can decode this field and search on such as:

select * from EncodeTest e
where  dbo.Decoder(e.FirstName) like '%Al%'

但是在许多记录中这种方式非常慢。

请帮助我寻求最佳解决方案。



谢谢。



我尝试了什么：

But this way very slow on many numbers of record.
Please help me for best solution.

Thanks.

What I have tried:

declare @EncodedName varbinary(max)
select @EncodedName =EncryptByPassPhrase('key', cast( @Name as varbinary(max)) )


declare @DecodedName nvarchar(max)
select @DecodedName=convert(varchar(100),DecryptByPassPhrase('key',cast( @EncodedName as varbinary(max)) ))

select * from EncodeTest e
where dbo.Decoder(e.FirstName) like '%Al%'

推荐答案

提前计划。



D ecrypt一次;多次查询。

Plan ahead.

Decrypt once; query multiple times.

这篇关于在SQL Server中的加密字段中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！