加密和“WHERE encrypted_column LIKE” [英] Encryption and "WHERE encrypted_column LIKE"
问题描述
我正在为我的数据库启动一个加密项目，我正在执行一些关于解密速度的测试。我的许多应用程序查询在WHERE子句中使用了
LIKE参数。为了避免更改我的
应用程序，我正在后端执行所有工作;创建
视图，触发器和UDF来加密/解密数据。但问题是LIKE参数附近出现了
。
目前：
SELECT SSN，FNAME，LNAME来自USERS WHERE LNAME LIKE''BON％''
将成为：
SET @NEWVALUE = dbo.decrypt_hash（''BON％''）
选择SSN，FNAME，来自USERS_VIEW的LNAME LNAME_HASH LIKE
@NEWVALUE
这不起作用。哈希只能将字符串值与字符串
值进行比较。有没有其他人使用过这种类型的加密技术？你是如何使用LIKE来获得的？
谢谢，
Josh
I am starting an encryption project for my database and I''m performing
some tests on decryption speed. A lot of my application queries use a
LIKE parameter in the WHERE clause. To keep from changing my
application I am performing all the work on the back-end; creating
views, triggers and UDFs to encrypt/decrypt the data. A problem has
arisen around the LIKE parameter, though.
Currently:
SELECT SSN, FNAME, LNAME FROM USERS WHERE LNAME LIKE ''BON%''
will become:
SET @NEWVALUE = dbo.decrypt_hash(''BON%'')
SELECT SSN, FNAME, LNAME FROM USERS_VIEW WHERE LNAME_HASH LIKE
@NEWVALUE
This will not work. A hash can only compare a string value to a string
value. Has anyone else worked with this type of encryption and how did
you get around using LIKE?
Thanks,
Josh
推荐答案
使用TSQL在UDF中加密是一个非启动者。它总是会破坏性能，因为任何非平凡的加密算法很可能在TSQL中实现的速度慢得多。
首先，加密数据的目标是什么？了解加密
不是控制数据库访问的好方法。在数据库中有合法的使用
的加密，但加密用户的名字似乎有点不可靠。
异常。由于您的示例代码似乎甚至没有包含
解密函数的密钥，因此我不太明白您在这里实现了什么
。
如果你真的需要加密，那么Google会提供一些第三方
解决方案。您还可以在
microsoft.public.sqlserver。*层次结构中找到关于此主题的先前帖子。
-
David Portas
SQL Server MVP
-
Using TSQL to encrypt in a UDF is a non-starter. It''s always going to
destroy performance because any non-trivial encryption algorithm is likely
to be unfeasibly slow implemented in TSQL.
Firstly, what is the goal of encrypting the data? Understand that encryption
is not a good way to control access to a database. There are legitimate uses
of encryption in a database but encrypting user''s names seems a little
unusual. Since your example code doesn''t even seem to include a key for the
decryption function I don''t quite understand what you are trying to
implement here.
If you really need encryption then Google for some of the third-party
solutions available. You''ll also find previous posts on this topic in the
microsoft.public.sqlserver.* hierarchy.
--
David Portas
SQL Server MVP
--
David Portas（RE ****** **********************@acm.org）写道：
David Portas (RE****************************@acm.org) writes:
使用TSQL在UDF中加密是一个非启动者。它总是会破坏性能，因为任何非平凡的加密算法都可能在TSQL中实现得慢得多。
Using TSQL to encrypt in a UDF is a non-starter. It''s always going to
destroy performance because any non-trivial encryption algorithm is likely
to be unfeasibly slow implemented in TSQL.
您可以从UDF调用扩展存储过程来执行实际加密。当然，它仍然会很慢，因为UDF
和XP调用本身就很昂贵。然后，加密
和高性能并不能很好地结合在一起。
至于发布的问题，我建议所需要的是：
选择SSN，FNAME，LNAME来自USERS_VIEW
WHERE dbo.decrypt（LNAME_HASH）喜欢''BON％''
哪个表现不佳。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/ techinf ... 2000 / books.asp
" joshsackett" <乔********* @ gmail.com>在消息中写道
news：11 ********************** @ z14g2000cwz.googlegr oups.com ...
"joshsackett" <jo*********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
我正在为我的数据库启动一个加密项目，我正在执行一些关于解密速度的测试。我的很多应用程序查询都在WHERE子句中使用了
LIKE参数。为了避免更改我的应用程序，我正在执行后端的所有工作;创建视图，触发器和UDF来加密/解密数据。但是，围绕LIKE参数出现了一个问题。
我刚刚在这个月的SQL Server
杂志上读了一篇关于这篇的文章。
我同意加密姓氏有点不同。
他们建议的信用卡号码之类的东西是a）是
能够在除了ccn以外的列上进行索引，因此您可以在
问题中获取行，并且仅解密所需的绝对最小值，如果您需要
使用ccn，b）存储未加密的最后4位数字以帮助
缩小搜索范围。
目前：
选择SSN，FNAME，LNAME来自用户LNAME喜欢''BON％''
将成为：
SET @NEWVALUE = dbo.decrypt_hash（''BON％''）
从USERS_VIEW选择SSN，FNAME，LNAME LNAME_HASH LIKE
@NEWVALUE
这不起作用。哈希只能将字符串值与字符串
值进行比较。有没有其他人使用过这种类型的加密技术？你是如何使用LIKE的？
谢谢，
Josh
I am starting an encryption project for my database and I''m performing
some tests on decryption speed. A lot of my application queries use a
LIKE parameter in the WHERE clause. To keep from changing my
application I am performing all the work on the back-end; creating
views, triggers and UDFs to encrypt/decrypt the data. A problem has
arisen around the LIKE parameter, though.
I was just reading an article on this I think in this month''s SQL Server
magazine.
I''ll agree that encrypting last name is a bit "different".
One thing they suggested for things like credit card numbers is a) being
able to index on a column OTHER than the ccn so you can get the row(s) in
question and only decrypt that absolute minimum needed and if you DO need to
use the ccn, b) store the last 4 digits unencrypted to use that to help
narrow your search.
Currently:
SELECT SSN, FNAME, LNAME FROM USERS WHERE LNAME LIKE ''BON%''
will become:
SET @NEWVALUE = dbo.decrypt_hash(''BON%'')
SELECT SSN, FNAME, LNAME FROM USERS_VIEW WHERE LNAME_HASH LIKE
@NEWVALUE
This will not work. A hash can only compare a string value to a string
value. Has anyone else worked with this type of encryption and how did
you get around using LIKE?
Thanks,
Josh
这篇关于加密和“WHERE encrypted_column LIKE”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
