1. What is SQL injectionSQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server.
2. How to prevent SQL injectionThere are two ways to prevent SQL injection:A. Storing all SQL statements in a stored procedure avoids SQL injection, improves performance, and stored procedures can be written and centrally managed by a dedicated database administrator (DBA), but this practice sometimes has different criteria for querying the same tables. SQL statements can be different, so you write a large number of stored procedures. B. Use parameterized SQL query statements.
3. Why parameterized SQL queries can prevent SQL injectionin the case of parameterized queries, the database server does not treat the contents of the parameter as part of the SQL instruction, but only runs the parameters after the database has completed compiling the SQL instructions, 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 some of the functionality, however, the additional development cost of using parameterized queries is often much lower than the significant damage caused by the discovery of a SQL injection attack vulnerability.
4. How to write parameterized SQL query statements (C # statements SQL Server database)use parameters to represent values that need to be filled in when composing SQL instructions
The parameter format for Microsoft SQL Server is in the "@" character plus the parameter name, as follows:
SELECT * from myTable WHERE MyID = @myID
INSERT into MyTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)
Example 1:Normal SQL statements:string idparam= "001"; string querysql = "SELECT count from [Test] where id= '" +idparam+ ""; SqlCommand com = new SqlCommand (QUERYSQL, conn);
parameterized SQL statements:string querysql = "SELECT count from [Test] where [email protected]";
SqlCommand com = new SqlCommand (QUERYSQL, conn);
Com. Parameters.addwithvalue ("@id", idparam);
Example 2:Normal SQL statements:string updatesql= "INSERT into [test] (Id,count,datetime) VALUES ('" + Idparam + "', '" + countparam+ "', '" + System.DateTime.Now.ToString () + "')";SqlCommand com = new SqlCommand (updatesql, conn);
parameterized SQL statements:Updatesql = "INSERT into [test] (Id,count,datetime) VALUES (@id, @count, @datetime)";
Updatecomm = new SqlCommand (UPDATESQL, conn);
UpdateComm.Parameters.AddWithValue ("@id", idparam );
UpdateComm.Parameters.AddWithValue ("@count", countparam);
UpdateComm.Parameters.AddWithValue ("@datetime", System.DateTime.Now.ToString ());
How to write SQL statements that prevent SQL injection
