SqlCommmand parameters not adding to UPDATE statement (C#, MVC)

Question

If you look at the stuff commented out, I can easily get this to work by adding user input directly in to the query, but when I try to parameterize it, none of the values are being added to the parameters...

This code is throwing an error

Must define table variable @formTable

but the issue is none of the values are adding, not just the table variable (verified by replacing table name variable with static text).

I have many insert statements in this project structured exactly like this one which work perfectly. What am I doing wrong here?

string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))
{
    //string query = "UPDATE " + s.formTable + " SET " + s.column + " = '" + s.cellValue + "' WHERE MasterID = '" + s.id + "'";
    string query = "UPDATE @formTable SET @column = @cellValue WHERE MasterID = @id;";

    using (SqlCommand cmd = new SqlCommand(query))
    {
        //SqlParameter param = new SqlParameter("@formTable", s.formTable);
        //cmd.Parameters.Add(param);
        cmd.Parameters.AddWithValue("@formTable", s.formTable);
        cmd.Parameters.AddWithValue("@column", s.column);
        cmd.Parameters.AddWithValue("@cellValue", s.cellValue.ToString());
        cmd.Parameters.AddWithValue("@id", s.id.ToString());

        cmd.Connection = con;

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

David · Accepted Answer · 2017-12-14 19:42:04Z

Parameters are for values, not object identifiers (tables, columns, etc.), so the only valid parameters you have are @cellValue and @id.

If you want to dynamically set table/column names based on user input, you're likely looking at string concatenation. However, that doesn't necessarily mean SQL injection. All you need to do is validate the user input against a set of known values and use the known value in the concatenation.

For example, suppose you have a List<string> with all of your table names. It can be hard-coded if your tables are never going to change, or you can make it more dynamic by querying some system/schema tables in the database to populate it.

When a user inputs a value for a table name, check if it's in the list. If it is, use that matching value from the list. If it isn't, handle the error condition (such as showing a message to the user). So, even though you're using string concatenation, no actual user input is ever entered into the string. You're just concatenating known good values which is no different than the string literals you have now.

Collectives™ on Stack Overflow

SqlCommmand parameters not adding to UPDATE statement (C#, MVC)

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related