Prevent SQL injection in queries with exec or execute

[pasStiga]

I'm developing a reporting web with Visual Studio 2017. All my SQL queries are parameterized and works properly. The problem is in some queries that have exec command like in a procedure.

For example: instead of select from tabla where id=@id I put exec('select from tabla where id=@id')

The exec case doesn't work. The message error is:

Must declare the scalar variable ""@id""." & vbCrLf & "Must declare the scalar variable ""@id""."

Anyone knows a possible solution?

Thanks a lot!

[ejohn20]

Concatenating data inside stored procedures and executing with EXEC, EXECUTE, OR sp_executesql allows SQL injection as well (very similar to inside a C# class). This can be secured by calling sp_executesql with parameters in the 2nd and 3rd arguments.

This is vulnerable:

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM Users 
WHERE UserName LIKE ''%' + @search + '%'''

EXECUTE sp_executesql @sql

This is safe because @search is passed in as a parameter in the 2nd and 3rd arguments of the sp_executesql function:

DECLARE @sql NVARCHAR(MAX)
SET @search = '%' + @search + '%'

SET @sql = 'SELECT * FROM Users 
WHERE UserName LIKE @userName'

EXECUTE sp_executesql @sql, 
N'@userName VARCHAR(500)', 
@userName = @search

Issue #15 is an open enhancement for locating this in the additional file analyzers.