sql injection attack

1. Principle
Because the SQL statement parameters are sent from the foreground to the background, and the background SQL statements are spliced, if the database keywords appear in the splicing process, the semantics of the spliced SQL statements may be changed, so as to perform some other operations. This phenomenon is called SQL injection attack.
2. Examples
//Query database name = "lili" -- idea transfers values to the database and only recognizes "values"
rs = stat.executeQuery("select * from user where name='" + name + "'and password='" + password + "'");
PrepareStatement (avoid sql injection attack)
It is a precompiled transmitter, which first sends the sql trunk statement part to the database service, and the parameter location is set with? To reserve. After the trunk statement is sent to the database server, it will become a machine code, which cannot be changed. Send the parameter in plain text. If the parameter contains keywords, it will also be considered as a text content.
Code implementation:

public class TestDemo {
    public static void main(String[] args) {
        //1. Enter the user name and password from the keyboard
        Scanner sc=new Scanner(System.in);
        System.out.println("Please enter your name:");
        String name = sc.nextLine();
        System.out.println("Please input a password:");
        String password=sc.nextLine();
        //2. Call the method to connect to the database to verify login
        testLogin(name,password);
    }
    //3. Verification method to solve sql injection attack
    private static void testLogin(String name, String password) {
        //4. Initialize variables
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        //5. Connect to the database
        try {
            conn=JdbcUtil.getConnection();//JdbcUtil ditto
            ps=conn.prepareStatement("select * from user where name=? and password=?");//Send the trunk statement of sql, and the parameters are reserved with question marks
            //6. Get the entered information setstring (parameterindex (a column of the table), string x (parameter of the method))
            ps.setString(1,name);
            ps.setString(2,password);
            //implement
            rs=ps.executeQuery();
            //7. Judge whether the information exists in the database,
//Rs.next (Boolean): true indicates that the following table moves and finds data

            if(rs.next()){
                System.out.println("Login successful");
            }else{
                System.out.println("Login failed");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.Close(conn,ps,rs);
        }
    }
}

3. Only one statement can be executed at a time
Batch statement: multiple sql statements can be executed in JDBC at a time. This operation can be regarded as an operation of batch mechanism
(1) Implementation of batch processing mechanism: Statement batch processing
statement.addBatch(String sql);// Adds an SQL statement to the current batch
statement.executeBache(); Execute batch
statement.clearBatch(); Empty batch
Code implementation:

conn=JdbcUtil.getConnection();
stat=conn.createStatement();
//Add sql statement to batch
stat.addBatch("create table t1(id int,name varchar(10))");
stat.addBatch("insert into t1 values(1,'Sandy')");
stat.addBatch("insert into t1 values(2,'Patrick Star')");
stat.addBatch("insert into t1 values(3,'Spongebob')");
stat.addBatch("insert into t1 values(4,'Crab boss')");
//Notify the database server to perform batch operations
stat.executeBatch();

Advantages: you can execute sql with different semantics
Disadvantages: 1. sql injection cannot be avoided and there is no precompiling
2. You need to send a complete sql statement
3. Send complete sql statements even if the semantics are the same

(2) PrepareStatement batch (with precompiling)
ps.addBatch(String sql);// Adds an SQL statement to the current batch
ps.executeBache(); Execute batch
ps.clearBatch(); Empty batch
Code implementation:

public static void main(String[] args) {
    Connection conn=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try{
        conn=JdbcUtil.getConnection();
        ps=conn.prepareStatement("insert into t1 values(?,?)");
        for(int i=0;i<=10;i++){
            ps.setInt(1,i);
            ps.setString(2,"name"+i);
            //Add to batch
            ps.addBatch();
            ps.executeBatch();
        }
        ps.clearBatch();
        //Output results
        System.out.println("Output complete");
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }finally {
        JdbcUtil.Close(conn,ps,rs);
    }
}

Advantages: 1. It has precompiled function, which can avoid sql injection. 2
2. Because the sql backbone statements are sent to the database service, there is no need to send them repeatedly, and the execution efficiency is high
3. Only sending parameters repeatedly without sending complete SQL statements has high execution efficiency
Disadvantages: sql statements with different semantics cannot be executed