c# - 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.