How to secure dynamic SQL stored procedure?

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

---

See here:

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

---

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)