Watch your door.-Protect data store (2)-View PreparedStatement final execution of SQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.