Seeing the params in a SQL statement
- Thread starter VBAHole22
- Start date
Nate Bross
Well-known member
I dont understand the question, or I am missing something. I dont think there is a way to just write out the values of paramaters in an SQL query. If you are building the query from variables in your program, why wouldnt you just write out those variables after putting them in the SQL.
Yes that is one option.
If I have an insert query with say 5 parameters I can writeLine each of the params after I load them into the parameter.
My question is: Is there a way to WriteLine the SQL itself and see the values in the SQL statement? When you do WriteLine on a parameterized SQL statement you see something like:
INSERT into tbl (a,b,c) Values (@a,@b,@c)
Where the @ is the parameter. Regardless of what value you set it to you are always going to see the parameter listed as that.
But isnt the value of the parameter being swapped into that statement before it gets sent to sql? Why couldnt I see the SQL at that point? Then my 5 lines of WriteLine @a, WriteLine @b .... would be replaced by
WriteLine(command.RealCommandText)
If I have an insert query with say 5 parameters I can writeLine each of the params after I load them into the parameter.
My question is: Is there a way to WriteLine the SQL itself and see the values in the SQL statement? When you do WriteLine on a parameterized SQL statement you see something like:
INSERT into tbl (a,b,c) Values (@a,@b,@c)
Where the @ is the parameter. Regardless of what value you set it to you are always going to see the parameter listed as that.
But isnt the value of the parameter being swapped into that statement before it gets sent to sql? Why couldnt I see the SQL at that point? Then my 5 lines of WriteLine @a, WriteLine @b .... would be replaced by
WriteLine(command.RealCommandText)
PlausiblyDamp
Administrator
Parametrised queries dont work like that, they are not simple text substitutions. The underlying provider supports a mechanism that allows parameters to be passed separately from the actual SQL itself.
Last edited by a moderator:
Yeah. Thats what I was afraid of. But sounds about right. That whole parameterizing thing seems a bit mysterious to me. What is it doing and when?
So basically there is no way for me to know during runtime what values are actually getting to the db. Even if I write out the parameters as I set them, how do I know that those are the values that are reaching the database unless I turn around and query for them? Would the parameterization process ever make changes to my values in order to make them "more acceptable" to the database? I dont think it would but you never know what gets done under the sheets in the name of security.
Kinda stinks. I was hoping to avoid having to write the code everytime to see my values before they are executed. Helps a lot during debugging. This also detracts from the approach of using the parameters in the first place. And at a time when everyone, including MS, is pushing to use them to avoid injection attacks.
So basically there is no way for me to know during runtime what values are actually getting to the db. Even if I write out the parameters as I set them, how do I know that those are the values that are reaching the database unless I turn around and query for them? Would the parameterization process ever make changes to my values in order to make them "more acceptable" to the database? I dont think it would but you never know what gets done under the sheets in the name of security.
Kinda stinks. I was hoping to avoid having to write the code everytime to see my values before they are executed. Helps a lot during debugging. This also detracts from the approach of using the parameters in the first place. And at a time when everyone, including MS, is pushing to use them to avoid injection attacks.
PlausiblyDamp
Administrator
You could just create a standard routine that expects a command as a parameter and iterate over its parameters collection writing them to the debug window - at least it will only need to be done once then rather than every time you need to do this.
However if your procedure is properly written and tested it should work when you pass the values in anyway
However if your procedure is properly written and tested it should work when you pass the values in anyway
Nerseus
Danner
A customer routine is what we use at work - loops through a SqlParam collection and creates a custom set of name/value pairs. We use it for debugging and it works well enough.
The SQL Engine DOES eventually get the parameters passed as a human-readable string. If you use SQL Profiler you can see the "exec proc @param=value" type of call. If you dont like the custom code solution, you may look into intercepting that string and saving it somewhere. If the Profiler can see the string before its executed, theres a high probability that something exists for you to "hook" into that stream. Good luck!
-ner
The SQL Engine DOES eventually get the parameters passed as a human-readable string. If you use SQL Profiler you can see the "exec proc @param=value" type of call. If you dont like the custom code solution, you may look into intercepting that string and saving it somewhere. If the Profiler can see the string before its executed, theres a high probability that something exists for you to "hook" into that stream. Good luck!
-ner