jdbc中Statement接口的sql注入

jdbc中Statement接口的sql注入及解决措施

代码实践
下面用最原始的jdbc连接代码演示一下

-- 环境
-- musql 8.0.12
-- jdbc jar包 mysql-connector-java-8.0.19.jar
--第一步   使用navicate创建数据库和表
-- 创建数据库
create database my_first;
-- 创建表
create table temp_user(
	u_id int primary key auto_increment,
	u_name varchar(255),
	u_password varchar(255)
)
-- 添加数据
insert into temp_user (u_name,u_password) values
('zhugeliang','zgl456'),
('zhouyu','zy326');

设计表对应的java类

import java.util.Objects;

/**
 * @author programmer trip
 * 根据表中的字段设计该类
 */
public class User {
    private int id;
    private String uName;
    private String uPassword;

    public User(){}

    public User(int id, String uName, String uPassword) {
        this.id = id;
        this.uName = uName;
        this.uPassword = uPassword;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getuName() {
        return uName;
    }

    public void setuName(String uName) {
        this.uName = uName;
    }

    public String getuPassword() {
        return uPassword;
    }

    public void setuPassword(String uPassword) {
        this.uPassword = uPassword;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        User user = (User) o;
        return id == user.id &&
                Objects.equals(uName, user.uName) &&
                Objects.equals(uPassword, user.uPassword);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, uName, uPassword);
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", uName='" + uName + '\'' +
                ", uPassword='" + uPassword + '\'' +
                '}';
    }
}

设计服务端类

import java.sql.*;

/**
 * @author programmer-trip
 *
 */
public class AppServer {

    /**
     * 用户名和密码的登录验证
     * @param userName 用户名
     * @param password 密码
     */
    public  User checkLogin(String userName,String password){
        Connection conn = null;
        Statement stat = null;
        ResultSet res = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_first?serverTimezone=UTC","root","123456");
            stat = conn.createStatement();
            String sql = "select * from temp_user where u_name = '" + userName + "' and u_password = '" + password + "'";
            res = stat.executeQuery(sql);
            while(res.next()){
                int id = res.getInt("u_id");
                String uName = res.getString("u_name");
                String uPassword = res.getString("u_password");
                return new User(id,uName,uPassword);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(res != null){
                    res.close();
                }
                if(stat != null){
                    stat.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
}

设计客户端

/**
 * @author programmer_trip
 */
public class AppClient {
    public static void main(String[] args) {
    	//写法一,该写法使得sql语句中的密码验证部分变成了注释,只要用户名存在就可以直接登录
    	//String uName = "zhugeliang'#"
    	//写法二
        String uName = "zhugeliang";
        String uPassword = "11111 ' or '1' = '1";
        
        AppServer appServer = new AppServer();
        User user = appServer.checkLogin(uName,uPassword);
        if(user != null){
            System.out.println("---------------验证成功,正在跳转--------------------");
        }else {
            System.out.println("---------------密码错误,请重新输入------------------");
        }
    }
}

//运行结果
---------------验证成功,正在跳转--------------------

在temp_user表中"zhugeliang"用户的密码是"zgl456" ,但是输入实参"11111 ’ or ‘1’ = '1"却能登录成功,容易造成数据安全隐患问题.
==> 用户可以通过参数改变sql语句的条件的个数,对于以上的案例相当于如下语句

"select * from temp_user where u_name = 'zhugeliang' and u_password = '11111' or '1' = '1'"

为了解决Statement接口的sql注入问题,可以使用PreparedStatement接口代替Statement接口进行预编译sql处理.

• PreparedStatement是Statement的子接口
• 可以确定sql语句的结构,无法通过其他条件进行增减sql语句的条件
• 通过 ? 提前占位,并确定语句的结构
• 可以防止sql攻击, 提高代码可读性,提高效率(采用预编译机制,编译一次,可重复执行多次该sql语句,不需再重新编译)

现在采用PreparedStatement代替Statement之后,再来看sql注入是否成功

import java.sql.*;

/**
 * @author programmer-trip
 *
 */
public class AppServer {

    /**
     * 用户名和密码的登录验证
     * @param userName 用户名
     * @param password 密码
     */
    public  User checkLogin(String userName,String password){
        Connection conn = null;
        //更改Statement为PreparedStatement
        PreparedStatement stat = null;
        ResultSet res = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_first?serverTimezone=UTC","root","123456");
            //更改sql语句并使用 ?  进行占位
            String sql = "select * from temp_user where u_name = ?  and u_password = ?";
            stat = conn.prepareStatement(sql);
            //使用相应的setXXX方法为sql模板中的占位符赋值
            stat.setString(1,userName);
            stat.setString(2,password);
            res = stat.executeQuery();
            while(res.next()){
                int id = res.getInt("u_id");
                String uName = res.getString("u_name");
                String uPassword = res.getString("u_password");
                return new User(id,uName,uPassword);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(res != null){
                    res.close();
                }
                if(stat != null){
                    stat.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
}

再次执行客户端的main方法,出现如下结果(sql注入失败)

---------------密码错误,请重新输入------------------