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.
See Question&Answers more detail:
os