JDBC强化关键_004_SQL 注入与批处理

发布于：2025-06-18 ⋅ 阅读:(23) ⋅ 点赞:(0)

目  录

一、实现简单的登录功能

二、SQL 注入

1.演示

2.说明 

3.PreparedStatement

三、PreparedStatement 的其他操作

1.新增

2.修改

3.删除 

4.模糊查询

5.分页查询

6.blob 数据

（1）插入 

（2）读取

四、批处理

1.未使用批处理

2.使用批处理

一、实现简单的登录功能

public class DbUtils {

    // 工具类的构造方法都是私有的，不能被实例化。
    private DbUtils() {}

    // 静态变量
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        // 读取属性配置文件
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc.cjjdbc");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        user = bundle.getString("user");
        password = bundle.getString("password");

        // 注册驱动只需要执行一次，所以放在静态代码块中，在类加载时执行
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Login {
    public static void main(String[] args) {
        System.out.println("======欢迎登录系统======");

        // 获取用户输入
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String userName = sc.nextLine();
        System.out.print("请输入密码：");
        String password = sc.nextLine();

        // 连接数据库
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        boolean login = false;
        String realName = null;

        try {
            connection = DbUtils.getConnection();
            statement = connection.createStatement();

            String selectSQL = "select * from t_user where name = '" + userName + "' and password = '" + password + "'";
            results = statement.executeQuery(selectSQL);

            // 结果集中有数据，则登录成功
            if (results.next()) {
                login = true;
                realName = results.getString("realname");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(connection, statement, results);
        }
        System.out.println(login ? "登录成功，欢迎" + realName : "登录失败");
    }
}

二、SQL 注入

1.演示

        先进行操作演示 SQL 注入。

2.说明 

  1. 导致 SQL 注入最根本的原因：先进行 SQL 语句字符串拼接，再进行 SQL 语句的编译。用户输入中包含了 SQL 语句关键字并参与了编译；
  2. 解决方法：JDBC 为 Statement 接口提供了一个子接口 PreparedStatement，被称为预编译数据库操作对象。其可以对 SQL 语句进行预编译，然后给预编译好的 SQL 语句占位符传值。

3.PreparedStatement

/*
 * PrepareStatement 登录
 * 1.对 SQL 语句进行预编译
 * 2.为 SQL 语句中的占位符传值
 * */
public class Login {
    public static void main(String[] args) {
        System.out.println("======欢迎登录系统======");

        // 获取用户输入
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String userName = sc.nextLine();
        System.out.print("请输入密码：");
        String password = sc.nextLine();

        // 连接数据库
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet results = null;
        boolean login = false;
        String realName = null;

        try {
            connection = DbUtils.getConnection();

            // 创建 PrepareStatement 对象
            String selectSQL = "select * from t_user where name = ? and password = ?";
            preparedStatement = connection.prepareStatement(selectSQL);

            // 为 SQL 语句中的占位符传值， 从 1 开始
            preparedStatement.setString(1, userName);
            preparedStatement.setString(2, password);

            System.out.println(selectSQL);
            results = preparedStatement.executeQuery();

            // 结果集中有数据，则登录成功
            if (results.next()) {
                login = true;
                realName = results.getString("realname");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(connection, preparedStatement, results);
        }
        System.out.println(login ? "登录成功，欢迎" + realName : "登录失败");
    }
}

  1. 通过使用 PreparedStatement，SQL 注入问题已被解决。因为【123' or '1'='1】虽然含有 SQL 关键字，但是只会被当作普通值传入，并没有参与编译；
  2. 注意：
    1. 带有占位符【?】的 SQL 语句称为：预处理 SQL 语句；
    2. 占位符不能使用引号包裹，若被包裹，它只是一个普通的问号字符；
    3. 执行 SQL 语句前，必须给占位符传值。
  3. PreparedStatement 和 Statement 都是执行 SQL 语句的接口，区别是：
    1. PreparedStatement 预编译 SQL 语句，Statement 直接提交 SQL 语句；
    2. PreparedStatement 执行速度更快，同一条 SQL 语句一次编译、多次执行，且可以避免 SQL 注入攻击；
    3. PreparedStatement 会进行类型检查。

三、PreparedStatement 的其他操作

# 初始化
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP(
    EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10);

1.新增

public class PreparedStatementInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String insertSQL = "insert into emp values(?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(insertSQL);
            ps.setInt(1, 8000);
            ps.setString(2, "牛马");
            ps.setString(3, "IT");
            ps.setInt(4, 7902);
            LocalDate date = LocalDate.parse("2000-01-01");
            ps.setDate(5, java.sql.Date.valueOf(date));
            ps.setDouble(6, 5000);
            ps.setDouble(7, 500);
            ps.setInt(8, 10);
            int count = ps.executeUpdate();
            System.out.println("新增" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }
}

2.修改

public class PreparedStatementUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String updateSQL = "update emp set ename = ? where empno = ?";
            ps = conn.prepareStatement(updateSQL);
            ps.setString(1, "冯国强");
            ps.setInt(2, 8000);
            int count = ps.executeUpdate();
            System.out.println("修改" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }
}

3.删除 

public class PreparedStatementDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String deleteSQL = "delete from emp where job = ?";
            ps = conn.prepareStatement(deleteSQL);
            ps.setString(1, "IT");
            int count = ps.executeUpdate();
            System.out.println("删除" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }
}

