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
Add a comment
|
2 Answers
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.
2 Comments
Zerotoinfinity
+1 But can you also suggest me any good practice I need to follow even while using stored procedure ?
Eric
If you're calling a stored procedure, you'll want to use
comm.Prepare() as stated otherwise, but other than that, this is pretty much the best practice to avoid injection.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();
}
}
