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?

Improve this question
6
  • 2
    why do you not want to use the SQL profiler? it's usually the easiest way to do this Commented Feb 23, 2011 at 17:20
  • possible duplicate of How to get the generated SQL-Statment from a SqlCommand-Object? Commented Feb 23, 2011 at 17:23
  • 3
    ADO.NET does have built in tracing capabilities. Never used it myself though. Commented Feb 23, 2011 at 17:26
  • @Adam - I can't unfortunately, it's something that I need to do at application level :( Commented Feb 23, 2011 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 :( Commented Feb 23, 2011 at 18:06

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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 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.
That's very bad news =(... but many thanks for the answer anyway!
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.