Menu
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
211
Using stored procedure prevents SQL injection from happening since input parameters are always treated as an actual text value, rather than as a command (see Image 1.3).
The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.
You should always use parameterized statements where available, they are your number one protection against SQL injection. You can see more examples of parameterized statements in various languages in the code samples below.
Securing data using a Stored Procedure You can do that by granting that user the permission to call the stored procedure we created above. Let us create a role or user called John with only login privileges. Then we will grant him permission to call the stored procedure that inserts values into the Employees table.
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
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
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
