LINQ to SQL的填充对象之前解密值 [英] LINQ to SQL decrypt value before populating object

问题描述

我的其中一个表格中有使用

There is a field in one of my tables that is encrypted using

ENCRYPTBYPASSPHRASE(<passphrase>,<value>)

当值放入对象时，字段仍然加密，任何东西。我不能创建一个视图或存储过程或任何其他项目，解密数据库上的字段，因为它违背了加密字段的目的。是否有框架工作运行的方法

When the value is placed into the object the field is still encrypted so I can't do anything with it. I can't create a view or stored proc or any other item that decrypts the fields on the database because then it defeats the purpose of encrypting the fields. Is there a way of having the frame work run something like

DECRYPTBYPASSPHRASE(<passphrase>, <columnName>)

？

现在我获取数据，然后调用ExecuteQuery解密值。并在我的数据模型类上的加密值上分配该新值。它工作，但我只是想知道是否可以通过一些我不知道的选项自动完成。我尝试搜索，但没有找到任何东西。

Right now I'm Getting the data then calling ExecuteQuery to decrypt the value. and assigning that new value over the encrypted value on my data model class. It works but I was just wondering if it could be done automatically through some options I don't know about. I've tried searching but have not found anything.

推荐答案

这个小提琴，下面的SQL非常有效，

As demonstrated by this fiddle, the following SQL is perfectly effective,

SELECT
            E.[Key],
            CAST(
                      DecryptByPassPhrase(
                          'test',
                          E.[Encrypted])
                AS
                    varchar(8000)) [Clear],
            E.[Other]
    FROM
            [Example] E;

因此请使用这个重载的ExecuteQuery 做一些类似的事情，

So use this overload of ExecuteQuery to do something like,

var examples = context.ExecuteQuery<Example>(
    @"SELECT
                E.[Key],
                CAST(
                          DecryptByPassPhrase(
                              @p0,
                              E.[Encrypted])
                    AS
                        varchar(8000)) [Clear],
                E.[Other]
        FROM
                [Example] E;",
    passPhrase);

可在一次通话中检索和解密您的数据。

to retrieve and decrypt your data in one call.

这篇关于LINQ to SQL的填充对象之前解密值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！