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

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 2

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 3

The easiest way to guard against that form of SQL injection, is to use parameters and stored procedures rather then building sql statements to run. (In C# or internally to SQL Server).

However I'm not entirely sure you should be spending time on this, unless of course it's your corporate policy, as the chances of it ever occuring internally are minimal at best, and if it did occur, I would hope you would know immediately who it is.

Answer 4

Yes, it can. Let's say the client sends this:

OR 1 = 1

That can be very painfull for your

  SELECT * FROM admin WHERE name = @name AND password = @password

You can prevent this with

• using Parameter class from ADO.NET
• using regexp
• reading, reading: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

Answer 5

Yes, they can happen. The easiest way to guard against this is to use prepared statements rather than building the SQL manually.

So, rather than this:

String sql = 
 String.Format("INSERT INTO mytable (text_column) VALUES ( '{0}' )",
   myTextBox.Text); // Unsafe!

You would do something like this:

String sql = "INSERT INTO mytable (text_column) VALUES ( ? )"; // Much safer

Then add the text of the text box as a parameter to your DbCommand which will cause it to be automatically escaped and replace the "?" in the SQL.

Answer 6

In addition to using prepared statements and parameters rather than concatenating strings into your SQL you should also do the following:

1. Validate and format user input on the server side. Client side validation and limits can easily be bypasses with tools like WebScarab, or by spoofing your form.

2. Configure appropriate permissions for the database user account. Web application should use a separate account or role in your database with permissions restricted to only the tables, views and procedures required to run your application. Make sure that user does not have select rights on the system tables

3. Hide detailed error messages from users, and use less common names for your objects. It amazes me how often you can determine the server type (oracle, mysql, sqlserver) and find basic schema information in an error message and then get information from tables called 'user(s)', 'employee(s)'. If you haven't set your permissions as in (2) and I can determine your server type you are open to statements like this for SQL Server

   SELECT table_name FROM information_schema.table

   EXECUTE sp_help foundTableName

Answer 7

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.