JDBC Learning Note (6): PreparedStatement Solving SQL injection problems

SQL injection issue: Requires that user information be specified based on the incoming name query:

1  PackageCom.xxyh.jdbc;2 Importjava.sql.Connection;3 ImportJava.sql.ResultSet;4 Importjava.sql.SQLException;5 Importjava.sql.Statement;6 7  Public classSqlinject {8     9      Public Static voidMain (string[] args)throwsSQLException {TenRead ("' or 1 or '"); One     } A      -     /** - * Query user information according to user name the      * @paramName user name -      * @throwsSQLException -      */ -      Public Static voidRead (String name)throwsSQLException { +Connection conn =NULL; -Statement stmt =NULL; +ResultSet rs =NULL; A         Try { atconn =jdbcutilssingleton.getinstance (). getconnection (); -stmt =conn.createstatement (); -String sql = "Select Id,name,birthday,money from user where name= '" "+name+" "; -rs =stmt.executequery (SQL); -              -              while(Rs.next ()) { inSystem.out.println (Rs.getobject ("id") + "\ T" + rs.getobject ("name") + "\ T" + -Rs.getobject ("Birthday") + "\ T" + rs.getobject ("Money"))); to             } +}finally { - jdbcutilssingleton.getinstance (). Close (RS, stmt, conn); the         } *     } $}

"Run result": 1 zhangs 1985-01-01 400.02 Lisi 1986-01-01 200.03 Wangwu 1987-01-01 300.0The result is a query in addition to all users ' information. The reason for this is because the SQL statements above are:

Select id,name,birthday,money fromuserwhere name='  or1or' '

That is, querying Name= ' (empty string) or name=1 user information, 1 means that the Boolean value is true. So the entire statement is identical to no conditional restrictions.

Reverse injection via PreparedStatement:

1  Public Static voidRead2 (String name)throwsSQLException {2Connection conn =NULL;3PreparedStatement PS =NULL;4ResultSet rs =NULL;5         Try {6conn =jdbcutilssingleton.getinstance (). getconnection ();7String sql = "Select Id,name,birthday,money from user where name=?";8             //pre-processing prior to execution9PS =conn.preparestatement (SQL);TenPs.setstring (1, name);//1 represents the first in a SQL statement?  Oners =ps.executequery (); A              while(Rs.next ()) { -System.out.println (Rs.getobject ("id") + "\ T" + rs.getobject ("name") + "\ T" + -Rs.getobject ("Birthday") + "\ T" + rs.getobject ("Money"))); the             } -}finally { - jdbcutilssingleton.getinstance (). Close (RS, PS, conn); -         } +}

• When you include special characters or SQL keywords (such as: ' 1 or 1 ') in SQL, statement will have unpredictable results (exceptions or incorrect query results) that can be resolved with PreparedStatement.
• PreparedStatement advantages relative to statement:

> No SQL injection issues >statement causes the database to compile SQL frequently, potentially causing a database buffer overflow. > databases and drivers can be optimized for PreparedStatement (valid only if the associated data connection is not closed).

JDBC Learning Note (6): PreparedStatement Solving SQL injection problems