How to prevent SQL injection

Question

I use stored procedures. To save time, I made some general procedures that use dynamic sqlin order for updating. Such a general procedure:

CREATE PROCEDURE [dbo].[SetField] @company_id uniqueidentifier, @id bigint, @field_code nvarchar(50), @value nvarchar(50) AS BEGIN DECLARE @field_name nvarchar(50) SET @field_name = NULL SELECT @field_name=field_name FROM dbo.FIELD_DEFINITION WHERE field_code=@field _code IF @field_name IS NOT NULL BEGIN IF @value IS NULL OR @value='' BEGIN SET @value='NULL' END ELSE BEGIN IF @field_code='START_DATE' OR @field_code='END_DATE' BEGIN SET @value = CONVERT(datetime, @value ,103) END SET @value='''' +@value +'''' END DECLARE @sql nvarchar(1000) SET @sql = 'UPDATE dbo.TABLE '+ 'SET ' +@field _name+'=' +@value +' '+ 'WHERE company_id=''' + CAST(@company_id as nvarchar(36)) + ''' AND '+ 'id='+CAST(@id as nvarchar) EXEC(@sql) END END 

How can I prevent SQL injection with this code?

Answer 1

An important aspect to remember about SQL injection is that, if at all possible, you should never embed user-supplied values ​​directly in your SQL. This does not mean that you cannot use dynamic sql (although it definitely makes things easier if you don't), but sometimes it becomes more dangerous.

In your specific example, you can save the parameterization of everything except @field_name . This, unfortunately, has to be built directly into SQL; everything else can be passed as a parameter again, so there is no need to worry about their contents.

The safest thing you can do in this particular example is the following:

 if(exists (select 1 from INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Table' and TABLE_SCHEMA = 'dbo' and COLUMN_NAME = @fieldName)) begin DECLARE @sql nvarchar(1000) SET @sql = 'UPDATE dbo.TABLE '+ 'SET ' + QUOTENAME(@field_name) + ' =@value ' + 'WHERE company_id=@company _id AND '+ ' id=@id ' exec sp_executesql @sql,N'@id bigint, @company_id uniqueidentifier, @value nvarchar(50)',@id,@company_id,@value end 

This does two things:

• It checks that there is actually a column with that name in the table. If the user inserted any other SQL statements into this field, this check would fail and the statement would not be executed. You can also raise raiseerror to report an error, but I will leave this exercise to you.
• It encloses the field name in square brackets so that field names containing spaces or reserved words will not violate the operator. This may not be a problem for you, but it is always a good practice if you are creating SQL yourself.

Answer 2

You said:

To save time, I made some general procedures that use dynamic sql to update

If you asked first, we could save time and suggest this ...

 UPDATE dbo.TABLE SET Field1 = CASE WHEN @field_name = 'Field1' THEN @value ELSE Field1 END, Field2 = CASE WHEN @field_name = 'Field2' THEN @value ELSE Field2 END, Field3 = CASE WHEN @field_name = 'Field3' THEN @value ELSE Field3 END, ... Fieldn = CASE WHEN @field_name = 'Fieldn' THEN @value ELSE Fieldn END WHERE company_id = @company_id AND id = @id 

Answer 3

I would start looking for ways to prevent SQL injection attacks before you ever call SP. Be careful with dynamic SQL strings assembled along with queries or form data. Use the SqlCommand object.

Edit: In response to the comment, there is a good explanation here of how parameterized queries (SqlCommand queries) help prevent SQL injection.

From http://forums.asp.net/t/1568268.aspx :

... The placeholder - @Id - became part of the hard-coded SQL. At run time, the value provided by the query is passed to the database along with hard-coded SQL, and the database checks the ProductID field when it tries to associate the parameter value with it. This provides a level of strong typing. If the parameter value is not the correct type for the database field (a string or a numeric value that is out of range for the field type), the database will not be able to convert it to the desired type and reject it. If the data type of the target field is a string (char, nvarchar, etc.), the Parameter value will be automatically "stiffened", which includes escaping single quotes. It will not be part of an executable SQL statement.