January 25, 2013 at 2:25 pm
Just wondering, if I construct a SQL query in a Stored Proc, and pass record selection criteria into the "EXECUTE sp_executesql ..." as parameters of specified type and value, is there any way that can be exploited?
i.e. EXECUTE sp_executesql @SQLString, @ParmDefinition, @p1val, @p2val
I assume that @p1val and @p2val are effectively passed into sp_executesql like ByRef parameters, not ByVal? In other words, if @p1val contains something evil like 'name=2; drop table2;' (or whatever!) then it doesn't get expanded in the calling EXECUTE line, but is simply passed through to the SP call as one complete isolated entity regardless of its contents....?
Just want to be 100% sure 🙂
Thanks
January 25, 2013 at 2:43 pm
a648 (1/25/2013)
Just wondering, if I construct a SQL query in a Stored Proc, and pass record selection criteria into the "EXECUTE sp_executesql ..." as parameters of specified type and value, is there any way that can be exploited?i.e. EXECUTE sp_executesql @SQLString, @ParmDefinition, @p1val, @p2val
I assume that @p1val and @p2val are effectively passed into sp_executesql like ByRef parameters, not ByVal? In other words, if @p1val contains something evil like 'name=2; drop table2;' (or whatever!) then it doesn't get expanded in the calling EXECUTE line, but is simply passed through to the SP call as one complete isolated entity regardless of its contents....?
Just want to be 100% sure 🙂
Thanks
Yes it will prevent sql injection inside the parameter because it doesn't execute as a pass through.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2013 at 3:24 pm
Thanks. I can try it for myself on Monday when I'm back at the office, but it would have been bugging me all weekend 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply