Safe alternative to exec(sql)

I've found some exec(sql) statements buried in the code. They're there for good reason, because these statements couldn't be written directly, however they are an obvious attack vector.

Is there a safe alternative to exec(some sql)? Something that will be paremetrised correctly, including table names in statements?

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...

StackExchange Database Administrators Q#152501, answer score: 9