Visual Studio 编译时 SQL 验证

Question

如果我有一个像这样的Dapper.NET查询：

conn.Execute("insert into My_Table values ('blah', 'blah, 'blah', 'blah')");

我该如何强制Visual Studio针对特定数据库架构在编译时验证此查询？我知道有一些库可以进行查询验证（提供字符串和连接），但是哪种工具才是正确的选择？

是通过扩展Roslyn来检查我标记为查询字符串的字符串（语法类似于熟悉的@"Unescaped string"）？还是使用自定义预处理？

或者我问错了问题？将所有查询逻辑包装在我的数据库项目中的存储过程中（这样可以验证查询本身），是否更安全？现在我写下来，我认为我实际上会采用这个解决方案，但我仍然对上述内容感到好奇。我想能够编写：

 conn.Execute(#"insert into My_Table values ('blah',
 'blah, 'blah', 'blah')"); //Hashtag to mark as query

并让编译器根据给定的数据库模式验证字符串。

- user3527893

---

为什么不使用单元测试库来创建一些存储库测试呢？这将允许您传入受控数据并测试结果。 - Jeff Siver

我同意单元测试会使我的问题无效，但我仍然认为从编译时检查中可以获得一些好处（特别是因为它将是完全自动的，并且永远消除了代码中的一个主要错误来源，而不需要进一步的努力）。不幸的是，我是一个独立开发者。 - user3527893

最好的方法是首先避免向服务器发送即席查询。在SQL存储过程中完成所有操作（正如您最后建议的那样）是处理此问题的最佳方式。不仅可以验证SQL，而且还可以更好地保护SQL服务器免受攻击。 - DunningKrugerEffect

Dapper.NET支持参数化查询，但你提到的其他方面也很有道理。好的，我会做的。 - user3527893

存储过程也有显著的缺点，比如它们不会自动随代码版本更新。而且它能防御哪种攻击？ - svick

我不确定编译时检查会像你想的那样有帮助。首先，可能会有许多问题阻止参数化查询的完全验证。其次，与语法验证相比，查询验证可能会很慢。但是，这些都无法与通过执行代码进行验证相比。 - Jeff Siver

Answer 1

其中之一的选择是编写Roslyn分析器。分析器将查找所有对Execute()等函数的调用，如果它们的参数是常量字符串，则使用您提到的库进行验证。

在实现分析器时，您会遇到一个问题，即如何指定要验证的数据库模式。除非您想以某种方式在分析器中硬编码它，否则似乎使用"附加文件"（这目前需要手动编辑任何要使用分析器的项目的csproj）是解决方法。

分析器可能看起来像这样（请注意，您可能需要修改代码使其更加健壮）：

public override ImmutableArray<DiagnosticDescriptor> SupportedDiagnostics
    => ImmutableArray.Create(BadSqlRule, MissingOptionsFileRule);

public override void Initialize(AnalysisContext context)
{
    context.RegisterCompilationStartAction(AnalyzeCompilationStart);
    context.RegisterCompilationAction(AnalyzeCompilation);
    context.RegisterSyntaxNodeAction(AnalyzeNode, SyntaxKind.InvocationExpression);
}

private Config config = null;

private void AnalyzeCompilationStart(CompilationStartAnalysisContext context)
{
    var configFile = context.Options.AdditionalFiles
        .SingleOrDefault(f => Path.GetFileName(f.Path) == "myconfig.json");

    config = configFile == null
        ? null
        : new Config(configFile.GetText(context.CancellationToken).ToString());
}

private void AnalyzeCompilation(CompilationAnalysisContext context)
{
    if (config == null)
        context.ReportDiagnostic(Diagnostic.Create(MissingOptionsFileRule, Location.None));
}

private void AnalyzeNode(SyntaxNodeAnalysisContext context)
{
    if (config == null)
        return;

    var node = (InvocationExpressionSyntax) context.Node;

    var symbol = (IMethodSymbol) context.SemanticModel.GetSymbolInfo(
        node, context.CancellationToken).Symbol;

    // TODO: properly check it's one of the methods we analyze
    if (symbol.ToDisplayString().Contains(".Execute(string"))
    {
        var arguments = node.ArgumentList.Arguments;
        if (arguments.Count == 0)
            return;

        var firstArgument = arguments.First().Expression;

        if (!firstArgument.IsKind(SyntaxKind.StringLiteralExpression))
            return;

        var sqlString = (string)((LiteralExpressionSyntax) firstArgument).Token.Value;

        if (Verify(config, sqlString))
            return;

        context.ReportDiagnostic(
            Diagnostic.Create(BadSqlRule, firstArgument.GetLocation(), sqlString));
    }
}

- svick