Java--JDBC learning--preparedstatement

You can get the PreparedStatement object by calling the PreparedStatement () method of the Connection object.
The PreparedStatement interface is a sub-interface of Statement, which represents a pre-compiled SQL statement.
The PreparedStatement object represents a parameter in the SQL statement 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), and the second is the value of the parameter in the SQL statement that is set.

Comparison of PreparedStatement and Statement

1, the readability and maintainability of the code.

2, PreparedStatement can improve the performance of the maximum possible:
DBServer provides performance optimizations for pre-compiled statements. Because precompiled statements are likely to be called repeatedly, the execution code of the statement after the compiler compiled by the dbserver is cached, so that the next call will not need to compile as long as it is the same precompiled statement, as long as the parameters are passed directly into the compiled statement execution code will be executed.
In the statement statement, even though the same operation but because the data content is not the same, the entire statement itself does not match, there is no meaning of the cached statement. The fact is that there is no database that executes code caches after the normal statement is compiled. This compiles the incoming statement once every time it is executed.
(Syntax checking, semantic checking, translating into binary commands, caching)

3, PreparedStatement can prevent SQL injection.

Example:

@Test Public voidtestpreparedstatement () {Connection Connection=NULL; PreparedStatement PreparedStatement=NULL; Try{Connection=jdbctools.getconnection (); String SQL= "INSERT into customers (name, email, birth)" + "VALUES (?,?,?)"; PreparedStatement=connection.preparestatement (SQL); Preparedstatement.setstring (1, "Soyoungboy"); Preparedstatement.setstring (2, "[Email protected]"); Preparedstatement.setdate (3,                    NewDate (Newjava.util.Date (). GetTime ());        Preparedstatement.executeupdate (); } Catch(Exception e) {e.printstacktrace (); } finally{jdbctools.releasedb (NULL, PreparedStatement, connection); }    }

SQL injection attacks

Concept:

SQL injection is the practice of using the system's SQL engine to complete malicious behavior by injecting illegal SQL statement segments or commands into user input data, using some systems that do not adequately examine the data entered by the user.

For Java, to prevent SQL injection, as long as the use of PreparedStatement to replace Statement.

SQL Injection Example:

/*** SQL injection. */@Test Public voidtestsqlinjection () {String username= "a ' OR PASSWORD ="; String Password= "OR ' 1 ' = ' 1"; String SQL= "SELECT * from users WHERE username = '" +username+ "' and" + "password = '" + Password + "'";        SYSTEM.OUT.PRINTLN (SQL); Connection Connection=NULL; Statement Statement=NULL; ResultSet ResultSet=NULL; Try{Connection=jdbctools.getconnection (); Statement=connection.createstatement (); ResultSet=statement.executequery (SQL); if(Resultset.next ()) {System.out.println ("Login Successful!"); } Else{System.out.println ("The user name and password do not match or the user name does not exist."); }        } Catch(Exception e) {e.printstacktrace (); } finally{jdbctools.releasedb (resultSet, statement, connection); }    }

Use PreparedStatement to resolve SQL injection examples:

/*** Using PreparedStatement will effectively solve the SQL injection problem. */@Test Public voidTestSQLInjection2 () {String username= "a ' OR PASSWORD ="; String Password= "OR ' 1 ' = ' 1"; String SQL= "SELECT * from users WHERE username =?" + "and password =?"; Connection Connection=NULL; PreparedStatement PreparedStatement=NULL; ResultSet ResultSet=NULL; Try{Connection=jdbctools.getconnection (); PreparedStatement=connection.preparestatement (SQL); Preparedstatement.setstring (1, username); Preparedstatement.setstring (2, password); ResultSet=Preparedstatement.executequery (); if(Resultset.next ()) {System.out.println ("Login Successful!"); } Else{System.out.println ("The user name and password do not match or the user name does not exist."); }        } Catch(Exception e) {e.printstacktrace (); } finally{jdbctools.releasedb (ResultSet, PreparedStatement, connection); }    }

Java--JDBC learning--preparedstatement