注入&事务&索引

一：SQL注入问题（SQL Inject）：由于是SQL拼接的方式处理的SQL，存在安全风险

什么是SQL注入？ “SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，

攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知

情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到

相应的数据信息。”

SQL注入的处理方式：

带参数的SQL处理（PrepareStatement更应该使用的方案）

SQL注入的引入

public static void main(String[] args) throws SQLException {
        try(Connection c = dataSource().getConnection()){
            String keyword = "'随便' OR 1=1";
            String sql = "select * from books where name = %s";
            sql = String.format(sql,keyword);
            try(PreparedStatement ps = c.prepareStatement(sql)){
                System.out.println("    DEBUG:"+ps);
                try(ResultSet rs = ps.executeQuery(sql)){
                    while(rs.next()){
                        System.out.println(rs.getString("name"));
                    }
                }
            }
        }
    }

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

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

public class Demo {
    private static String url = "jdbc:mysql:127.0.0.1:3306/db_11_26?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";

    private static DataSource dataSource() {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl(url);
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        return mysqlDataSource;
    }

    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        try (Connection c = dataSource().getConnection()) {
            System.out.println("请输入要查询的书籍名称>");
            while (scanner.hasNextLine()) {
                String keyword = scanner.nextLine();
                String sql = "select * from books where name like '%s";
                //int keyword = Integer.parseInt(scanner.nextLine());
                //String sql = "select * from books where current_count > %d";

                //参数写法
                //String sql = "select * from books where current_count > ?;无需字符串拼接
                //通过？在SQL中进行占位
                sql = String.format(sql, "%" + keyword + "%");
                System.out.println("    DEBUG:" + sql);
                try (PreparedStatement ps = c.prepareStatement(sql)) {
                    //ps.setString(1,keyword);
                    //通过ps.setXxx(...) 使用真实参数替换
                    //由于Keyword是String类型，所以使用setString
                    //只有一个占位符，所以parameterIndex是1
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("id");
                            String name = rs.getString("name");
                            int total = rs.getInt("total_count");
                            int current = rs.getInt("current_count");
                            System.out.printf("    %3d %10s %3d %3d\n", id, name, total, current);
                        }
                    }
                }
                System.out.println("请输入要查询的书籍名称>");
            }
        }
    }
}

注：现阶段，通过字符串拼接构造SQL应该被完全禁止的，否则容易出现SQL注入。

PrepareStatement的参数写法：SQL:"select*from books where current_count > ?"('?':参数占位符parameter placeholder）

ps.setInt(要替换第几个参数，要求从1开始算     int类型的要替换的值）

ps.setInt(1,1000)  1000是SQL中真正执行的参数 - ...where current_count > 1000

ps.setString(1,"...")

二：事务（Transaction）（从使用角度）

 1）为什么需要事务？2）SQL中如何使用事务 3）JDBC中如何使用事务

事务：数据库的事务是指一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败。对应一条基本的业务动作，组成的一条或者多条SQL形成的概念。

一致性是指执行事务前后的状态要一致，也称数据一致性

一个业务动作-事务-多条SQL

借书-借书事务-查阅书籍存量+修改书记存量+（系统执行出现bug，导致后面的SQL无法执行，无法查询红楼梦的借阅记录）添加借阅记录

还书-还书事务-修改书籍存量+修改借阅记录

数据的一致性（consistency)：DBMS用户根据其业务场景，对数据提出的一系列业务要求

比如：图书管理系统中，任取books的一本书（book）要求：book.存量>0 ->book.存量 <= book.总量-> book.总量-book.存量 <->被借出的书的量=count（借阅记录中（未归还的）当前书）

           银行转账系统中 账户A->账户B转账 要求：无论是否成功，A.账户余额+B.账户余额是一个恒定值

           无头双向链表 ，任取链表中的节点（除了头节点和尾节点） 要求 节点.prev != null -> 节点.next!=null ->节点.prev.next = 节点  ->节点.next.prev = 节点

为了让过程中一致性破坏不影响数据整体的一致性，所以，需要事务概念来处理

事务的引入：避免风险

    风险：（1）SQL执行过程出现异常，导致SQL执行不下去

         异常的原因：1）硬件原因：JDBC程序所在的电脑死机了，MySQL服务器程序所在的电脑死机了，连接的网络中断了

                              2）软件原因：后边SQL执行异常（比如SQL写错了），程序遇到软件异常（NPE、AIE）

              （2）当多个会话（Session）同时动用一份数据，造成互相干扰

public static void main(String[] args) throws SQLException {
        try (Connection c = dataSource().getConnection()) {
            // 修改书籍存量
            String sql1 = "update books set current_count = current_count - 1 where bid = 7";
            try (java.sql.PreparedStatement ps = c.prepareStatement(sql1)) {
                ps.executeUpdate();
            }
            int a = 1 / 0;  // 一定会抛一个 除0 异常，模拟执行完第一条SQL之后，故障了
            // 添加借阅记录
            String sql2 = "insert into records (uid, bid, borrowed_at) values (1, 7, '2021-11-29 10:11:13')";
            try (PreparedStatement ps = c.prepareStatement(sql2)) {
                ps.executeUpdate();
            }
        }
    }

public static void main(String[] args) throws Exception {
        try (Connection c = dataSource().getConnection()) {
            // 查询书籍存量
            String sql0 = "select * from books where bid = 7";
            try (PreparedStatement ps = c.prepareStatement(sql0)) {
                ps.executeQuery();
            }
            // 当存量 == 0，应该停止动作

            Scanner scanner = new Scanner(System.in);
            scanner.nextLine(); // 等待输入一个内容，为了模拟同时的情况

            // 修改书籍存量
            String sql1 = "update books set current_count = current_count - 1 where bid = 7";
            try (PreparedStatement ps = c.prepareStatement(sql1)) {
                ps.executeUpdate();
            }

            // 添加借阅记录
            String sql2 = "insert into records (uid, bid, borrowed_at) values (1, 7, '2021-11-29 10:11:13')";
            try (PreparedStatement ps = c.prepareStatement(sql2)) {
                ps.executeUpdate();
            }
        }
    }

如何使用事务（Workbench及可以执行SQL的客户端）

    核心：如何让DBMS知道我们要执行的哪些SQL应该被当成一个整体（事务）来对待

    start transaction:开始事务，告诉DBMS，接下来之后的所有SQL应该被当成一个整体（事务）来对待

    commit：以成功的状态结束事务（提交事务），告诉DBMS，这组SQL已经全部告诉DBMS了，再接下来就是下一组事务。

在JDBC中执行事务，默认情况，每条SQL都当成一个独立的事务对待，自动进行提交。

    c.setAutoCommit(false) //关闭自动提交功能

回滚（rollback）：c.rollback();为了保证在应用程序、数据库或系统出现错误后，数据库能够被还原，以保证数据库的完整性，所以需要进行回滚。在事务提交之前将数据库数据恢复到事务修改之前数据库数据状态。

三：索引（Index）

1）为什么需要索引？2）MySQLWorkbench添加索引 3）使用索引

索引-优化部分 有索引，查询快，否则查询慢。索引能够让我们避免全表扫描去查找数据，提升检索效率。主键、唯一键等只要是能让数据具备一定区分性的字段都能成为索引

private static Connection connection;
    static {
        try {
            connection = dataSource().getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static Random random = new Random();
    // 全小写字母
    private static String generateRandomString(int length) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < length; i++) {
            char c = (char) (random.nextInt(26) + 'a');
            sb.append(c);
        }

        return sb.toString();
    }

    private static void insertStudent(String name) throws SQLException {
        String sql = "insert into students (name) values (?)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, name);

            ps.executeUpdate();
        }
    }

    // sufaddoksbatyiveqgbuukrctelmll
    // bvfrvxtmgnooyagqxioihvrjjjupog
    public static void main(String[] args) throws SQLException {
        for (int i = 0; i < 500000; i++) {
            String name = generateRandomString(30);
            insertStudent(name);

            // SQL 插入，每 1000 条，提交一次，目的提升插入速度
            if (i % 1000 == 999) {
                connection.commit();
            }
        }
    }