>>>I'm reading this article (thanks Naomi) for building dynamic queries from variable user input. The user might provide one search field or several:
>>>http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/do-you-use-column-param-or-param-is-null
>>>
>>>In the article the author uses a built in stored procedure for executing dynamic sql select statements called sp_executesql
>>>
>>>This stored procedure has a weird way of calling it with parameters which I assumed was a way of calling dynamic sql using parameterized variables.
>>>
>>>However, on the MS page for this (http://msdn.microsoft.com/en-us/library/ms188001.aspx) the first warning is that "Run time-compiled Transact-SQL statements can expose applications to malicious attacks, such as SQL injection."
>>>
>>>Can anyone shed any light here?
>>>
>>>My goal is to create a stored procedure that dynamically creates a query based on variable user input, but does so in a parameterized way to avoid any sql injection vulnerabilities.
>>
>>Naomi's statement is open to interpretation, unfortunately. Only if you concatenate the values that should have been parameters into a string and execute it, do you leave yourself open to SQL Injection.
>>
>>Most people stop at the concatenation part and figure that means SQL Injection is possible, but parameters never become part of the executable command and so are immune from injection. Any time the user's values are concatenated instead of parameterized, you run a risk.
>>
>>The author of those blogs could be clearer too.
>>
>>"there is a place for dynamic SQL and if you use it correctly you will also get plan reuse." I'd change that a little and say "dynamic parameterized SQL" - just to reinforce the parameters issue.
>
>IMHO, I stated clearly that embedding parameters into dynamic sql as a string opens doors for injection attacks. Using them as parameters prevents the attacks. I don't see what was not clear in what I stated.

m.lcParameter = "something to be injected"

Here I add the parameter to the string:
lcSQL = "select * from table where field = lcParameter"

vs

Here I add the parameter value to the string:
lcSQL = "select * from table where field = ' " + m.lcParameter + "'"

The difference is obvious.