UPDATE sql table value

Question

I have following table:

and I want to update every cell, the user can define.

I have following code:

 private void button3_Click_1(object sender, EventArgs e)
    {                                                                              
        string columnName = textBox8.Text;
        string IDNum = textBox7.Text;


        using (SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Produkt.mdf;Integrated Security=True"))
        {
            try
            {
                SqlCommand cm = new SqlCommand();
                cm.Connection = cn;

                //as was the update statement
                cm.CommandText = "UPDATE ProduktTable SET " + columnName + " = " + textBox2.Text + " WHERE Id = @id";
                //clear all parameters insert statements uesd
                cm.Parameters.Clear();
                SqlParameter sp_update_col1 = new SqlParameter();
                sp_update_col1.ParameterName = "@id";
                sp_update_col1.SqlDbType = SqlDbType.VarChar;
                sp_update_col1.Value = IDNum;
                cm.Parameters.Add(sp_update_col1);
                //
                cn.Open();
                cm.ExecuteNonQuery();
                cn.Close();

             }
            catch (Exception ex)
            {
                MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }

The point is that, the user can update the desired cell after defining the columnName, which can either be (Navn, Varenr, Antal, Enhed, Priseksklmoms , Konto) and the Id value. textBox2.Text defines the new value, which replaces the old one in a specific cell. I'm lost in order to find a procedure of how to handle this. Any advice ?.

Answer 1

Try with this

cm.CommandText = "UPDATE ProduktTable SET [" + columnName + 
                 "] = @newValue WHERE Id = @id";
cm.Parameters.Clear();

SqlParameter sp_update_key = new SqlParameter();
sp_update_key.ParameterName = "@id";
sp_update_key.SqlDbType = SqlDbType.VarChar;
sp_update_key.Value = IDNum;
cm.Parameters.Add(sp_update_key);

SqlParameter sp_update_value = new SqlParameter();
sp_update_value.ParameterName = "@newValue";
sp_update_value.SqlDbType = SqlDbType.VarChar;
sp_update_value.Value = textBox2.Text;
cm.Parameters.Add(sp_update_value);
cn.Open();
cm.ExecuteNonQuery();
cn.Close();

The problem, in your original code, is the value that is not enclosed in single quotes (assuming that all of your columns are of varchar datatype) In this way the parser thinks that you have a column named 'Hans' and not a string to use as value.

Using a parameter should remove the problem.

Said that, keep in mind that your query is not safe. It is at risk of sql injection because a malicious user could write anything in your textbox including sql commands that could destroy your database.

At least putting square brakets around the table name should render (a little) more difficult an sql injection. A more safer way is to not let your user type the column name, but force him/her to choose the column name from a DropDownList.