是否有可能得到与SqlParameters一个SqlCommand的解析的文本？ [英] Is it possible to get the parsed text of a SqlCommand with SqlParameters?

问题描述

我所要做的是创建一个参数的一些任意的SQL命令，设置值和类型的参数，然后返回解析SQL命令 - 与参数包括在内。我不会直接运行针对SQL数据库此命令，所以没有连接应该是必要的。所以，如果我跑下面的示例程序，我希望看到以下文本（或类似的东西）：

What I am trying to do is create some arbitrary sql command with parameters, set the values and types of the parameters, and then return the parsed sql command - with parameters included. I will not be directly running this command against a sql database, so no connection should be necessary. So if I ran the example program below, I would hope to see the following text (or something similar):

WITH SomeTable (SomeColumn)
AS
(
    SELECT N':)'
    UNION ALL
    SELECT N'>:o'
    UNION ALL
    SELECT N'^_^'
)
SELECT SomeColumn FROM SomeTable

和示例程序是：

using System;
using System.Data;
using System.Data.SqlClient;

namespace DryEraseConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            const string COMMAND_TEXT = @"
WITH SomeTable (SomeColumn)
AS
(
    SELECT N':)'
    UNION ALL
    SELECT N'>:o'
    UNION ALL
    SELECT @Value
)
SELECT SomeColumn FROM SomeTable
";
            SqlCommand cmd = new SqlCommand(COMMAND_TEXT);
            cmd.CommandText = COMMAND_TEXT;
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Value",
                Size = 128,
                SqlDbType = SqlDbType.NVarChar,
                Value = "^_^"
            });
            Console.WriteLine(cmd.CommandText);
            Console.ReadKey();
        }
    }
}

这是东西是可以实现使用.NET标准库？初始搜索说没有，但我希望我是错的。

Is this something that is achievable using the .net standard libraries? Initial searching says no, but I hope I'm wrong.

推荐答案

您有参数化查询是如何工作的一个错误观念。在文本分析你说的是的从不的创建，参数值的从不的直接代入查询字符串。

You have a mistaken notion of how parameterized queries work. The "parsed text" you speak of is never created, and parameter values are never substituted directly into the query string.

这就是为什么它如此重要，使用参数化查询＆MDASH;你的完整的从查询code查询数据的隔离。数据是数据，code是code，两者永远不相遇。因此，有没有可能对SQL注入攻击。

That's why it's so important to use parameterized queries — you have complete segregation of query data from query code. Data is data, code is code, and never the twain shall meet. Thus, there is no possibility for sql injection.

它的意思是，如果你有这样的的CommandText：

What it means is that if you have a CommandText like this:

SELECT SomeColumn FROM SomeTable WHERE ID= @ID

而不是最终运行一个查询，看起来像这样：

instead of ultimately running a query that looks like this:

SELECT SomeColumn FROM SomeTable WHERE ID= 123

在实际运行更多的东西是这样的：

you actually run something more like this:

DECLARE @ID Int
Set @ID = RetrieveQueryDataItem("@ID")
SELECT SomeColumn FROM SomeTable WHERE ID= @ID

现在，这是不完全会发生什么;发动机不改变你code这样。相反，它使用sp_executesql过程。但是，这会帮助你理解发生了什么事情。

Now, this isn't exactly what happens; the engine doesn't transform you code like that. Instead, it uses the sp_executesql procedure. But this should help you understand what's going on.

这篇关于是否有可能得到与SqlParameters一个SqlCommand的解析的文本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！