CODEWITHSUNDEEP
c#sqlsql-injection
Brad
Brad

Reputation: 21190

SQL injection on INSERT

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?

Upvotes: 35

Views: 94026

Answers (8)

David Webb
David Webb

Reputation: 193774

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.

Upvotes: 64

Vivek Sharma
Vivek Sharma

Reputation: 2687

Prevent SQL Injection by using prepared statement. The use of placehoder(?) totally eliminates sql Injection Vulnerability. example String sql=Select * from user_table where username='+request.getparameter("username")+'; statement.executeQuery(sql);

the above statement is vulnerable to sql injection.

To make it safe against sql injection. Use following the snippet

String sql=Select * from user_table where username=?; statement.setString(1,username);

Upvotes: 0

K. Brian Kelley
K. Brian Kelley

Reputation: 1677

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.

Upvotes: 3

Gary.Ray
Gary.Ray

Reputation: 6501

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

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60529

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.

Upvotes: 0

boj
boj

Reputation: 11395

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

Upvotes: -1

Bravax
Bravax

Reputation: 10493

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.

Upvotes: -4

tvanfosson
tvanfosson

Reputation: 532625

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 );

...

Upvotes: 27

Related Questions