CODEWITHSUNDEEP
sql-injectionasp.net-4.0
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

How to handle sql injection from asp.net

What is the best way from asp.net to avoid sql injections but at the same time I want to set my user free to enter any special symbol.

Edit

I am not using any parametrised query, I am using enterprise library and stored procedure

Upvotes: 0

Views: 499

Answers (2)

Quentin
Quentin

Reputation: 943108

Use the SqlCommand.Prepare Method as mentioned on bobby-tables.

private static void SqlCommandPrepareEx(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand(null, connection);

        // Create and prepare an SQL statement.
        command.CommandText =
            "INSERT INTO Region (RegionID, RegionDescription) " +
            "VALUES (@id, @desc)";
        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
        SqlParameter descParam = 
            new SqlParameter("@desc", SqlDbType.Text, 100);
        idParam.Value = 20;
        descParam.Value = "First Region";
        command.Parameters.Add(idParam);
        command.Parameters.Add(descParam);

        // Call Prepare after setting the Commandtext and Parameters.
        command.Prepare();
        command.ExecuteNonQuery();

        // Change parameter values and call ExecuteNonQuery.
        command.Parameters[0].Value = 21;
        command.Parameters[1].Value = "Second Region";
        command.ExecuteNonQuery();
    }
}

Upvotes: 1

Eric
Eric

Reputation: 95093

Parameterized queries. Use them wherever you have a query at all, and your user can enter in any symbol they particularly feel like.

If you're using an ORM, this is pretty much handled for you, but if you aren't, then what you need to do is something like this:

comm.CommandText = "insert into MyTable (col1, col2) values (@col1, @col2)";
comm.Parameters.AddWithValue("@col1", 123);
comm.Parameters.AddWithValue("@col2", "; drop table whatever; --");
comm.ExecuteNonQuery();

That query is 100% safe to run ad nauseum. Let .NET just handle the parameters for you, and you'll be all set.

Also, make sure you're using nvarchar (Unicode) columns, rather than varchar, if your users are going to be inserting symbols outside of the ANSI character set.

Upvotes: 2

Related Questions