JAVA预防SQL注入问题

JAVA预防SQL注入问题

一、SQL注入例子

@Test
public void testLogin() throws  Exception {
    //1.1 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以将127.0.0.1:3306/简化书写成/
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    // 接收用户输入 用户名和密码
    String name = "sjdljfld";
    String pwd = "' or '1' = '1";
    // 拼接sql,就是这里出了问题
    String sql = " select * from tb_user where username = '"+name+"' and password = '"+pwd+"' ";
	//变成了密码等于空或1=1
    '"+pwd+"'变成了' ' or '1' = '1 '
	select * from tb_user where username = 'sjdljfld' and password = '' or '1' = '1'

   
    // 获取stmt对象
    Statement stmt = conn.createStatement();
    // 执行sql
    ResultSet rs = stmt.executeQuery(sql);
    // 判断登录是否成功
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }

    //7. 释放资源
    rs.close();
    stmt.close();
    conn.close();
}

二、解决SQL注入

@Test
    public void testLogin() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
 
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        String userName = "";
        String password = "";
        Connection conn = DriverManager.getConnection(url, userName, password);
 
        //接受用户输入的用户名和密码
        String name = "sjdljfld";
        String pwd = "' or '1' = '1";
        //定义SQL
        String sql = "select * from tb_user where username = ? and password = ?";
 
        //获取statement对象,将conn.createStatement换成conn.prepareStatement
        PreparedStatement pstmt = conn.prepareStatement(sql);
        
 
        //设置？的值.pstmt.set数据类型(第几个问号,参数);
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);
        //转义完成后再拼接就变成了
       select * from tb_user where username = 'sjdljfld' and password = '\'or \'1\' = \'1'
		//在这个过程中,不是直接拼接,而是确保值被正确转移后再赋值.
         //如sql注入的' or '1' = '1中的'会变成转义字符,所以这里面的'不会产生单双引号的sql注入问题
           
        //执行sql
        ResultSet rs = pstmt.executeQuery();
 
        //判断登录是否成功
        if(rs.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
 
        //释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }