Handle single quote in SqlParameter in EXEC sp_setapprole?

Question

I have a method that executes a SqlCommand and returns the result to a winforms application. My method is this:

public bool ApplyRoles(string roleApp, string roleAppPassword)
{
     Command = new SqlCommand("EXEC sp_setapprole @roleApp, @rolePassword", Connection);
     AssignParam("roleApp", roleApp, SqlDbType.VarChar);
     AssignParam("rolePassword", roleAppPassword, SqlDbType.VarChar);
     bool ret = Command.ExecuteNonQuery() == -1;
     return ret;
}

and the AssignParam method is this:

public void AssignParam(string name, object value, SqlDbType type)
{
    var parameter = new SqlParameter(name, type)
    {
         Value = value ?? DBNull.Value
    };
    Command.Parameters.Add(parameter);
}

Now, this ApplyRoles method throws the exception: Application roles can only be activated at the ad hoc level. but if i change the ApplyRoles to this:

public bool ApplyRoles(string roleApp, string roleAppPassword)
{
   Command = new SqlCommand(string.Format("EXEC sp_setapprole '{0}', '{1}'", roleApp, roleAppPassword), Connection);
   bool ret = Command.ExecuteNonQuery() == -1;
   return ret;
}

The method works fine.. so i'm guessing that the problem is in the AssignParam method. What is the problem? I don't want to use the "working" method because i could have sql injection on it.

Answer 1

sp_setapprole uses the parameter names @rolename and @password. You have to use those parameter names if you are passing the parameter names into the a SqlCommand` instance. It was working for you in your second example because you were passing only the parameter values in the correct order that the stored proc was expecting them.

Since you're executing a stored procedure, use CommandType.StoredProcedure

Also put the '@' character in front of your parameter names.

Command = new SqlCommand("sp_setapprole", Connection);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddWithValue( @rolename, roleApp);
Command.Parameters.AddWithValue( @password, rolePassword);
bool ret = Command.ExecuteNonQuery() == -1;
     return ret;