SQL Injection for JDBC Connecting MySQL

Keywords: SQL Database JDBC Java

* JDBC is a bridge for Java to operate database.
With the help of the data-driven provided by the database and the database language to be operated on,
After executing the database statement, you can crud the records in the database.*

See the demo case of linking mysql database to simulate landing:

Case 1: SQL injection, deceiving the server to execute malicious SQL commands

1. Write a tool class, MyJdbcUtils, encapsulating two methods:

(1) Encapsulate the method getConnection() to connect to the database;

// Loading configuration files
static {    
    Properties p = new Properties();
    InputStream is;
    try {
        // Get the input stream path
        is = new FileInputStream("src/db.properties");
        // Loading input stream with Properties object
        p.load(is);
        //Driving path  
        drivername = p.getProperty("drivername");
        //Database Path
        url = p.getProperty("url");
        //Database username
        username = p.getProperty("username");
        //Database password
        password = p.getProperty("password");
    }catch (Exception e) {
        e.printStackTrace();
    }
}

public static Connection getConnection() throws Exception{
    //Load driver
    Class.forName(drivername);
    //Connect to the database
    Connection con=DriverManager.getConnection(url, username, password);
    return con;
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

(2) Method of encapsulating and releasing resources
releaseSource();

public static void releaseSource(ResultSet rs,Statement st,Connection con){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs=null;
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            st=null;
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            con=null;
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

2. Then sql injection is implemented through the following test class

This method of writing sql statements gives sql injection a good chance!!!!

public class Test1_sql_IllegalLogin {

    /**
     * sql Malicious Injection Case
     * Simulated landing
     */
    public static void main(String[] args) {
        // Keyboard Entry User Name and Password
        Scanner sc=new Scanner(System.in);
        System.out.println("username:");
        String username = sc.nextLine().trim();
        System.out.println("password:");
        String password = sc.nextLine().trim();

        login(username,password);
    }

    private static void login(String username,String password) {
        Connection con=null;
        Statement st=null;
        ResultSet rs=null;
        //Query the database to get the username and password
        try {
            con = MyJDBC_util_Illegal.getConnection();
            //Writing sql code
            String sql="select * from Person where username='"+username+"'and password='"+password+"'";
            //Execute sql code
            ps = con.createStatement();
            rs = ps.executeQuery(sql);          //Get the result set
            if(rs.next()){
                System.out.println("welcome!");     //Landing successfully
            }else{
                System.out.println("Login failed"); //Landing failed
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            MyJDBC_util_Illegal.releaseSource(rs, st, con);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

The following results will appear when running:

Is it very exciting???? You can log in without entering a password!!! Essential skills for number theft!!!! 6666~?

Of course, we are going to save the world in the future!! So this kind of thing has to be put an end to!!

However... How to prevent malicious injection?

Tips:
* Pre-compiler is used to set parameters in SQL statements before executing sql.
* The value of parameter in sql statement can be replaced by placeholder, which can achieve the function of fixed format.

Here comes the example.~~~~

Case 2: Preventing sql injection
1. Modify the variable Statement st to PreparedStatement ps; to precompile sql statements!
2. Write sql statements with placeholders? Instead of field values, so that you can fix the format of sql statements!

Look at the code:

public class Test2_sql_PreStatementLogin {
/**
 * sql Prevention of Injection
 * Simulated landing
 */
    public static void main(String[] args) {
        // Keyboard Entry User Name and Password
        Scanner sc=new Scanner(System.in);
        System.out.println("username:");
        String username = sc.nextLine().trim();
        System.out.println("password:");
        String password = sc.nextLine().trim();

        login(username,password);
    }

    private static void login(String username,String password) {
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        //Query the database to get the username and password
        try {
            con = MyJDBC_util_Login.getConnection();
            String sql="select * from Person where username=? and password=?";
            //Precompiled sql code
            ps = con.prepareStatement(sql);
            //Setting parameters
            ps.setString(1, username);
            ps.setString(2, password);
            //Execute sql code
            rs = ps.executeQuery();         //Get the result set
            if(rs.next()){
                System.out.println("welcome!");     //Landing successfully
            }else{
                System.out.println("Login failed"); //Landing failed
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            MyJDBC_util_Login.releaseSource(rs, ps, con);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

The key is to fix the format of sql statements, so that those malicious input statements can no longer be arrogant!!! Haha!!~~

I can't get in here now, can I ____________ Blue thin? Lentinus edodes?

Posted by DedoMraz on Sat, 18 May 2019 01:06:43 -0700