"Java Programming" JDBC Injection attack-statement and PreparedStatement

In the previous "Java programming" build a simple JDBC connection-drivers, Connection, Statement and PreparedStatement we described how to use the JDBC driver to establish a simple connection, and realize the use of statement and PreparedStatement database query, this blog will continue the previous blog through SQL injection attack comparison statement and PreparedStatement. Of course, there are many other differences between the two, in the subsequent blog will continue to update.

"Statement Query"

1. Add a method to query user through username and password in Dbhelper.java.

public static void Querybyuser (String username,string password) {Connection conn = dbconnection.getconnection (); Statement stmt = null; ResultSet rs = null;try {stmt = Conn.createstatement (); rs = Stmt.executequery ("SELECT * from user where username = '" + US Ername+ "' and password= '" +password+ "'"), while (Rs.next ()) {User user = new User (), User.setid (Rs.getint ("id")); User.setusername (rs.getstring ("username")); User.setpassword (rs.getstring ("password")); User.setgender ( Rs.getboolean ("gender")); User.setregtime (Rs.getdate ("Regtime")); System.out.println (User.tostring ());}} catch (SQLException e) {e.printstacktrace ();} Finally {Dbconnection.closeresultset (RS);D bconnection.closestatement (stmt);D bconnection.closeconnection (conn);}}

2. Add a test method to test in Dbhelpertest.java

public void Querybyusertest () {Dbhelper.querybyuser ("Jack", "Jack");

Java Side test Results:

User [id=2, Username=jack, Password=jack, Gender=true, regtime=2014-05-14]

The test results show that all the information of the user is successfully obtained through a valid user name and password.

3, through the MySQL log information, tracking query SQL statements. Refer to how MySQL tracks SQL statements in the specific way.

Open the Mysql.log log, and keep track of the latest logs, as shown here:

140514 10:16:13   QuerySET character_set_results = NULL [   queryshow VARIABLES]   queryshow   WARNINGS Queryshow COLLATION   QuerySET autocommit=1   queryselect * from user where username = ' Jack ' and Password= ' J Ack '   Quit

The log information indicates that the database side executes the normal query statement.

4. Add an injection attack test in Dbhelpertest.java, username input:hack (any string), password input:' or ' 1 ' = ' 1

public void Querybyuserinjecttest () {dbhelper.querybyuser ("hack", "' or ' 1 ' = ' 1");}

Java Side test Results:

User [Id=1, Username=andy, Password=andy, Gender=true, regtime=2014-05-13]
User [id=2, Username=jack, Password=jack, Gender=true, regtime=2014-05-14]
User [Id=3, Username=rose, Password=rose, Gender=false, regtime=2014-05-13]

The test results show that an illegal user can get all the user information in the Users table by injecting an attack, which is horrible!

5, through the MySQL log information, tracking query SQL statements, analysis of the data end what happened.

140514 10:23:14   connect[email protected] on Db_bbs QuerySET NAMES latin1 all   QuerySET character_set_ Results = NULL   queryshow VARIABLES (   queryshow WARNINGS)   queryshow COLLATION   Autocommit=1   Queryselect * from user where username = ' hack ' and password= ' or ' 1 ' = ' 1 ' +   Quit

The database side executes a statement:

SELECT * FROM user where username = ' hack ' and password= ' or ' 1 ' = ' 1 '

Because the where condition is constant , it is equivalent to executing:

SELECT * FROM user

All user information is obtained through this statement.

"PreparedStatement query" 1, in Dbhelper.java a new method to query the user through username and password.

public static void Querypreparebyuser (String username,string password) {Connection conn = dbconnection.getconnection (); PreparedStatement PS = null; ResultSet rs = null;try {PS = conn.preparestatement ("SELECT * from user where username =?") and password =? "); Ps.setstring (1,username);//Set placeholder parameter ps.setstring (2, password); rs = Ps.executequery (); while (Rs.next ()) {User user = new User (); User.setid (Rs.getint ("id")), User.setusername (rs.getstring ("username")), User.setpassword (Rs.getstring (" Password ")), User.setgender (Rs.getboolean (" Gender ")), User.setregtime (Rs.getdate (" Regtime ")); System.out.println (User.tostring ());}} catch (SQLException e) {e.printstacktrace ();} finally {Dbconnection.closeresultset (RS);D bconnection.closestatement ( PS);D bconnection.closeconnection (conn);}}

2. Add a test method to test in Dbhelpertest.java

public void Querybypreparedusertest () {Dbhelper.querypreparebyuser ("Jack", "Jack");

Java Side test Results:

User [id=2, Username=jack, Password=jack, Gender=true, regtime=2014-05-14]

The test results show that all the information of the user is obtained through a valid user name and password.

3, through the MySQL log information, tracking query SQL statements.

140514 10:37:04-   connect[email protected] on Db_bbs +   QuerySET NAMES latin1   QuerySET character_set_ Results = NULL   queryshow VARIABLES-   queryshow WARNINGS-queryshow   COLLATION-   QuerySET Autocommit=1   Prepareselect * from user where username =? and password =?   ExecuteSelect * FROM user where username = ' Jack ' and password = ' Jack '   Close stmt +   Quit

The log information indicates that the database side first performed thePre-compilation, and executes a normal query statement.

4. Add an injection attack test in Dbhelpertest.java:

public void Querybyprepareduserinjecttest () {dbhelper.querypreparebyuser ("hack", "' or ' 1 ' = ' 1");}

Java Side test Results:

did not print out any message, that is, did not get the user's information, the injection attack is invalid !

5, through the MySQL log information, tracking query SQL statement, why inject attack invalid?

140514 10:42:42   QuerySET character_set_results = NULL (   queryshow VARIABLES)   queryshow   WARNINGS Queryshow COLLATION   QuerySET autocommit=1   prepareselect * from user where username =? and password =? 
   19 ExecuteSelect * from user where username = ' hack ' and password = ' \ ' or \ ' 1\ ' =\ ' 1 '   Close stmt   

The original was executed: SELECT * FROM user where username = ' hack ' and password = ' \ ' or ' 1\ ' =\ ' 1 '

Reference

JDBC Statement vs Preparedstatement–sql injection Example (recommended)

Why JDBC uses preparedstatement instead of statement

"You may be interested."

Establish a simple JDBC connection-drivers, Connection, Statement and PreparedStatement

Reprint Please specify source: http://blog.csdn.net/andie_guo/article/details/25775163, thank you!