SQL injection on INSERT

Question

I have created a small survey web page on our company Intranet. This web page is not accessible from the outside.

The form is simply a couple of radio buttons and a comments box.

I would like to maintain good coding practices and would like to guard against SQL Injections.

Can SQL injections happen on a insert statement with comments from the textbox? If so, how can I guard against it using .NET 2.0?

Answer 1

Injection can happen on any SQL statement not run properly.

For example, let's pretend your comment table has two fields, an integer ID and the comment string. So you'd INSERT as follows:

 INSERT INTO COMMENTS VALUES(122,'I like this website'); 

Consider someone entering the following comment:

'); DELETE FROM users; -- 

If you just put the comment string into the SQL without any processesing this could turn your single INSERT in to the following two statements followed by a comment:

INSERT INTO COMMENTS VALUES(123,''); DELETE FROM users; -- '); 

This would delete everything from your users table. And there are people willing to spend all day finding the right tablename to empty using trial and error and various tricks. Here's a description of how you could perform an SQL Injection attack.

You need to use parameterized SQL statements to prevent this.

And this isn't just for security reasons. For example, if you're creating your SQL statements naively the following comment:

I'm just loving this website 

would cause an SQL syntax error because of the apostrophe being interpreted by SQL as a closing quote.

Answer 2

Use parameterized queries so that the text is automatically quoted for you.

SqlCommand command = connection.CreateCommand();
command.CommandText = "insert into dbo.Table (val1,val2,txt) values (@val1,@val2,@txt)";
command.AddParameterWithValue( "val1", value1 );
command.AddParameterWithValue( "val2", value2 );
command.AddParameterWithValue( "txt", text );

...

Answer 3

SQL injection can happen any time you pass a query back to the database. Here's a simple demonstration:

SQL Injection Explained

The key, within .NET, is to do as Dave Webb has given. It will prevent the injection attempt by encompassing the entire string as one parameter to be submitted, handling all characters that might be interpreted by SQL Server to change the query or append additional commands.

And it should be pointed out that SQL injection can occur on any application, not just web applications. And that an internal attack is usually the most costly to an organization. One cannot safely assume that an attack won't originate from within.