4.模糊查询

public class PreparedStatementLikeSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            String selectSQL = "select ename, job from emp where ename like ?";
            ps = conn.prepareStatement(selectSQL);
            ps.setString(1, "_O%");
            rs = ps.executeQuery();
            System.out.println("[ename]\t[job]");
            while (rs.next()) {
                System.out.println(rs.getString("ename") + "\t" + rs.getString("job"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }
}

5.分页查询

        分页查询可以回顾【MySQL基础关键_006_DQL（五）】。

        查询第二页所有员工的姓名，每页显示 3 条数据。 

public class PreparedStatementPageSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int pageSize = 3;
        int pageNum = 2;
        try {
            conn = DbUtils.getConnection();
            String selectSQL = "select ename from emp limit ?,?";
            ps = conn.prepareStatement(selectSQL);
            ps.setInt(1, (pageNum - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("ename"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }
}

6.blob 数据

        blob（binary large object），二进制大对象。

# 初始化
drop table if exists t_img;
create table t_img(
    id bigint primary key auto_increment,
    name varchar(10),
    img blob
) engine = innoDB;

（1）插入 

public class PSBlobInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream fis = null;
        try {
            conn = DbUtils.getConnection();
            String selectSQL = "insert into t_img(name, img) values(?, ?)";
            ps = conn.prepareStatement(selectSQL);
            ps.setString(1, "JDBC");
            fis = new FileInputStream("D:\\test.jpg");
            ps.setBlob(2, fis);
            int count = ps.executeUpdate();
            System.out.println("插入" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            DbUtils.close(conn, ps, null);
        }
    }
}

（2）读取

public class PSBlobSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            String selectSQL = "select img from t_img where name = ?";
            ps = conn.prepareStatement(selectSQL);
            ps.setString(1, "JDBC");
            rs = ps.executeQuery();
            while (rs.next()) {
                InputStream in = rs.getBinaryStream("img");
                OutputStream out = new FileOutputStream("D:\\testNew.jpg");
                byte[] bytes = new byte[1024];
                int count = 0;
                while ((count = in.read(bytes)) != -1) {
                    out.write(bytes, 0, count);
                }
                out.flush();
                in.close();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }
}

四、批处理

# 初始化
drop table if exists t_batch;
create table t_batch(
    id bigint primary key,
    name varchar(10)
);

1.未使用批处理

public class BatchTest {
    public static void main(String[] args) {
        long begin = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String insertSQL = "insert into t_batch values(?, ?)";
            ps = conn.prepareStatement(insertSQL);
            int count = 0;
            // 循环插入1万条记录
            for (int i = 0; i < 10000; i++) {
                ps.setInt(1, i);
                ps.setString(2, "牛马" + i);
                count += ps.executeUpdate();
            }
            System.out.println("插入" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, null);
        }
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - begin) + "ms");
    }
}

2.使用批处理

        开启批处理，在 URL 后面添加参数：【rewriteBatchedStatements=true】。

# cjjdbc.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=root
# 再次执行，以清空表
drop table if exists t_batch;
create table t_batch(
    id bigint primary key,
    name varchar(10)
);
public class BatchTest {
    public static void main(String[] args) {
        long begin = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String insertSQL = "insert into t_batch values(?, ?)";
            ps = conn.prepareStatement(insertSQL);
            int count = 0;
            // 循环插入1万条记录
            for (int i = 0; i < 10000; i++) {
                ps.setInt(1, i);
                ps.setString(2, "牛马" + i);
                // 将SQL语句加入批处理，打包
                ps.addBatch();
                // 每1000条SQL语句执行一次I/O
                if (i % 1000 == 0) {
                    count += ps.executeBatch().length;
                }
            }
            // 循环结束后再次执行批处理，防止数据丢失
            count += ps.executeBatch().length;
            System.out.println("插入" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, null);
        }
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - begin) + "ms");
    }
}

        可以看到，效率大幅提升，其原因是：减少了磁盘 I/O 开销。 

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布