was asked a question yesterday by a Daniel, why SQL Parameterized query can prevent SQL injection, what is the principle of parameterized query? Results stuffy force, before only know the parameterized query is to prevent SQL injection, but did not delve into its principle, today to find some articles, learning, but also share to everyone. The following references inform the great gods of the answer: the principle is the use of pre-compiled method, the SQL statement can be controlled by the client parameter set to compile, generate the corresponding set of temporary variables, and then use the corresponding setting method, for the elements inside the temporary variable set to assign Value, Assignment function setstring (), Enforces type checking and security checks on incoming parameters, thus avoiding the generation of SQL injection. Recently in-depth study of Java, attached a piece of implementation code, other languages to the assignment function processing encapsulation, resulting in the user is not visible, can not understand the mechanism. Import java.sql.preparedstatement; string sql = "SELECT * from user where username=?" and passwd=? "; PS = conn. PreparedStatement (SQL);p s.setstring (1, "admin");p s.setstring (2, "123456"); ResultSet = Ps.executequery (); Specific implementation Code reference java PreparedStatement (Java Platform SE 7)  , other languages can be implemented against his principles. Parameter queries are the ability of a database to be native, rather than being provided by a data access class library such as ADO, which is merely encapsulation of the former. We write in the program language of SQL statements and parameter objects, sent to the database or statements and parameters, not some of the answers think that the value of the parameter to be well-defined after splicing into the statement, and finally the statement to the database. To say what "preprocessing" a class library does, presumably only when the developer does not explicitly specify the type and length of the parameter, the class library automatically determines the appropriate type and length for it based on the value of the parameter. This is easily verified with database statement tracking tools such as SQL Server Profiler. So parameterized queries really do not close the program language/class library how many things. As for how the database is processed after receiving statements and parameters, my understanding/guess is that the subsystem that parses the query statement transforms/compiles the statement into a language that the database execution subsystem can executing (like the C # compiler compiles C # to IL to run the CLR), and in this step, The semantics of this batch of query statements are solidified into a set of behavioral actions, which is the so-called behavior action"Execution plan", the execution plan describes what is probably from where to fetch data, how to deal with the data, and so on, which is why the table name, field names can not be parameterized, because these things are not sure to generate an execution plan at all. As to whether the value of the parameter has any effect on the execution plan generation, there is, but it affects the ability to hit an index, statistics and other performance-related things, can produce a better execution plan (accurate guidance to a page to take data, etc.), otherwise go stupid method (such as full table scan), Without having an impact on the program's programme, this is why parametric anti-injection is a factor. Simple summary, the reason that the parameter can prevent injection is that the statement is a statement, parameter is a parameter, the value of the parameter is not part of the statement, the database only according to the semantics of the statement run, as for the run is to take a normal backpack or a monster, will not affect the road route, but run fast and slow point difference.
Why does parameterized queries prevent SQL injection? (GO)
