SQLCommand\parameters - viewing prepared statement

  • Thread starter Thread starter Jeff Jarrell
  • Start date Start date
J

Jeff Jarrell

In the old days before sql injection attacks our apps would just build
strings and execute them. If the query threw an exception the evaluated
query string would be displayed\logged. From the log the analyst could
simply cut and paste into query analyzer and see what was wrong and
ultimately work towards fixing it.

Now we are re-writing and coding with parameters i'd like to be able to see
the same thing. For instance, the exception that says "incorrect syntax
near =" and you have no idea near what. In the old days I would simply add
the query to the message and you could readily see in the exception what is
wrong. I.e. an apostrophe in the user data (which hadn't been escaped for).

In my first foray into this I went into SQL Profiler and pulled out the
statement. I need the evaluated statement in the log. (I guess I could
build it myself, but then that wouldn't be exactly what sql sees now would
it?)

Any ideas?
 
Jeff - with paramaters, you aren't going to get many of those same errors
;-) but that's not your point.

Personally, we use Log4Net for isntance, and in our BusinessLayer, we have
logging statements that will spell out each parameter's name and it's
corresponding value (we actually often use different levels of logging so at
the most verbose, we log all the values when the method is first called, and
then the values after all the validation right before the call to the dalc
class).

However another thing worht noting is that SqlException is a weee bit
different than other exceptions. You see, you might have 50 errors in your
SqlException and if you just check SqlExceptionName.ToString() then you may
miss out on a lot of info. You can loop through the Errors colleciton and
gather that info ie
foreach(SqlError er in MySqlException.Errors){

}

Between these two techniques you can get about all the information you'd
ever want and more. You can also trap the InfoMessage event which will
throw back Print Statements as well as Sql Error msgs with a Severity level
of under 15 (Actually, it may be 11 or 13 - I forget the exact number off of
the top of my head) but you can use this as well to fine tune your error
logging.

HTH,

Bill
 
Jeff,

Nobody prefents you that, if you have an error, to first hard code your SQL
string in your program.

Than you can go back to find the error and slowly build it to SP.

That is the way I do these things when I have errors.

Cor
 
actually, besides errors in taking i am talking about syntax coding errors
in the sql statement. I always find it easier getting the sql working in
query analyer then moving it into code (as necessary, i do use SPs also).
And back into query analyzer.

sounds like I am going to roll my own diagnostic that dumps that parms,
possibly into the statement similar that .sql would see.

thanks.
jeff
 
Cor,

I work on a vertical market app. moving the data to my shop isn't a common
practice, and interactively
debugging code on the client is is a no-no. That is why it was also so
beneficial to see the sql statement with the placeholders evaluated.

As I said in the other msg, looks like I have to roll my own here.

Thanks,
jeff
 
Back
Top