c# - Preventing SQL Injection on ASP.NET Web Application

Question

I am new to C# and ASP.NET.

I am using VS2005 C# and SQL Server 2005 and have done some research on preventing SQL injections

I have a couple of functions in my server-side web application which I am unsure if they requires input validation.

---

1) Login control from the toolbox. I have implemented the login control directly from the VS Toolbox, and I tried to use a RegularExpressionValidator for my login tool but it does not seem to work. Does Microsoft already have a in-built validation for the tool?

---

2) Upload of excel file sheets into SQL Server database. I have a function which allows users to upload excel file sheets into the database. At the beginning I don't feel that there is a need to validate it as there is no open sql queries, but after that I'm asking myself if it is possible for the user to input SQL queries in the excel file which will cause SQL injection during uploading. Below is my upload code snippet, would be looking forward to advice if a validation is required:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 8.0;";

using (OleDbConnection connection =
             new OleDbConnection(connStr))
{
    string selectStmt = string.Format("Select [COLUMNS]  FROM [userlist$]");

    OleDbCommand command = new OleDbCommand(selectStmt, connection);

    connection.Open();
    Console.WriteLine("Connection Opened");
    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader())
    {
        // SQL Server Connection String
        string sqlConnectionString = "Data Source=<datasource>";

        // Bulk Copy to SQL Server
        using (SqlBulkCopy bulkCopy =
                   new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "UserDB";
            bulkCopy.WriteToServer(dr);;
        }
    }
}

---

3) INSERT statements. I have a couple of INSERT statements which is mainly used to insert new records into the database. As these statements do not actually grep data from the database, I am unsure if a validation is required. Below is a sample INSERT statement:

SqlConnection conn = new SqlConnection("<datasource>");
string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES ('" + un.Text + "', '" + pw.Text + "', '" + role.Text + "', '" + ms.Text + "', '" + dor.Text + "')"); --> all *.Text are textboxes on the webpage

SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

---

4) Search function in webpage. I have several .aspx pages which displays data by GridView. Below is an example of my search query which uses a textbox and a dropdownlist filter:

SqlDataSource1.SelectCommand = "SELECT * FROM [UserData] where [" + DropDownList1.Text + "] like '%" + searchTextBox.Text + "%'";
SqlDataSource1.DataBind();

---

I would like to know what is the easiest way to do a input validation check on the sql statements itself without creating additional methods and functions, to the above examples, as I have seen regular expression and using mysql_real_escape_string.

Thank you in advance for any advice and suggestions provided.

Direct examples given would be good as well.

Answer 1

Validation (checking for things like quotes etc) is never required in these examples, and should never be used (except for a few cases, where white-listing might be appropriate).

What is required is parameterisation. Use parameters instead of concatenation.

SqlBulkCopy handles the data directly, so that is fine, however:

SqlConnection conn = new SqlConnection("<datasource>");
string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES ('" + un.Text + "', '" + pw.Text + "', '" + role.Text + "', '" + ms.Text + "', '" + dor.Text + "')"); --> all *.Text are textboxes on the webpage

SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

is just asking to be abused horribly. YOUR SYSTEM IS BROKEN. You should have something like:

cmd.CommantText = "INSERT INTO [UserData] (Username, ...) VALUES (@username, ...)";
cmd.Parameters.AddWithValue("Username", un.Text);
...

or any of the other ways of adding parameters.

your example 4 is an interesting one, which is an example of where white-listing might be appropriate; SQL Server does not allow you to parameterise the column name, but you cannot trust the value coming up from the client. If you need that kind of "choose the column based on input", you must test it against the expected values:

string[] allowedColumns = new[] {"Name", "Description", "Foo", "Bar"};
string colName = ...
if(!allowedColumns.Contains(colName)) colName = allowedColumns[0]; // DENIED!

Once you have white-listed the column against expected values, you now know that the value is not "] where 1=1 drop table Users drop table Customers --"

However! the search value should be parameterised, i.e.

`... LIKE @searchValue`

where the searchValue parameter is given the value "%" + something.Text + "%"