How to secure dynamic SQL stored procedure?

Question

I have a stored procedure that takes in the name of a table as a parameter and uses dynamic sql to perform the select. I tried to pass @TableName as a parameter and use sp_executesql but that threw an error. I decided to go with straight dynamic sql without using sp_executesql.

Is there anything else I should be doing to secure the @TableName parameter to avoid sql injection attacks?

Stored procedure below:

CREATE PROCEDURE dbo.SP_GetRecords  
    (   
    @TableName VARCHAR(128) = NULL
    )   
AS
BEGIN   

    /* Secure the @TableName Parameter */          
    SET @TableName = REPLACE(@TableName, ' ','')    
    SET @TableName = REPLACE(@TableName, ';','')    
    SET @TableName = REPLACE(@TableName, '''','')

    DECLARE @query NVARCHAR(MAX)    

    /* Validation */    
    IF @TableName IS NULL
    BEGIN       
        RETURN -1
    END 

    SET @query = 'SELECT * FROM ' + @TableName
    EXEC(@query)        
END

This failed when using sp_executesql instead:

SET @query = 'SELECT * FROM @TableName' 
EXEC sp_executesql @query, N'@TableName VARCHAR(128)', @TableName

ERROR: Must declare the table variable "@TableName".

Whichever solution you choose, be aware that when you execute dynamic SQL within a stored procedure, that SQL runs by default with the permissions of the stored proc *caller*. Whereas normal SQL in a stored procedure runs by default with the permissions of the stored proc *owner*. — HTTP 410, Jan 12 '11 at 18:44

score 3 · Accepted Answer · edited May 23 '17 at 12:26

3

See here:

How should I pass a table name into a stored proc?

edited May 23 '17 at 12:26

Community

1
1

answered Jan 12 '11 at 17:36

RBarryYoung

55,398
14
96
137

Jason Benson · Answer 2 · 2011-01-12T17:42:17.547

2

you of course can look at the sysobjects table and ensure that it exists

Select id from sysobjects where xType = 'U' and [name] = @TableName

Further (more complete example):

DECLARE @TableName nVarChar(255)
DECLARE @Query nVarChar(512)

SET @TableName = 'YourTable'
SET @Query = 'Select * from ' + @TableName

-- Check if @TableName is valid
IF NOT (Select id from sysobjects where xType = 'U' and [name] = @TableName) IS NULL
     exec(@Query)

edited Jan 12 '11 at 17:42

answered Jan 12 '11 at 17:32

Jason Benson

3,371
1
19
21

1

Wouldn't the unsafe string still be included in the query to verify the tables existence? – jason saldo Jan 12 '11 at 17:35
4

@jms but here you can use bind parameters – Markus Winand Jan 12 '11 at 17:36

How to secure dynamic SQL stored procedure?

2 Answers2