20220606_JDBC_API预编译PrepareStatement作用详细测试

create table test_for_prepare
(
    id      int primary key auto_increment,
    user_name    varchar(10),
    password varchar(32)
);
insert into test_for_prepare (user_name, password) values
('马大帅','1234'),('范德彪','5678');

select *
from test_for_prepare;

select *
from test_for_prepare where user_name = '马大帅' and password = '1234';

    @Test
    public void testSqlInject() throws Exception {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;
        Connection con = DriverManager.getConnection(url,uName,pWord);

//        String name = "马大帅";
//        String pwd = "1234";
        String name = "Not_exist";
        String pwd = "' or '1' = '1";
         String sql_st = "select * from test_for_prepare where user_name = '"+name+"' and password = '"+pwd+"'";
        /**
         * 用DQL语句模拟登录，当输入的名字和密码找得到数据，代表存在该条，登录成功
         * 如果不使用prepareStatement预编译，则只要密码写成' or '1' = '1
         * 就可以使得传给数据库的SQL语句变成
         * select *
         *  from test_for_prepare
         *  where user_name = '瞎写' and password = '' or '1' = '1';
         * 凭借or，则永远可以登录成功了，这就叫SQL注入攻击
         */
        Statement stt = con.createStatement();

        ResultSet rs = stt.executeQuery(sql_st);

        if (rs.next()){
            System.out.println("Login succeed.");
        }else {
            System.out.println("Login failed.");
        }

        rs.close();
        stt.close();
        con.close();
    }

    @Test
    public void testPrepare() throws Exception {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;
        Connection con = DriverManager.getConnection(url,uName,pWord);

//        String name = "马大帅";
//        String pwd = "1234";
        String name = "Not_exist";
        String pwd = "' or '1' = '1";

        String sql_st = "select * from test_for_prepare where user_name = ? and password = ?";

        PreparedStatement ppst = con.prepareStatement(sql_st);
        ppst.setString(1,name);
        ppst.setString(2,pwd);
        /**
         * 当使用prepareStatement之后，sql的String变量就不需要拼接，里头写?即可
         * 用prepare，将?转成传入的值，并且会给传入的值转义，不会发生SQL语义结构上的变化
         * 此时依然 String pwd = "' or '1' = '1"，用prepare后的sql语句为
         * select *
         *  from test_for_prepare
         *  where user_name = '瞎写' and password = '\' or \'1\' = \'1';
         *  password 为 \' or \'1\' = \'1 了，不可能登录成功
         */
        ResultSet rs = ppst.executeQuery();//ppst里已经有sql语句参数了，这里括号一定要空，否则报错

        if (rs.next()){
            System.out.println("Login succeed.");
        }else {
            System.out.println("Login failed.");
        }

        rs.close();
        ppst.close();
        con.close();
    }

先设置数据库日志输出

如果是较老的MySQL版本，具体多老不知，5.7开头的
预编译的数据库my.ini文件添加配置，路径自定义，保存后重启mysql服务

log-output=FILE
general-log=1
general_log_file="C:\MySQL_Script_Log\mysql.log"
slow-query-log=1
slow_query_log_file="C:\MySQL_Script_Log\mysql_slow.log"
long_query_time=2

较新MySQL版本，8.0以上的，需要先在数据库开启日志功能
操作语句如下，路径同样自定义，斜杠要双反斜杠，单正斜杠貌似无效

Show variables like 'slow_query%';
set global slow_query_log_file = 'C:\\log\\mysql-slow.log';
set global slow_query_log = 'ON';

Show variables like 'general_log%';
set global general_log_file = 'C:\\log\\mysql-general.log';
set global general_log = 'ON';

然后测试
用剪切的方式添加或删除开启预编译的连接参数

    @Test
    public void testPrepare2() throws Exception {
        /**
         * 启用预编译首先要在连接中加参数 &useServerPrepStmts=true
         * 撤销参数再测试，查看日志结果差异
         */
        String url = "jdbc:mysql:///my_temp?useSSL=false&useServerPrepStmts=true";
        String uName = "root" ;
        String pWord = "1234" ;
        Connection con = DriverManager.getConnection(url,uName,pWord);

        String name = "马大帅";
        String pwd = "1234";
        String name1 = "范德彪";
        String pwd1 = "5678";


        String sql_st = "select * from test_for_prepare where user_name = ? and password = ?";

        PreparedStatement ppst = con.prepareStatement(sql_st);
        ResultSet rs;

        ppst.setString(1,name);
        ppst.setString(2,pwd);
        rs = ppst.executeQuery();
        if (rs.next()){
            System.out.println("Login succeed.");
        }else {
            System.out.println("Login failed.");
        }
        /**查询两次，更看出预编译和没有预编译的效果，去日志里看，if判断其实可以省略，本例还是不省*/
        ppst.setString(1,name1);
        ppst.setString(2,pwd1);
        rs = ppst.executeQuery();
        if (rs.next()){
            System.out.println("Login succeed.");
        }else {
            System.out.println("Login failed.");
        }

        rs.close();
        ppst.close();
        con.close();
    }

开预编译和不开的日志如图，编号，其实是id，编号17和18的两次连接；
编号17无预编译的query了两次sql语句，这就是检查语法，编译sql，执行sql各两次；
编号18的有预编译，在prepare时就检查了语法，编译了sql，这一次就管后面的两次或N次执行了；
所以execute直接两次；

预编译因此在性能上实现优化；