像运算符一样在SQL Server中的加密列上进行搜索 [英] Like operator search on encrypted column in SQL server
问题描述
我在sqlserver中有一个表，我的表有一个字符串字段，例如FirstName(这只是示例！).此字段"EncryptByPassPhrase"在插入数据库之前.
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'.
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.
谢谢.
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%'
推荐答案
加密(通常是安全性)是对便利性的折衷.如果您想确保安全，则必须牺牲访问的便利性.如果要易于访问和快速访问，则必须牺牲安全性.作为开发人员，您的工作是在两者之间找到适当的平衡.对于您而言，很明显，服务器需要解密所有数据才能搜索所需的文本.如果您需要搜索加密的数据，那么您的设计就很糟糕.要么找到另一种保护数据的方法，要么放弃有效地搜索数据.
Encryption (security in general) is a compromise with convenience. If you want to be secure, you must sacrifice the easy of access. If you want to be easy accessible and fast, then you must sacrifice security. Your job as developer is to find the right balance between these. In your case, obviously the server need to decrypt all of your data to be able to search for the text you want. If you need to search in encrypted data, then your design is broken. Either find another way to protect your data, or give up searching it efficiently.
我会说，为什么要加密名称?数据库已经具有安全层来保护您的数据(透明数据加密，数据库用户权限等)，可以保护数据免遭未经授权的访问.您应该只加密真正敏感的信息，例如信用卡号，社会保险号及其等效物等.在每个字段(或名称等相对无害的字段)中使用加密来滥用加密将使您的应用程序非常不便，通常不会这样做.不值得付出努力.
I would say, why are you encrypting names? The database already has security layers to protect your data (Transparent Data Encryption, database users rights, etc.) which will protect the data from unauthorized access. You should encrypt only really sensitive information, such credit card numbers, social security numbers and its equivalents, etc. Abusing with encryption by using it for every single field (or relatively harmless fields like names) will make your application quite inconvenient and usually doesn't worth the efforts.
这篇关于像运算符一样在SQL Server中的加密列上进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
