使用EF Core 2.2使用SQL Server DECRYPTBYKEY解密字符串 [英] Using EF Core 2.2 to decrypt a string using SQL Server DECRYPTBYKEY
问题描述
基本上，我有一个带有加密字符串的POCO模型.使用EF core 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时，这实际上会填充ok.
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中使用； 2)作为EF Expression树节点. 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似乎仍支持在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屋！
