使用 EF Core 2.2 使用 SQL Server DECRYPTBYKEY 解密字符串 [英] Using EF Core 2.2 to decrypt a string using SQL Server DECRYPTBYKEY
问题描述
基本上我有一个带有加密字符串的 POCO 模型.使用 EF 核心 2.2.
Basically I have a POCO model that has a encrypted string. Using EF core 2.2.
我们使用 DECRYPTBYKEY 使用 SYMMETRIC KEY 解密字符串.
We use DECRYPTBYKEY to decrypt strings using SYMMETRIC KEY.
我正在使用 DBSet.FromSQL 传入调用开放对称密钥的 SQL 查询，获取包括解密值、关闭对称密钥在内的数据.
I am using DBSet.FromSQL to pass in SQL query which calls open symmetric key, get the data including the decrypted value, close symmetric key.
FromSQL 只允许您带回实体，而不是单独带回字符串.
FromSQL only allows you to bring back an entity rather than a string by itself.
我尝试在模型上添加解密的字符串值，然后尝试在 FromSQL 查询中设置该值.
I have tried adding an decrypted string value on the model and have tried to then set that in FromSQL query.
当存储库 DBSet 中没有任何 .Include 时，这实际上可以填充.
This actually populates ok when the repository DBSet does not have any .Include in it.
当 DBSet 确实有 .Include(用于过滤外键表上的 DBSet)时，会出现运行时错误，它会抱怨解密的字符串不是数据库表上的列 - 当然它不是.因此，拥有 .Include 是首先在基表上调用 SQL.
When the DBSet does have .Include (to filter the DBSet on a foreign key table) there's a runtime error which complains about the decrypted string not being a column on the database table - which of course it isn't. So having .Include is calling the SQL on base table first.
如果我将 [NotMapped] 属性放在解密的字符串列上，那么当 FromSQL 查询运行时，它不会填充它.
If I put the [NotMapped] attribute on the decrypted string column then when the FromSQL query runs it doesn't populate it.
那么如何在不使用 [NotMapped] 而是在 DBSet 上使用 .Include 的情况下使用这个解密的字符串列?
So how can I use this decrypted string column without using [NotMapped] but with using .Include on the DBSet?
我已经添加了代码，以便您可以更多地看到问题.无法按照一个答案中的建议在模型上添加 Decrypt 的实现.Decrypt 方法需要 DbSet 调用 FromSQL.DbSet 源自 ConcreteRepository.我也无法看到调用即席 SQL 查询来返回 1 个字符串.
I've added code so you can see the problem more. There's no way to add an implementation of Decrypt on the Model as suggested in one answer. Decrypt method requires the DbSet to call FromSQL. The DbSet originates comes from the ConcreteRepository. There's also no way that I can see to call an adhoc SQL Query to return 1 string.
从原始 SQL (SQL Server) 中截取
OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';
SELECT * , --other fields
CONVERT(VARCHAR(60), DECRYPTBYKEY(A.Encrypted)) AS Decrypted
FROM dbo.Model A
JOIN table2 t2 ON ...
JOIN table3 t3 ON ...
WHERE A.Id= 123
CLOSE SYMMETRIC KEY {1};",
具体存储库
public async Task<IEnumerable<Model>> GetAllById(int id)
{
var filteredSet = Set.Where(x => x.Id == id)
.Include(x => x.Table2)
.Where(x => x.Table2.IsSomething).ToList();
var models = filteredSet.Select(f =>
GetDecryptValue($"Id = {f.Id}");
return models;
}
基础存储库
protected DbSet<TEntity> Set => _dbContext.Set<TEntity>();
public virtual TEntity GetDecryptValue(string filterCriteria)
{
string buildSelectStmt = $"SELECT TOP 1 Encrypted FROM Model";
string buildSelectStmt2 = $"SELECT *, CONVERT(VARCHAR(MAX), DECRYPTBYKEY(@Value)) AS Decrypted FROM Model";
buildSelectStmt = $"{buildSelectStmt} WHERE {filterCriteria}";
buildSelectStmt2 = $"{buildSelectStmt2} WHERE {filterCriteria}";
string sql = string.Format(@"
DECLARE @Value NVARCHAR(MAX)
SET @Value = ({0});
OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';
{3};
CLOSE SYMMETRIC KEY {1};",
buildSelectStmt, SymmetricKeyName, SymmetricKeyPassword, buildSelectStmt2);
var result = Set.FromSql(sql);
return result.FirstOrDefault();
}
模型
public partial class Model
{
public int Id { get; set; }
public string Encrypted { get; set; }
[NotMapped]
public string Decrypted { get; set; }
}
推荐答案
所以正如我在评论中暗示的那样，确实可以侵入 EFCore 的管道并使其执行自定义 SQL 函数.这是一个功能控制台应用，可以做到这一点.
So as I hinted in the comment, it is indeed possible to hack into EFCore's pipeline and make it do custom SQL functions. Here's a functional console app that does it.
我会预先声明，因为我没有密钥，所以我使用 DECRYPTBYPASSPHRASE 函数对数据库(请参阅我的存储库链接中的 SQL 脚本)进行了试验.我也只安装了 .net core 2.1.尽管如此，我还是希望你能明白要点.话虽如此，我将强调几点，让您进一步探索解决方案:
I will state upfront, I experimented on a database (see SQL script within my repo link) with DECRYPTBYPASSPHRASE function as I didn't have a key. I also only have .net core 2.1 installed. None the less, I am hoping you'd get the gist anyway. With that said, I'll highlight a few points and let you explore the solution further:
我最终像这样定义了我的模型:
I ended up defining my Model like so:
public partial class Model
{
public int Id { get; set; }
public byte[] Encrypted { get; set; } // apparently encrypted data is stored in `VARBINARY`, which translates to `byte[]`, so I had to tweak it here
[NotMapped] // this is still required as EF will not know where to get the data unless we tell it (see down below)
public string Decrypted { get; set; } // the whole goal of this exercise here
public Table2 Table2 { get; set; }
}
鉴于我应该能够只选择值而不必进行第二次往返，我稍微修改了您的 Concrete Repository 代码:
Given I should be able to just select the value without having to make a second roundtrip, I slightly modified your Concrete Repository code:
public IEnumerable<Model> GetAllById(int id)
{
// you will need to uncomment the following line to work with your key
//_dbContext.Database.ExecuteSqlCommand("OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';", SymmetricKeyName, SymmetricKeyPassword);
var filteredSet = Set.Include(x => x.Table2)
.Where(x => x.Id == id)
.Where(x => x.Table2.IsSomething)
.Select(m => new Model
{
Id = m.Id,
//Decrypted = EF.Functions.DecryptByKey(m.Encrypted), // since the key's opened for session scope - just relying on it should do the trick
Decrypted = EF.Functions.Decrypt("test", m.Encrypted),
Table2 = m.Table2,
Encrypted = m.Encrypted
}).ToList();
// you will need to uncomment the following line to work with your key
//_dbContext.Database.ExecuteSqlCommand("CLOSE SYMMETRIC KEY {1};", SymmetricKeyName);
return filteredSet;
}
现在，定义 EF.Functions.Decrypt 是这里的关键.我们基本上必须做两次:1) 作为扩展方法，所以我们可以在 LINQ 中使用 then 和 2) 作为 EF 表达式树节点.然后 EF 会做什么，对于它发现的每个方法调用，它检查 IMethodCallTranslator 的内部列表，如果它发现匹配 - 它将函数推迟到 SQL.否则，它将必须在 C# 中运行.因此，您将看到的所有管道基本上都需要将 TranslateImpl 注入该列表.
now, defining EF.Functions.Decrypt is the key here. We basically have to do it twice: 1) as extension methods so we can use then in LINQ and 2) as EF Expression tree nodes. What EF then does, for each method call it discovers, it checks internal list of IMethodCallTranslator and if it discovers a match - it defers the function to SQL. Otherwise it will have to be run in C#. So all the plumbing you will see is basically needed to inject TranslateImpl into that list.
public class TranslateImpl : IMethodCallTranslator
{
private static readonly MethodInfo _encryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Encrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(string) });
private static readonly MethodInfo _decryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Decrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(byte[]) });
private static readonly MethodInfo _decryptByKeyMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.DecryptByKey),
new[] { typeof(DbFunctions), typeof(byte[]) });
public Expression Translate(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Method == _encryptMethod)
{
var password = methodCallExpression.Arguments[1];
var value = methodCallExpression.Arguments[2];
return new EncryptExpression(password, value);
}
if (methodCallExpression.Method == _decryptMethod)
{
var password = methodCallExpression.Arguments[1];
var value = methodCallExpression.Arguments[2];
return new DecryptExpression(password, value);
}
if (methodCallExpression.Method == _decryptByKeyMethod)
{
var value = methodCallExpression.Arguments[1];
return new DecryptByKeyExpression(value);
}
return null;
}
}
我最终实现了三个表达式存根:DecryptByKey、DecryptByPassphrase 和 EncryptByPassphrase，例如:
I ended up implementing three expression stubs: DecryptByKey, DecryptByPassphrase and EncryptByPassphrase, for example:
public class DecryptByKeyExpression : Expression
{
private readonly Expression _value;
public override ExpressionType NodeType => ExpressionType.Extension;
public override Type Type => typeof(string);
public override bool CanReduce => false;
protected override Expression VisitChildren(ExpressionVisitor visitor)
{
var visitedValue = visitor.Visit(_value);
if (ReferenceEquals(_value, visitedValue))
{
return this;
}
return new DecryptByKeyExpression(visitedValue);
}
protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is IQuerySqlGenerator))
{
return base.Accept(visitor);
}
visitor.Visit(new SqlFragmentExpression("CONVERT(VARCHAR(MAX), DECRYPTBYKEY("));
visitor.Visit(_value);
visitor.Visit(new SqlFragmentExpression("))"));
return this;
}
public DecryptByKeyExpression(Expression value)
{
_value = value;
}
}
毕竟是非常微不足道的弦乐练习.希望这能为您提供足够的燃料来启动和运行您的解决方案.
pretty trivial string building exercise after all. Hopefully this gives you enough fuel to get your solution up and running.
UPD EF Core 3 似乎仍然支持 IMethodCallTranslator，因此上述解决方案仍然适用.UPD2:确实可以.请参阅我的 github 上的更新存储库.
UPD EF Core 3 seems to still support the IMethodCallTranslator, therefore solution above should still apply. UPD2: Indeed, can be done. See my updated repo on github.
这篇关于使用 EF Core 2.2 使用 SQL Server DECRYPTBYKEY 解密字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
