Making a SQL Server Stored Procedure Safe from SQL Injections

Question

This can be easily injected here because the @ID param can be practically anything in this SQL statement by inputting it, however, how do you prevent this exploit?

I prefer to specifically prevent this exploit at this level rather than application level, any suggestions?

CREATE PROCEDURE [dbo].[GetDataByID]
@ID bigint,
@Table varchar(150)
AS
BEGIN

Declare @SQL Varchar(1000)

SELECT @SQL = 'SELECT * FROM ' + @Table + ' WHERE ID = ' + CONVERT(varchar,@ID)

SET NOCOUNT ON;

EXEC(@sql)  
END

Answer 1

Check this page, it has a wonderful guide to dynamic sql, and the options to execute them safely

In your case it should be like this:

SELECT @SQL =  N'SELECT * FROM ' + quotename(@Table) + N' WHERE ID = @xid' 
EXEC sp_executesql @SQL, N'@xid bigint', @ID

Answer 2

1) create a new table that will have an identity PK and contain the string table names
2) insert all/only the valid tables you will allow in your procedure
3) use this int identity PK as the input parameter value (TableID) for the stored procedure
4) in the procedure, just look up the string value (table name) from the given identity PK and you are safe to concatenate that looked up string in your query. 5) your WHERE clause is fine since you pass in an int