An inefficient SQL statement is enough to destroy the entire application.
Statement is the parent interface of preparedstatement. It reduces the overhead of precompilation without pre-compilation. It is slower to run preparedstatement at a time than statement.
Preparedstatement can implement all functions of statement, but it is called the pre-compilation command because it can be given an SQL string with a certain format when creating an object, then, the setxxx method is used to assign values to the specified SQL statement in the blank mode. With this feature, it is convenient to execute a fixed format string multiple times, it is more efficient. unlike statement, strings must be compiled before SQL Execution.
Preparedstatement requires server support to improve efficiency. For example, it has a significant effect on Oracle, while MySQL explicitly states that preparedstatement is not supported.
In Oracle, all SQL statements are first compiled, called an execution plan, and placed in a specific cache in Oracle. Each time the same SQL statement is encountered, it is first called in the cache, if statement is used every time without pre-compilation, it is required to be compiled every time. Many repeated "execution plans" in the buffer affect the database performance. another point is that when using setobject (), remember to use a method with the targetsqltype parameter to improve efficiency.
SQL injection attacks use design vulnerabilities to run SQL statements on the target server, the main reason for the successful SQL injection attack is that user input data is not verified when SQL statements are dynamically generated.
For JDBC, the SQL injection attack is only valid for statement and invalid for preparedstatement, because preparedstatement cannot change the logical structure of the query during insertion.
This method is only valid for statement and invalid for preparedstatement.
If there is an SQL statement: "select * from table where username = 'username '"
The statement SQL statement is written as follows: "select * from table where username = '" + variable value + "'"
The SQL statement of preparedstatement is written as follows: "select * from table where username =? "Then corresponds? Assignment
In this way, we can find that the input "AA 'or '1' = '1"
Statement is to connect this string with the SQL statement for execution.
Preparedstatement is to assign "AA 'or '1' = '1" as a string ?, As the corresponding value of the "username" field, it is clear that SQL Injection cannot be discussed.
The implementation mechanism is different. injection only damages the preparation (Compilation) process of SQL statements, and preparedstatement is ready. In the execution stage, the input string is used as data processing, no longer need to parse and prepare SQL statements, thus avoiding SQL Injection problems.