Prevent SQL injection

Recently, the younger brother maintenance of the educational system has some problems, there is a SQL injection loophole, the boss let me show them, so in the online search, and looked at the project code, the project uses statement, decided to replace PreparedStatement.

Statement is the parent interface of PreparedStatement and does not precompile, reducing the overhead of precompiling. Single Run

PreparedStatement can implement all functions of statement, but it is called precompiled instruction because it can be given a certain format of SQL string when creating an object of it, and then use its Setxxx method to assign a value to the specified SQL statement in a blank way , with such an attribute, it is convenient and more efficient to execute a fixed-format string multiple times. Unlike statement, it is necessary to compile the string before executing SQL.

oracle all the SQL language Sentence first compiled, called "Execution plan", placed in a specific Oracle internal cache, each encounter the same SQL, will be called in the cache, if not precompiled, each time with statement, then every time to compile, in the buffer there will be a lot of repeated "execution plan" Affect the performance of the database. Another point is that when using setobject (), be sure to use the method with Targetsqltype parameters to improve efficiency.

sql injection attack exploits a design vulnerability that runs SQL statements on the target server and does not validate the data entered by the user when dynamically generating SQL statements, which is the main reason for the successful SQL injection attack. for JDBC , the SQL injection attack is valid only for statement and is not valid for PreparedStatement because PreparedStatement does not allow the logical structure of the query to be changed at insert time. bypass authentication, However, this method is only valid for statement and invalid for preparedstatement.

If there is an SQL statement: "SELECT * from table where user name = ' User name '"
The statement SQL statement reads: "SELECT * from table where user name = '" + Variable Value + "'"
The PreparedStatement SQL statement reads: "SELECT * from table where user name =?" then the corresponding assignment
So we'll find the input "AA ' or ' 1 ' = ' 1"
Statement is to concatenate this and SQL statements into strings to be executed together. PreparedStatement is that "AA ' or ' 1 ' = ' 1" is assigned as a string, as the corresponding value of the "username" field, so SQL injection does not have to be discussed.

The implementation mechanism is different, injected only to the SQL statement Preparation (compilation) process has a destructive effect, and PreparedStatement is ready, the execution phase is just the input string as data processing, no longer need to parse the SQL statement, preparation, therefore also avoids the SQL injection problem.