The first thing to declare is that this article is purely an ignorant view of a little developer without foresight and knowledge, and is intended only for reference in Web system security.
1. Prerequisites
Many students want PreparedStatement to print out the resulting SQL, which may be used for learning, for system maintenance, and possibly for other purposes;
I also have the idea and the need, but after many practices and attempts, I found that in my capacity, I was unable to achieve.
So I found a tool, LOG4JDBC, that can cut into the JDBC layer and pull out SQL before the actual SQL executes.
This tool is quite powerful, but the end result will be beyond your imagination ....
The PreparedStatement prints out the final executed SQL.
Use the Log4jdbc tool.
2. Using LOG4JDBC Tools
See my blog:
A good memory is better than a bad pen. 14-use LOG4JDBC to display full SQL statements and execution times
http://blog.csdn.net/ffm83/article/details/43407905
There are detailed instructions.
3, the test JSP page
<%@ page language="java" import="java.util.*" pageencoding= "UTF-8"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" ><html><head><title>Test of a classmate of a meal</title></head><body> <form Action="Alogin.action" method="POST" Name="Form1"> <table Width="392" border="1"> <tr> <TD Height="+"><br> <div Align="center"> <p>User name:<input type="text" name="username" size= ></P> <p>Password:<input type="password" name="password" size = > </P> </div></td> </tr> <tr Align="center"> <TD colspan="2" bgcolor= "#FFCCFF"><input type="Submit"value="Login" /> </td> </tr> </table> </form></body></html>
4. Use the LOG4JDBC tool to print the Java code of the PreparedStatement final execution sql
PackageCom.struts2;ImportJava.sql.Connection;ImportJava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportJavax.servlet.http.HttpServletRequest;ImportOrg.apache.commons.dbutils.DbUtils;ImportOrg.apache.commons.lang.xwork.StringUtils;ImportOrg.apache.struts2.ServletActionContext;ImportCom.db.DBUtils;ImportCom.db.Log4JDBCTest;ImportCom.opensymphony.xwork2.ActionSupport;/** * A simple login authentication function, only for illustrative situations * * @author Fan Fangming */ Public class loginaction extends actionsupport { Private Static Final LongSerialversionuid =7854497526623985504L PublicStringExecute()throwsException {System.out.println ("---loginaction start--"); HttpServletRequest request = Servletactioncontext.getrequest (); String username = Request.getparameter ("username"); String Password = request.getparameter ("Password"); Request.setattribute ("username", username);//username and password if one is empty, the return fails if(Stringutils.isblank (username) | | Stringutils.isblank (password)) {return "false"; } Connection con =NULL;Try{//con = DBUTILS.GETCONNO2O ();con = LOG4JDBCTEST.GETCONNBM (); String sql ="SELECT COUNT (*) as Count from Ffm_user where"+"username =?" and password =? "; System.out.println ("Print out the sql:"+ SQL); PreparedStatement PS = con.preparestatement (SQL); Ps.setstring (1, username); Ps.setstring (2, password); ResultSet rs = Ps.executequery (); while(Rs.next ()) {intCount = Rs.getint ("Count");//Do not consider the issue of password encryption, if you find a result in the database, then when the password is correct if(Count >0) {return "Success"; }Else{return "false"; } } }Catch(Exception e) {E.printstacktrace (); }finally{dbutils.closequietly (con); }return "Success"; }}
5, start the application, run
Input: User name: FFM, Password: 1
Print out the Sql:select count (*) as Count from Ffm_user where username =? and password =?
LOG4JDBC print out the sql:
Select COUNT (*) as Count from Ffm_user where username = ' FFM ' and password = ' 1 ' {executed in 147 msec}
Does it feel good to meet our needs?
Let's change the input.
Input: Username: ' or 0 = ' 0 ' –, Password input: 2 or any other
Print out the Sql:select count (*) as Count from Ffm_user where username =? and password =?
LOG4JDBC print out the sql:
Select COUNT (*) as Count from Ffm_user where username = "or 0 = ' 0 ' – ' and password = ' 2 ' {executed in 331 msec}
It looks good, but there's a huge hole in it.
Watch the program carefully, enter: User name: ' or 0 = ' 0 ' –, Password input: 2 is not verifiable, and log4jdbc print out of SQL, placed in plsql execution, is validated.
In other words, the actual execution of SQL is not what we print out to see.
6. Conclusion
**LOG4JDBC can only print general SQL that PreparedStatement eventually executes and cannot print SQL with special characters.
The method of using PreparedStatement parameters is capable of targeted defense of SQL injection. **
Watch your door.-Protect data store (2)-View PreparedStatement final execution of SQL