Safe alternative to exec(sql)
For certain types of parameterization, it's not going to matter if you use EXEC()
or sp_executesql
, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need to expressed literally to SQL Server (it can't tokenize that and swap at runtime).
To protect yourself from table name vulnerabilities, you can easily protect yourself like this:
DECLARE @tablename sysname; -- pretend this is a parameter
SET @tablename = N'this_is_not_a_table';
-- in procedure body:
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
RAISERROR(N'Table does not exist.', 1, 11);
RETURN;
END
SET @sql = N'SELECT ... FROM dbo.' + QUOTENAME(@tablename) + ...;
-- EXEC(@sql) or EXEC sys.sp_executesql @sql
Now, I prefer to use sp_executesql
always, partly because it promotes using strongly-typed parameters (avoiding SQL injection issues as well as double-single-quote issues), but also because in some cases you will be passing parameters for both parameterizable and unparameterizable values (yes I made those words up). More info:
- Bad Habits to Kick : Using EXEC() instead of sp_executesql
- Protecting Yourself from SQL Injection - Part 1
- Protecting Yourself from SQL Injection - Part 2
Now the only thing you have to worry about is if someone is able to create tables and can create a table named sys.objects; DROP TABLE foo; --
- but if you have someone you don't trust but has the ability to create tables in your database...
Take a look at using sp_executeSQL instead. It is fully paramaterized and so provides a much greater sense of safety to dynamic queries (and using alongside EXECUTE AS USER within procedures with strict permission sets make it even better).