JDBC Programming for pre-compiled SQL and anti-injection attacks and the use of PreparedStatement tutorials

In JDBC programming, Statement, PreparedStatement, and callablestatement are commonly used in three ways to execute query statements, where Statement is used for general queries, PreparedStatement Used to perform parameterized queries, while CallableStatement is used for stored procedures.

1, Statement
The object is used to execute a static SQL statement and returns the execution result. The SQL statement here must be complete with a clear indication of the data. What's the record? Which record was changed? Have to be clearly instructed.
Create the object by calling the Createstatement method of the Connection object
Query: ResultSet excutequery (String sql)--the encapsulated object ResultSet that returns the result of the query. Use Next () to traverse the result set, GETXX () to get the record data.
Modified, deleted, added: int excuteupdate (String sql)--Returns the number of data table records affected.

2, PreparedStatement
The SQL statements are precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement efficiently several times.
You can get the PreparedStatement object by calling the PreparedStatement () method of the Connection object
The PreparedStatement object executes the SQL statement, with the argument with a question mark (?). To indicate that the setxxx () method of the PreparedStatement object is called to set these parameters. The Setxxx () method has two parameters, the first parameter is the index of the parameter in the SQL statement to set (starting at 1), the second is the value of the parameter in the set SQL statement, and when set with setxxx, you need to correspond to the field type in the database. For example, in MySQL the field is varchar, you need to use the SetString method, if it is a date type, you need to use the Setdate method to set the specific SQL parameters.

Simply put, a precompiled SQL statement is not a statement with a specific value, but rather a (? ) to replace the specific data, and then call the Setxx () method at execution time to pass in the specific data. At the same time, this statement is compiled only once for the first execution and then saved in the cache. After execution, the full SQL command can be obtained by simply extracting the compiled code from the cache and the new incoming specific data. This saves the compilation time of each subsequent execution statement.

use pre-compilation to step 4 steps:

1: Define a precompiled SQL statement, where the parameters to be filled in are used? occupies a position. Note that, regardless of the type, you do not need to add semicolons. The specific data type is determined at the following setxx ().

2: Create a precompiled statement and pass in the SQL statement. The SQL statement is now bound to this preparedstatement. So the 4th step executes the statement without having to pass the SQL statement as a parameter to execute ().

3: Fill in the specific parameters. Fill in the specific data for the SQL statement by SETXX (question mark subscript, value). Note: The question mark subscript starts with 1, SETXX is related to the numeric type, and the string is SetString (INDEX,STR).

4: Executes the Preprocessing object. Mainly include:

boolean	execute() PreparedStatementexecutes the SQL statement in this object, which can be any kind of SQL statement.
ResultSet	executeQuery() PreparedStatementexecutes the SQL query in this object and returns the object generated by the query ResultSet .
int	executeUpdate() PreparedStatementexecutes the SQL statement in this object, which must be a SQL data manipulation language (manipulation LANGUAGE,DML) statement, such as INSERT , a statement, or UPDATE DELETE a SQL statement with no return content, such as DDL statements.

Note that the SQL statement was passed in when the PreparedStatement was created, and the SQL statement is not required to be passed in at this time, which differs from the general statement execution of the SQL statement.

Like what:

String sql= "Select Sname from Stu where sno=?"

PreparedStatement prestmt = conn.prepareStatement(sql);

  prestmt.setString(1,sno);

Prestmt.executequery ();

Benefits of using precompilation:

1:preparedstatement faster than Statement
One of the most important benefits of using PreparedStatement is that it has a better performance advantage, and SQL statements are precompiled in the database system. The execution plan is also cached, which allows the database to make parameterized queries. Using a preprocessing statement is faster than a normal query because it does less work (database parsing of SQL statements, compilation, optimization already done before the first query).

2:preparedstatement to prevent SQL injection attacks

SQL injection attacks:SQL Injection is the use of the system's SQL engine to complete malicious behavior by using systems that do not adequately examine user input data and inject illegal SQL statement segments or commands into user input data.

For example, a website's login verification SQL query code is:

1	strSQL = "SELECT * FROM users WHERE name = ‘"+ userName + "‘ and pw = ‘"+ passWord +"‘;"

Malicious filling in:

12	userName = "1‘ OR ‘1‘=‘1";passWord = "1‘ OR ‘1‘=‘1";

Then the final SQL statement becomes:

1	strSQL = "SELECT * FROM users WHERE name = ‘1‘ OR ‘1‘=‘1‘ and pw = ‘1‘ OR ‘1‘=‘1‘;"

Because where conditions are constant, this is equivalent to executing:

1	strSQL = "SELECT * FROM users;"

So you can access the website without an account password. If a malicious user is worse, the user fills in:

1	strSQL = "SELECT * FROM users;"

The SQL statement becomes:

1	strSQL = "SELECT * FROM users WHERE name = ‘any_value‘ and pw = ‘‘; DROP TABLE users"

This way, the data tables are deleted, although they are not logged in.

A parameterized query using PreparedStatement can block most SQL injections. In the case of parameterized queries, the database system does not treat the contents of the parameters as part of the SQL instruction, but only runs the parameters after the database has completed compiling the SQL instructions, so even if the parameters contain destructive instructions, they will not be run by the database. Because for the parameterized query, the format of the query SQL statement is already stipulated, the data that needs to be checked is also set up, the missing is only the specific number of the data. So the user can only provide the data, and can only be provided on demand, can not further make impact on the database of other actions.

Resources:

Http://www.importnew.com/5006.html

JDBC Programming for pre-compiled SQL and anti-injection attacks and the use of PreparedStatement tutorials