Basic JDBC usage:

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * Test query class for all users
 *
 */
public class QueryAll {
    
    @Test
    public void testQueryAll(){
        Connection conn= null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.Registration driver
            Class.forName("com.mysql.jdbc.Driver");
            //2.Get connection
            String url ="jdbc:mysql://localhost:3306/mybase";
            String username="root";
            String password="xuyiqing";
            conn = DriverManager.getConnection(url,username,password);
            //3.Get execution sql Statement object
            stmt = conn.createStatement();
            //4.To write sql Sentence
            String sql = "select * from user";
            //5.implement sql Sentence
            rs = stmt.executeQuery(sql);
            //6.Process result set
            while(rs.next()){
                System.out.println("user name:"+rs.getString(2)+" password:"+rs.getString("upassword"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }
}

Next, analyze the SQL injection problem:

Database preparation:

CREATE DATABASE mybase;
USE mybase;
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64),
upassword VARCHAR(64)
);
INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
SELECT * FROM users;

JDBC Code:

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

public class TestLogin {

    @Test
    public void testLogin() {
        try {
            login("zhangsan", "123");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void login(String username, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mybase";
        String usern = "root";
        String pwd = "xuyiqing";
        Connection conn = DriverManager.getConnection(url, usern, pwd);
        Statement stmt = conn.createStatement();
        String sql = "select * from users where " + "username='" + username + "'and upassword='" + password + "'";
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            System.out.println("Login successful");
            System.out.println(sql);
        } else {
            System.out.println("Wrong account or password!");
        }
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

When running, output:

Login successful
select * from users where username='zhangsan'and upassword='123'

If so:

            login("zhangsan", "1234");

Output:

Wrong account or password!

However, if you do:

    @Test
    public void testLogin() {
        try {
            login("zhangsan' or 'zhangsan", "666");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

The output is login success! :

Login successful
select * from users where username='zhangsan' or 'zhangsan'and upassword='666'

Obviously, there is no such user and corresponding password in the database, but the login is successful. Here is a simple SQL injection attack

terms of settlement:

1. Separate verification, judge whether the user exists first

2. Preprocess the object using the following methods:

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;

public class TestLogin {

    @Test
    public void testLogin() {
        try {
            login("zhangsan' or 'zhangsan", "666");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void login(String username, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mybase";
        String usern = "root";
        String pwd = "xuyiqing";
        Connection conn = DriverManager.getConnection(url, usern, pwd);
        String sql = "select * from users where username=? and upassword=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            System.out.println("Login successful");
            System.out.println(sql);
        } else {
            System.out.println("Wrong account or password!");
        }
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

At this time, the output account or password is wrong, which solves the SQL injection problem above