Unexpected consequences of splicing SQL statements

During data operations, splicing SQL has various drawbacks and should have been abandoned for a long time. However, in actual development, due to various reasons, the company has been using this method (both the UI Layer and the logic layer have strict filtering, but there is no problem), but there were unexpected problems during development yesterday, A simple statement can cause serious consequences. A simple statement example is as follows:

Method for updating the total amount of money for a record whose primary key is 2

///   <Summary>
/// Update the total amount of money for a record whose primary key is 2
///   </Summary>
///   <Param name = "totalmoney"> Total modified amount of money </Param>
Public   Void Updatetotalmoney ( Int Totalmoney)
{
Int ID =   2 ; // Database Table Primary Key
Int Oldtotalmoney =   5000 ; // The original total amount of money for a record whose primary key is 2
Int Newtotalmoney = Totalmoney; // The amount of money after the record whose primary key is 2
Int Totalmoneychange = Oldtotalmoney - Newtotalmoney; // Changes in the total amount of money
String SQL =   String . Format ( " Update test1220 set totalmoney = {0}, remainmoney = remainmoney-{1} Where id = {2} " , Newtotalmoney, totalmoneychange, ID ); // The total amount of money changes, and the remaining amount of disposable money is also numbered
Sqlconnection con =   New Sqlconnection (sqlconnectstring );
Con. open ();
Sqlcommand cmd =   New Sqlcommand (SQL, con );
Cmd. executenonquery ();
Con. Close ();
}

 

Create a table and insert Test Data

Create   Table Test1220 (
ID Int   Identity ( 1 , 1 ) Not   Null   Primary   Key ,
Totalmoney Int   Null ,
Remainmoney Int   Null
)

Insert   Into Test1220 (totalmoney, remainmoney) Values ( 3000 , 2000 )
Insert   Into Test1220 (totalmoney, remainmoney) Values ( 5000 , 5000 )
Insert   Into Test1220 (totalmoney, remainmoney) Values ( 1000 , 1000 )
Insert   Into Test1220 (totalmoney, remainmoney) Values ( 3000 , 3000 )
Insert   Into Test1220 (totalmoney, remainmoney) Values ( 3000 , 3000 )
Insert   Into Test1220 (totalmoney, remainmoney) Values ( 3000 , 3000 )

 
Next, call updatetotalmoney () to update the total amount of money and remaining money for the record whose primary key is 2. updatetotalmoney (4000) and updatetotalmoney (8000) Update the record with ID = 2, what will happen? When updatetotalmoney (4000) is executed normally, only records with ID = 2 in the table are updated. However, after updatetotalmoney (8000) is executed, unexpected problems occur, the totalmoney of all records is changed to 8000, but the remainmoney is not modified. Why? If the where condition id = 2 has been added, how can it be updated to all records?

This makes it difficult for me to use a cainiao-level program. In simple statements, passing different parameters will sometimes be normal and sometimes abnormal, and the problem occurs only after repeated debugging, when the parameter is 8000, the obtained SQL statement is "Update test1220 set totalmoney = 8000, remainmoney = remainmoney-- 3000 where id = 2", It turns out to beThe subtraction operator and the negative sign are connected together into the annotation symbol of SQL.It is a dangerous operation to comment out the where condition during the update! This error is concealed, and it takes a long time to locate the error. If the database operation is parameterized, this will not happen.