Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
214 views
in Technique[技术] by (71.8m points)

c# - Is it possible to get the parsed text of a SqlCommand with SqlParameters?

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

And the sample program is:

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();
        }
    }
}

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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.

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...