How to update comment of a specific row in SQL table with WHERE clause when its empty?

Question

I have a gridview of a SQL table. The first two columns automatically get selected and put into the grid when the webpage starts and a date is selected. The third column is a comment section where I'd like to let the user input comments themselves. I have a text box that they can enter comments in when they select a row, but I can get the column to update properly.

Run_DB_Script("update Log_Transfers set Comment = '" + tmpBox.Text + "' where '" + GridView1.Rows[Row] + "'", ref tmpErr);

The bracketed [Row] is a int that is set to the row number they put in.

It executes the code, but nothing is there after hitting update.

Your query is directly concatenating user input and is easily exploitable for `sql injection`. I highly recommend you read up on it, [this for example](https://www.youtube.com/watch?v=iElrPcUPZnA) is worth your time! — Stu, May 19 '21 at 19:45
[How does the SQL injection from the “Bobby Tables” XKCD comic work?](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — J..., May 19 '21 at 19:48
[What is SQL injection?](https://stackoverflow.com/q/601300/327083) — J..., May 19 '21 at 19:49
Use the debugger to see what `GridView1.Rows[Row]` evaluates to. It is not going to be something that can go into a WHERE clause and I would place a bet you are getting an exception. Think about how you would write a WHERE clause yourself and then make the code do that.... after fixing the sql injection. — Crowcoder, May 19 '21 at 19:52

MichaelD · Accepted Answer · 2021-05-20T18:24:56.147

Building a SQL statement using the input from a user is opening the door for a SQL Injection hack. A better alternative is to use parameters.

// set up the sql command to run with parameter placeholders
//  parameters are prefixed with an @
var command_text = "update Log_Transfers set Comment = @comment where @grid_row";
// Define the two parameters
SqlParameter comment = new SqlParameter("@comment",tmpBox.Text);
SqlParameter grid_row = new SqlParameter("@grid_row", ....);

SqlCommand cmd = new SqlCommand();
cmd.Text = command_text;

// Add the parameters to the command
cmd.Parameters.Add(comment);
cmd.Parameters.Add(grid_row);
cmd.Execute();

Best to declare data type explicitly, also C# uses double quotes `new SqlParameter("@comment", SqlDbType.VarChar, 50).Value = tmpBox.Text;` — Charlieface, May 19 '21 at 22:35

How to update comment of a specific row in SQL table with WHERE clause when its empty?

1 Answers1