A perfect example of why stored procedures don't automatically protect you from
SQL Injection[
^]!
Use
sp_executesql[
^] to execute your query, passing the parameters as parameters. You also need to validate that the table name is a valid table in the current database.
CREATE PROC insertdata
(
@tableName nvarchar(50),
@name nvarchar(50),
@Nrange nvarchar(50),
@isActive bit
)
As
BEGIN
DECLARE @sql nvarchar(max);
DECLARE @RealTableName sysname;
SET NOCOUNT ON;
SELECT
@RealTableName = QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM
sys.tables As T
INNER JOIN sys.schemas As S
ON S.schema_id = T.schema_id
WHERE
T.name = @tableName
;
If @@ROWCOUNT = 0 RAISERROR('Invalid table: "%s"', 16, 1, @tableName);
SET @sql = N'INSERT INTO ' + @RealTableName + N' VALUES (@name, @Nrange, @isActive)';
EXEC sp_executesql @sql,
N'@name nvarchar(50), @Nrange nvarchar(50), @isActive bit',
@name = @name,
@Nrange = @Nrange,
@isActive = @isActive
;
END
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[
^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[
^]
Query Parameterization Cheat Sheet | OWASP[
^]