2

Is it somehow possible to intercept the query that a given SqlCommand is going to execute on the database?

I'd like to track for debugging purposes all queries that my Data class invokes, and can't find a clever way to do this.

I tried to use some weird "replace" of the sql command string, or appending the parameters with a funky

sb.AppendLine("@" + p.ParameterName + " = " + p.ToDebugString());

(with "ToDebugString()" being an extension method that does a "ToString()" with or without single quotes depending if it's a string or not)

But that seems kinda unprofessional, and it fails horribly when it encounters an

SqlDbType.Structured

parameter.

More or less, I'd like to intercept the DB call inside the application in the same way the SqlServer Profiler does inside the DB itself.

Thank you in advance.

BIG EDIT:

I know that given a simple query like this:

SELECT * FROM MyTable WHERE ID=@ID

Rather than running it like this:

SELECT * FROM MyTable WHERE ID=1234

The database actually runs the procedure like this:

DECLARE @ID int
SET @ID = 1234
SELECT * FROM MyTable WHERE ID=@ID

Can I intercept at application level this last block?

sh0uzama
  • 582
  • 1
  • 6
  • 17
  • 2
    why do you not want to use the SQL profiler? it's usually the easiest way to do this – Adam Ralph Feb 23 '11 at 17:20
  • possible duplicate of [How to get the generated SQL-Statment from a SqlCommand-Object?](http://stackoverflow.com/questions/265192/how-to-get-the-generated-sql-statment-from-a-sqlcommand-object) – Abe Miessler Feb 23 '11 at 17:23
  • 3
    [ADO.NET does have built in tracing capabilities](http://msdn.microsoft.com/en-us/library/ms971550.aspx). Never used it myself though. – Martin Smith Feb 23 '11 at 17:26
  • @Adam - I can't unfortunately, it's something that I need to do at application level :( – sh0uzama Feb 23 '11 at 18:05
  • @Martin - didn't know about that! Again however, it does not solve the issue, since it can't be done at application level :( – sh0uzama Feb 23 '11 at 18:06
  • out of interest, why do you need to do it at the application level? what's preventing you from doing this using the SQL profiler? – Adam Ralph Feb 23 '11 at 21:10

1 Answers1

5

It sounds like you're wanting to see the parameters substituted directly in the query string "as it's done on the server". This is not possible, because the server never substitutes the parameters into the string. That's the beauty of parameterized queries: data is data, code is code, and never that twain shall meet.

Given a simple query like this:

SELECT * FROM MyTable WHERE ID=@ID

Rather than running it like this:

SELECT * FROM MyTable WHERE ID=1234

You can think of it as if the database actually runs a procedure like this:

DECLARE @ID int
SET @ID = 1234
SELECT * FROM MyTable WHERE ID=@ID
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • I know that. The question still remains: how do I get the "database version" of the querystring? In your example, how can I intercept the last statement [DECLARE ... WHERE ID=@ID]? - edited the original question with your sample – sh0uzama Feb 23 '11 at 18:00
  • 1
    @sh0uzama my example is a simplification. It doesn't run the declare query. It uses sq_executesql() and passes the parameters as binary data that is often not printable. – Joel Coehoorn Feb 23 '11 at 19:12
  • That's very bad news =(... but many thanks for the answer anyway! – sh0uzama Feb 25 '11 at 08:54
  • 1
    Coming back to this late, but I see it as very good news =) It means sql injection is completely impossible with parameterized queries, because inputs are quarantined from the command text in a way that's not true when it's merely escaped or sanitized. It means MS did it the "Right way"™, instead of merely trying to sanitize or escape things, and thus doesn't have the [problems you sometimes see with php db-independent prepared statements or MySql parameterized queries.](http://stackoverflow.com/a/12202218/3043) – Joel Coehoorn Oct 11 '16 at 21:19