formular security remove chars before DB write

Question

how to prevent security leaks in my own created form which data I write into my db?

Basicly I thought to replace the dangerous chars ('',"",~,....)... but I don't know how to do that in a clean way for each formular element ( more than 20)...

I don't know if asp.net provides an easy thing for that.? Ok the validators I do already some validation but at least I like to remove all all the dangerous and exotic chars.

I don't like to make a replace function for each textbox in my formular... Hope there is an other solution which works for all simple and properly.

thank you

EDIT: OK. I do the insert with a function of the API of the CMS Kentico. So of course it's paremeterized there.

Answer 1

You should to use parametrized queries; this way your user can't inject SQL.

SqlCommand command = new SqlCommand(
    "SELECT * FROM Table WHERE ID=@Id", connection);
command.Parameters.AddWithValue("@Id", 1);

Answer 2

If you use parametrized queries there's no risk of SQL injection and you can store any characters in the DB. Later when displaying them on the page you need to make sure you HTML encode the data.

Answer 3

All your SQL statements should use SqlParameters rather than being constructed as complete strings. This will prevent SQL injection attacks.

NO:

var cmd = new SqlCommand("INSERT INTO MyTable (MyColumn) VALUES ('" + formValue + "')", connection);
cmd.ExecuteNonQuery();

YES:

var cmd = new SqlCommand("INSERT INTO MyTable (MyColumn) VALUES (@FormValue)", connection);
cmd.Parameters.AddWithValue("@FormValue", formValue);
cmd.ExecuteNonQuery();

Answer 4

You should use a parameterized call to the database. It will escape characters as necessary and allow them to be persisted in your database safely.

E.g.

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "myStoredProc";
    command.CommandType = CommandType.StoredProcedure;

    DbParameter parameter = command.CreateParameter();
    parameter.ParameterName = "myParameter";
    parameter.DbType = DbType.AnsiString;
    parameter.Size = 100;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = "foo";

    command.ExecuteNonQuery();
}

or

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "insert myTable (column1) values @myParameter";
    command.CommandType = CommandType.Text;

    DbParameter parameter = command.CreateParameter();
    parameter.ParameterName = "myParameter";
    parameter.DbType = DbType.AnsiString;
    parameter.Size = 100;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = "foo";

    command.ExecuteNonQuery();
}

You can safely substitute 'foo' for any string you like without exposing yourself to SQL injection attacks.