Is it safe to use dynamic SQL with parameters? If not, what security issues could be exposed?


For example, this is the code that I am using:

String commandString = "UPDATE Members SET UserName = @newName , AdminLevel = @userLevel WHERE UserID = @userid";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnectionstring"].ConnectionString))
    SqlCommand cmd = new SqlCommand(commandString, conn);
    cmd.Parameters.Add("@newName", newName);
    cmd.Parameters.Add("@userLevel", userLevel);
    cmd.Parameters.Add("@userid", userid);

That code looks fine. Parameterisation is the way to go, as opposed to concatenating user-supplied values in an adhoc SQL statement which can open you up to sql injection attacks. This can also help with execution plan reuse.

The only thing I'd add, is I prefer to explicitly define the datatype and sizes of the parameters. For example, if you don't then, as an example, all string values will get passed in to the database as NVARCHAR instead of VARCHAR. Hence I like to be explicit.