1

Stemmed from this question: How does SQLParameter prevent SQL Injection?

Is there any way that I can see what query is getting passed to my database and executed when I execute a parametrized query?

I don't have SQL Profiler available to me so that is not an option. I was hoping there might be a way to do it from visual studio. Possible?

Community
  • 1
  • 1
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486

2 Answers2

4

Well, it's really no magic or no black art - a query like this in ADO.NET:

string sqlStmt = "SELECT * FROM dbo.Customers WHERE country = @country";

using(SqlConnection _conn = new SqlConnection("server=.;database=Northwind;integrated security=SSPI;"))
using(SqlCommand _cmd = new SqlCommand(sqlStmt, _conn))
{
    _cmd.Parameters.Add("@country", SqlDbType.VarChar, 100).Value = "Switzerland";

    DataTable results = new DataTable();

    using(SqlDataAdapter dap = new SqlDataAdapter(_cmd))
    {
        dap.Fill(results);
    }
}

will be translated into this on SQL Server:

exec sp_executesql N'SELECT * FROM dbo.Customers WHERE country = @country',N'@country varchar(100)',@country='Switzerland'

Basically, ADO.NET / SQL Server do not replace the parameters in the SQL statement string like many folks believe - it is actually passed to SQL Server as a parametrized query, along with a list of parameters and their values.

This SQL statement was taken from SQL Profiler - I don't know how else you could see that query...

Why can't you use SQL Profiler?? I mean - it's in every copy of SQL Server, there's even a free SQL Express Profiler for those using the free SQL Server Express editions.....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

I don't believe you can access that sort of thing within Visual Studio since the translation would be happening internally inside ADO.NET libraries, but the answer marc_s posted above is correct. You could verify this using SQL Server Management Studio by looking at the incoming transactions (if you had it available).

Cortright
  • 1,164
  • 6
  • 19
  • Actually - not even ADO.NET does any translation - it's sent to SQL Server for execution as a parametrized query - no replacement of parameters with their values is happening! (a common misconception...) – marc_s Jul 07 '11 at 05:06