JDBC和SQL注入(二)

1 Indroduction

接上,简单演示下如何进行SQL注入,以及介绍下PreparedStatementStatement之间的区别

代码的目录结构如下:

.
├── lib
│   └── mysql-connector-java-8.0.17.jar
└── src
    └── main
        └── org
            └── zqq
                └── jdbc
                    ├── DBConnection.class
                    ├── DBConnection.java
                    ├── Driver.class
                    ├── Driver.java
                    ├── GetUserDetails.class
                    ├── GetUserDetails.java
                    ├── GetUserDetailsUsingPS.class
                    └── GetUserDetailsUsingPS.java

6 directories, 9 files

数据库内容如下:

2 Source files

DBConnection.java:

package org.zqq.jdbc;

import java.sql.*;

public class DBConnection {
    public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://localhost:3306/UserDB";
    public final static String DB_USERNAME = "root";
    public final static String DB_PASSWORD = "12345678";

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Connection con = null;

        Class.forName(DB_DRIVER_CLASS);

        con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

        System.out.println("DB connection created successfully");

        return con;
    }
}

GetUserDetails.java:

package org.zqq.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.zqq.jdbc.DBConnection;

public class GetUserDetails {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Please enter email id:");

        String id = scanner.nextLine();
        System.out.println("User id=" + id);
        System.out.println("Please enter password to get details:");
        String pwd = scanner.nextLine();
        System.out.println("User password="+pwd);
        printUserData(id, pwd);
    }

    private static void printUserData(String id, String pwd) throws ClassNotFoundException, SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = DBConnection.getConnection();
            stmt = con.createStatement();
            String query = "select name, country, password from Users where email = '" + id + "' and password ='" + pwd + "'";
            System.out.println(query);

            rs = stmt.executeQuery(query);

            while(rs.next()) {
                System.out.println("Name=" + rs.getString("name") + " ,country=" + rs.getString("country") + ", password=" + rs.getString("password"));
            }
        } finally {
            if (rs != null) rs.close();
            stmt.close();
            con.close();
        }

    }
}

然后编译运行:

$ javac -sourcepath src/main src/main/org/zqq/jdbc/GetUserDetails.java

$ java --class-path src/main:lib/* org.zqq.jdbc.GetUserDetails

Please enter email id:
840486874@qq.com
User id=840486874@qq.com
Please enter password to get details:
111111
User password=111111

DB connection created successfully
select name, country, password from Users where email = '840486874@qq.com' and password ='111111'
Name=zqq, country=CN, password=111111

3 Running

编译:

$ javac -sourcepath src/main src/main/org/zqq/jdbc/GetUserDetails.java

3.1 正常

运行:

$ javac -sourcepath src/main src/main/org/zqq/jdbc/GetUserDetails.java

$ java --class-path src/main:lib/* org.zqq.jdbc.GetUserDetails

Please enter email id:
840486874@qq.com
User id=840486874@qq.com
Please enter password to get details:
111111
User password=111111

DB connection created successfully
select name, country, password from Users where email = '840486874@qq.com' and password ='111111'
Name=zqq, country=CN, password=111111

3.2 SQL注入

运行:
关键在于输入了840486874@qq.com' or '1'='1,使得SQL语句变为select name, country, password from Users where email = '840486874@qq.com' or '1'='1' and password ='',因为'1'='1'永真,所以跳过了密码验证,这可能是最简单的SQL注入,找到SQL注入点

$ java --class-path src/main:lib/* org.zqq.jdbc.GetUserDetails
Please enter email id:
840486874@qq.com' or '1'='1
User id=840486874@qq.com' or '1'='1
Please enter password to get details:

User password=
DB connection created successfully
select name, country, password from Users where email = '840486874@qq.com' or '1'='1' and password =''
Name=zqq, country=CN, password=111111

4 Solutions

造成上述SQL注入的根本原因是用户输入作为了SQL语句的一部分,决定了SQL的语意。然而用户输入应作为SQL语句的参数存在,不能决定SQL的语意,PreparedStatement可以解决上述问题

GetUserDetailsUsingPS.java

package org.zqq.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class GetUserDetailsUsingPS {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		// read user entered data
		Scanner scanner = new Scanner(System.in);
		System.out.println("Please enter email id:");
		String id = scanner.nextLine();
		System.out.println("User id=" + id);
		System.out.println("Please enter password to get details:");
		String pwd = scanner.nextLine();
		System.out.println("User password=" + pwd);
		printUserData(id, pwd);
	}

	private static void printUserData(String id, String pwd) throws ClassNotFoundException,
			SQLException {

		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String query = "select name, country, password from Users where email = ? and password = ?";
		try {
			con = DBConnection.getConnection();
			ps = con.prepareStatement(query);
			
			//set the parameter
			ps.setString(1, id);
            ps.setString(2, pwd);
            System.out.println(ps.toString());
            
			rs = ps.executeQuery();

			while (rs.next()) {
				System.out.println("Name=" + rs.getString("name") + " ,country="
						+ rs.getString("country") + " ,password="
						+ rs.getString("password"));
			}
		} finally {
			if (rs != null)
				rs.close();
			ps.close();
			con.close();
		}

	}
}

编译运行:

$ javac -sourcepath src/main src/main/org/zqq/jdbc/GetUserDetailsUsingPS.java

$ java --class-path src/main:lib/* org.zqq.jdbc.GetUserDetailsUsingPS

Please enter email id:
840486874@qq.com' or '1'='1
User id=840486874@qq.com' or '1'='1
Please enter password to get details:

User password=

DB connection created successfully
com.mysql.cj.jdbc.ClientPreparedStatement: select name, country, password from Users where email = '840486874@qq.com\' or \'1\'=\'1' and password = ''

并没有出现之前SQL注入的情况,仔细发现SQL语句已经变了,select name, country, password from Users where email = '840486874@qq.com\' or \'1\'=\'1' and password = '',SQL语句中用户输入的特殊值被转义了

上述select语句查询出的应同时满足下面的情况:

  • email840486874@qq.com' or '1'='1
  • password为空

5 Others

可自行了解PreparedStatementStatement之间有什么样的区别,同时应注意并不是只要使用了PreparedStatement就可以避免SQL注入,如下仍然存在SQL注入:

PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();

6 References

https://www.youtube.com/watch?v=gU3DLOsw0Eg
https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example
https://stackoverflow.com/questions/1582161/how-does-a-preparedstatement-avoid-or-prevent-sql-injection

Tags:

Categories:

Updated: