20220606

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+"'";
        
        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);
        
        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_Logmysql.log"
slow-query-log=1
slow_query_log_file="C:MySQL_Script_Logmysql_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 {
        
        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.");
        }
        
        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直接两次；

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