flawopen.com/SQL Injection/Stored procedures
Are stored procedures immune to SQL injection?
No — a common misconception. A stored procedure protects you only if it uses parameter binding internally. One that builds a query by concatenating its own input parameters into a string and executing it with EXEC or sp_executesql is exactly as vulnerable as application-level string concatenation.
-- SQL Server T-SQL; dbo.Users.Id is NVARCHAR(50).
CREATE PROCEDURE dbo.GetUser
@UserId NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) =
N'SELECT Id FROM dbo.Users WHERE Id = N'''
+ @UserId + N'''';
EXEC(@sql);
END;
-- SQL Server T-SQL; same illustrative schema.
CREATE PROCEDURE dbo.GetUser
@UserId NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) =
N'SELECT Id FROM dbo.Users WHERE Id = @id';
EXEC sys.sp_executesql
@stmt = @sql,
@params = N'@id NVARCHAR(50)',
@id = @UserId;
END;
Why this misconception is common
"Use stored procedures" is often given as a blanket SQL-injection fix. It is effective when the procedure passes values as bound parameters in a normal query. A procedure that constructs dynamic SQL with EXEC and concatenates untrusted values recreates the same injection risk. When dynamic SQL is necessary, bind values with sp_executesql. Identifiers such as table or column names cannot be bound as values; map allowed choices to fixed identifiers with an allowlist.
FAQ
Yes. sp_executesql supports parameters: define them in its parameter declaration and pass the values separately (for example, EXEC sp_executesql @sql, N'@id INT', @id = @UserId). Do not concatenate values into @sql. For dynamic table or column names, use a strict allowlist because identifiers are not bind parameters.