Best way to debug parameterized query in c#

Question

Escape ( ' ） symbol in Textbox for asp.net c#

Based on the question in post above, most people suggested that "parameterized query" is the best solution to avoid the sql injection.

Below is my code by using the sql injection

public DataSet checkemp(string user)
{
    strsql = "SELECT * from employee where employeeid = @userid";
    SqlConnection con = new SqlConnection(connectionString);
    SqlDataAdapter da = new SqlDataAdapter(strsql, connectionString);
    da.SelectCommand.Parameters.Add("@userid", SqlDbType.VarChar, 50).Value = user;    
    // pretend the user name is "Micheal"
    con.Open();
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    con.Dispose();
    return ds;
}

During the debugging, I can only get the query "SELECT * from employee where employeeid = @userid" if I point on "strsql" label, but not "SELECT * from employee where employeeid = 'Micheal'.

Any solution suggested to solve this question and make it most efficiency? thanks everyone!

Simple answer is no, it is not possible to view the command as `"SELECT * from employee where employeeid = 'Micheal'.` on holding over `strsql`. — sujith karivelil, Jun 02 '16 at 04:33
You can also user profiler to view the actual sql statement executed on database — Yogi, Jun 02 '16 at 04:36
if you wanna test the query, why don't you do it in the SQL server? — woodykiddy, Jun 02 '16 at 04:36
so that mean during debugging, I need to manual insert parameter value into query to repalce the @userid and run on sql server? — 120196, Jun 02 '16 at 04:36
Yogi, how to use user profiler to view the actual sql statement? — 120196, Jun 02 '16 at 04:37
woodykiddy, because I can only get "SELECT * from employee where employeeid = @userid" but not the 'Micheal', if that consists of 20+ parameters, i need to replace it 1 by 1 manually — 120196, Jun 02 '16 at 04:39
@120196 - Find details on profiler here - http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step — Yogi, Jun 02 '16 at 04:40
you should be able to loop through all the paramters in `da.SelectCommand.Parameters` using a foreach loop. Also you could try to create a list of params first then add them `List paramList`, the then loop through `paramList` and add them to the command — Alex W, Jun 02 '16 at 05:08
The whole *point* of using parameterized queries is that your SQL *code* is kept separate from the *data* that you're operating on - meaning that e.g. your data doesn't all have to be converted into strings just to force the database server to have to convert them back into the appropriate types, and thus no formatting/escaping issues, nor SQL injection issues (where the data is mistaken for code) — Damien_The_Unbeliever, Jun 02 '16 at 06:30

score 2 · Answer 1 · answered Jun 02 '16 at 05:15

I would introduce an extension method (although this is not must, actual logic is more important) that returns the parsed query as below, and call that only during debug mode:

public void TestMethod()
{
    string cmdStr = "<some sql command text>";
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(cmdStr, con);
    cmd.Parameters.AddWithValue("<param1>", <value1>); // add parameter in any way you want
#if DEBUG
    string parsedQuery = cmd.GetParsedQuery();
    Console.WriteLine(parsedQuery); // or whatever
#endif  
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    con.Open();
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    con.Dispose();
    return ds;
}

public static string GetParsedQuery(this SqlCommand cmd)
{
    if(cmd.CommandType == CommandType.Text)
    {
        string parsedQuery = cmd.CommandText;
        foreach(var p in cmd.Parameters)
        {
            parsedQuery = parsedQuery.Replace(p.ParameterName, Convert.ToString(p.Value));
        }

        return parsedQuery;
    }

    return null;
}

Note that, although I have directly written extension method (for brevity), it should really be defined in a separate static class.

score 0 · Answer 2 · answered Jun 02 '16 at 05:00

Try MiniProfiler :

"An ADO.NET profiler, capable of profiling calls on raw ADO.NET"

http://miniprofiler.com/

public DataSet checkemp(string user)
{
    strsql = "SELECT * from employee where employeeid = @userid";
    SqlConnection con = GetOpenConnection(connectionString);
    SqlDataAdapter da = new SqlDataAdapter(strsql, connectionString);
    da.SelectCommand.Parameters.Add("@userid", SqlDbType.VarChar, 50).Value = user;    
    // pretend the user name is "Micheal"
    con.Open();
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    con.Dispose();
    return ds;
}

public static DbConnection GetOpenConnection(string connectionString)
{
    var cnn = new SqlConnection(connectionString);
    // wrap the connection with a profiling connection that tracks timings 
    return new StackExchange.Profiling.Data.ProfiledDbConnection(cnn, MiniProfiler.Current);
}

** You might need to wrap SqlCommand with ProfiledDbCommand

Best way to debug parameterized query in c#

2 Answers2