How to handle erroneous data?

Question

How do you deal with user input (unicode) that you need to be restricted to a certain set of values, and you want to minimize the risk to applications that you pass the data to further down the line. For example, if I were to store the data in SQL, I would want to remove any chance of a SQL injection. If I were to send it over the wire via HTTP, I would want to make sure it doesn't malform the request, etc..

I guess what I am asking is there any generic method for data sanitization?

Answer 1

In case of saving to the database this is very simple. Just use parametes (DbParameter objects) - they will protect you from SQL injection and also will add escape symbols if necessary.

The code can be like this:

OleDbConnection cn = new OleDbConnection(strConn);
cn.Open();
strSQL = "INSERT INTO customers (Name) VALUES (@Name)";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@Name", "John O'Brian");
cmd.ExecuteNonQuery();

Answer 2

Each interface has its own problems when it comes to ways to compromise the system. If you want to play it safe you will need to tailor the validations to suit the problems and/or threats that are relevant in the current context.

If a certain text box in a user interface should be used for numeric input, make sure that the user cannot type (or paste) anything non-numeric into it. If a certain control is used to collect a date from the user, validate that the given value is indeed a valid date (perhaps it should even fall within a certain range; validate that too).

Make sure to url encode anything that is being passed as a query string value in a http request. Use stored procedures and pass the values as parameters to them.

And so on. There is no free lunch, unfortunately.

Answer 3

Like nightcoder has suggested, parameters are the way to avoid SQL injection. If you're using SQL though, consider using the SqlClient namespace as it is more efficient than its OleDb counterpart and was created specifically for SQL Server 7 and up.

Using nightcoder's above example:

SqlConnection cn = new SqlConnection(strConn);
cn.Open();
strSQL = "INSERT INTO customers (Name) VALUES (@Name)";
SqlCommand cmd = new SqlCommand(strSQL, cn);
cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.Varchar)).Value = "John O'Brian";
cmd.ExecuteNonQuery();

Something to keep in mind about the SqlClient namespace is that if you're writing for older systems (Win98), then there may be compatibility issues, making OldDBxxx the better choice.

Cheers!