JDBC 第三章 SQL注入

1.SQL注入概述

用户输入的信息中含有sql语句的关键词，并且这些关键词参与sql语句的编译过程，会导致sql语句的原意被扭曲，达到sql注入
例如输入：用户名aaa 密码aaa’ or ‘1’ = '1

String sql = "select*from t_user where loginName = '" + loginName + "' and loginPwd = '" + loginPwd + "'";

用户填入后完成sql语句的拼接，发送sql语句给DBMS，DBMS进行sql编译，将非法信息编译进入，扭曲原含义

2.解决SQL注入问题

使用java.sql.PrepareedStatement，让用户输入的信息不参与SQL语句的编译
PreparedStatement接口继承了Statement，输入预编译数据库操作对象，原理是，预先对SQL语句的框架进行编译，然后再给SQL语句传“值”。

public class JDBCTest08 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean loginSuccess = false;

        Scanner s = new Scanner(System.in);
        System.out.println("用户名");
        String loginName = s.nextLine();
        System.out.println("密码");
        String loginPwd = s.nextLine();
        
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://loaclhoat:3306/bjpowernode", "root", "333");
            //3.获取预编译的数据库操作对象
            //一个?代表一个占位符，将来接收一个值，占位符不能用单引号括起来
            String sql = "select * from t_user where loginName = ? and loginpwd = ?";
            ps = conn.prepareStatement(sql);
            //第一个下标是1
            ps.setString(1,loginName);
            ps.setString(2,loginPwd);
            //4.执行sql
            rs = ps.executeQuery();
            //5.处理结果集
            if (rs.next()) loginSuccess = true;
            System.out.println(loginSuccess);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

3.Statement和PreparedStatement的对比

• Statement存在sql注入问题，PreparedStatement解决了sql注入问题
• Statement是编译一次执行一次，PreparedStatement是编译一次执行N次，效率较高
• PreparedStatem会在编译阶段做类型的安全检查
• PreparedStatement使用较多，当业务方面要求必须支持SQL注入时，必须使用Statement
  示例：根据用户输入升序降序输出

public class JDBCTest09 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        Scanner scanner = new Scanner(System.in);
        System.out.println("输入desc或asc");
        String keyWords = scanner.nextLine();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://loaclhoat:3306/bjpowernode", "root", "333");
            stmt = conn.createStatement();
            String sql = "select ename from emp order by " + keyWords;
            rs = stmt.executeQuery(sql);
            while (rs.next()) System.out.println(rs.getString("ename"));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

4.PreparedStatement完成DML

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://loaclhoat:3306/bjpowernode", "root", "333");

String sql = "insert into dept (deptno,dname.loc) values (?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1,60);
ps.setString(2,"销售部");
ps.setString(3,"上海");

String sql = "update dept set dname = ?,loc = ? where deptno = ?";
ps = conn.prepareStatement(sql);
ps.setString(1,"研发一部");
ps.setString(2,"北京");
ps.setInt(3,60);

String sql = "delete from dept where deptno = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1,60);

int count = ps.executeUpdate();
System.out.println(count);
//5.处理结果集(不需要)
//6.释放资源

传送门

上一章：JDBC 第二章 JDBC编程六步
下一章：JDBC 第四章 JDBC事务机制