Stored Procedure with dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure with dynamic SQL

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-02-11 : 10:16:59

Hello!

I just wrote a stored procedure with dynamic SQL. It works! But I am not sure if it is safe or if it is prone to SQL-injection or other heinous attacks.


alter proc sptest2 
@tableIn sysname
as
begin
declare @table nvarchar(20)
set @table = @tableIn
exec(N'select * from '+ @table)
end

Thank you very much and many greetings
landau

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-11 : 20:37:07

please do read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 23:27:17

its prone to injection attack. You should use sp_executesql instead.
See link Tan posted. It has pretty good explanation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources