SQL Parameterized Query

parameterized queries (parameterized query or parameterized Statement) refer to the use of parameters (Parameter) to give values when designing a link to a database and accessing data, where values or data need to be populated, This approach is now considered to be the most effective defense against SQL injection attacks (SQL injection) attack tactics.

Principle

  In the case of parameterized queries, the database server does not treat the contents of the parameter as part of the SQL instruction, but rather   the database, and sql  directive, the parameter is run, so even if the parameter contains malicious instructions, it will not be run by the database because it has been compiled. Some developers may think that using parameterized queries can make the program less maintainable or inconvenient to implement partial functionality, however, the additional development cost SQL injection attack The vulnerability was found to be attacked, resulting in significant losses.

How to write SQL instructions
When composing SQL instructions, parameters are used to represent the values that need to be filled in, for example:Microsoft SQL ServerThe parameter format for Microsoft SQL Server is the "@" character plus the parameter name, and SQL Server also supports the anonymous parameter "?". SELECT * from myTable WHERE MyID = @myIDINSERT into myTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)ADOSqlCommand sqlcmd = new SqlCommand ("INSERT into MyTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)", sqlconn); sqlcmd.   Connection = conn; Connection stringsqlcmd. CommandText = SQL; SQL statements
sqlcmd. Parameters.Add ("@c1", sqldbtype.text,30).  VALUE=C1; Assign value
Or: sqlcmd. Parameters.addwithvalue ("@c1", 1); Sets the value @c1 the parameter. sqlcmd. Parameters.addwithvalue ("@c2", 2); Sets the value @c2 the parameter. sqlcmd. Parameters.addwithvalue ("@c3", 3); Sets the value @c3 the parameter. sqlcmd. Parameters.addwithvalue ("@c4", 4); Sets the value @c4 the parameter. Sqlconn. Open (); sqlcmd. ExecuteNonQuery (); sqlconn. Close ();

SQL Parameterized Query