The affected rows are 2 while there is only one item in SQL Server

Question

While using the C# code to delete rows in SQL Server, the affect rows amount returned is 2. But there is only one item in the table. Here is the code.

            int result = -1;
            using (SqlConnection sqlConnection = new SqlConnection(AppConfiguration.ConnectionStringIguide))
            {
                string sql = string.Format("delete from atblOrders where OrderID='{0}'", orderId);
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = sql;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlConnection.Open();
                    result = sqlCommand.ExecuteNonQuery();
                    sqlConnection.Close();
                }
            }

I copy the SQL into SQL Server Management Studio and run the SQL. It prints out two lines of 1 rows affected.

(1 rows affected) (1 rows affected) Completion time: 2021-12-13T13:53:52.0466180+08:00

If I use select query with the same id, it only returns one item. So, why there is two rows affected while deleting?

Is there a DELETE trigger on the table that deletes records in another table? — juergen d, Dec 13 '21 at 06:16
I agree it is most likely a DELETE trigger, but this trigger might insert, update or delete. If it is for audit trail purposes it might well be an insert! — Jonathan Willcock, Dec 13 '21 at 06:21
@juergend. Yes. I use `select * from sysobjects a INNER JOIN sys.triggers b ON b.object_id = a.id INNER JOIN syscomments c ON c.id = a.id WHERE a.xtype = 'tr'` to check the triggers, and I find one related with this table. — Robin Sun, Dec 13 '21 at 06:27

score 1 · Accepted Answer · answered Dec 13 '21 at 06:59

1

As mentioned in the comments, this is most likely because there's a trigger (either for or instead of) delete defined for this table.

However, this is not the main problem in your code. The main problem in your code is that it's vulnerable to SQL injection attacks since you're concatenating strings into your SQL instead of using parameters.

For more information, you can read my blog post entitled Back to basics: SQL Injection, and Sandy's question and it's answers here on stackoverflow - Why do we always prefer using parameters in SQL statements?

answered Dec 13 '21 at 06:59

Zohar Peled

79,642
10
69
121

Whilst I agree in general with getting into the good habit of using parameters in all cases, I feel it is fair to point out that the code as written by the OP is not really vulnerable to SQL injection. C# is a strongly typed language. Assuming orderId is an int (a natural assumption given the use of string.Format), then at run time any attempt to put harmful SQL into orderId will fail. – Jonathan Willcock Dec 14 '21 at 05:35
@JonathanWillcock. Yes, order id is an int. Good eyes. Anyway, using parameters is a good habit. – Robin Sun Dec 14 '21 at 05:37
@JonathanWillcock On the other hand, `where OrderID='{0}'` seems to imply that the order id might not be an int (and that's one of the things I've considered while writing my answer) - however I agree that if you get to the point where ints can be used to perform sql injection attacks, it's probably the least of your worries. for more information, read Jon Skeet's blog post entitled [THE BOBBYTABLES CULTURE](https://codeblog.jonskeet.uk/2014/08/08/the-bobbytables-culture/) – Zohar Peled Dec 14 '21 at 08:08
1

@ZoharPeled What disturbs me more is the implication that OrderId (column) is not an int as opposed to orderId (variable). Storing numbers as strings in the database is a serious failing. Of course it could still be an int and the OP is getting SQL Server to cast unnecessarily. – Jonathan Willcock Dec 14 '21 at 08:15
1

@JonathanWillcock Unnecessary casting is a problem indeed, but it's not a very big problem. Storing data in the wrong data type can have much worst impact on the database. Aaron Bertrand wrote about it in [Bad Habits to Kick : Choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type). – Zohar Peled Dec 14 '21 at 08:23

The affected rows are 2 while there is only one item in SQL Server

1 Answers1