Learn about SQL injection and how to fix it

Recommendation (free): sql tutorial

##SQL injection refers to the intrusion of user-entered data by web applications. There is no judgment on legality or lax filtering. Attackers can add additional SQL statements at the end of the predefined query statements in the web application to achieve illegal operations without the administrator's knowledge, thereby deceiving the database. The server performs unauthorized arbitrary queries to further obtain corresponding data information.

1. SQL injection case

Simulate a SQL injection case of user login. The user enters the user name and password on the console, and then uses Statement string concatenation. Implement user login.

1.1 First create the user table and data in the database

-- 创建一张用户表
CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(20),
  `password` VARCHAR(50),
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 插入数据
INSERT INTO  users(username,`password`) VALUES('张飞','123321'),('赵云','qazxsw'),('诸葛亮','123Qwe');
INSERT INTO  users(username,`password`) VALUES('曹操','741258'),('刘备','plmokn'),('孙权','!@#$%^');


-- 查看数据
SELECT  * FROM users;

##1.2 Write a login Program

import java.sql.*;
import java.util.Scanner;


public class TestSQLIn {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=UTF-8";
        Connection conn = DriverManager.getConnection(url,"root","123456");
        //System.out.println(conn);
        // 获取语句执行平台对象 Statement
        Statement smt = conn.createStatement();


        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String userName = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();


        String sql = "select  * from users where username = '" + userName + "'  and  password = '" + password +"'";
        //打印出SQL
        System.out.println(sql);
        ResultSet resultSet = smt.executeQuery(sql);
        if(resultSet.next()){
            System.out.println("登录成功！！！");
        }else{
            System.out.println("用户名或密码错误，请重新输入！！！");
        }


        resultSet.close();
        smt.close();
        conn.close();


    }


}

1.3 Normal login

After entering the correct user name and password, it will prompt "Login successful"

1.4 Login failure

When the user name or password is entered incorrectly, the prompt "User name or password is incorrect, please re-enter"

1.5 Simulating SQL injection

The concatenated string contains or '1'='1', which is a constant condition, so even if the previous user and password do not exist, it will All records are taken out, so the prompt "Login successful"

1.6 SQL syntax error

Using the splicing method, SQL will also appear Syntax errors and other errors, such as

2. Solution

Using the Statement method, the user can change the original text through string splicing. The true meaning of SQL leads to the risk of SQL injection. To solve SQL injection, you can use the preprocessing object PreparedStatement instead of Statement for processing.

1.1 Write a new program

import java.sql.*;
import java.util.Scanner;


public class TestSQLIn {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=UTF-8";
        Connection conn = DriverManager.getConnection(url,"root","123456");
        //System.out.println(conn);
        // 获取语句执行平台对象 Statement
        // Statement smt = conn.createStatement();


        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String userName = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();


        String sql = "select  * from users where username = ? and  password = ? ";
        // System.out.println(sql);
        // ResultSet resultSet = smt.executeQuery(sql);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,userName);
        preparedStatement.setString(2,password);


        ResultSet  resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            System.out.println("登录成功！！！");
        }else{
            System.out.println("用户名或密码错误，请重新输入！！！");
        }




        preparedStatement.close();
        resultSet.close();
        // smt.close();
        conn.close();


    }


}

2.2 Log in normally

2.3 Incorrect username and password

When the username or password is entered incorrectly, it will prompt "The username or password is incorrect, please re-enter"

2.4 Simulate SQL injection

According to the previous situation, write SQL injection. After the test, SQL injection will no longer occur.

2.5 Simulating SQL syntax error

After using the preprocessing class, inputting content with single quotes or double quotes will not work SQL syntax errors will appear again

3. Summary

The main differences between Statement and PreparedStatement are as follows:

Statement is used to execute static SQL statements. During execution, a prepared SQL statement must be specified.
PrepareStatement is a precompiled SQL statement object. The statement can contain the dynamic parameter "?", and the parameter value can be dynamically set for "?" during execution
PrepareStatement can reduce the number of compilations and improve database performance

The above is the detailed content of Learn about SQL injection and how to fix it. For more information, please follow other related articles on the PHP Chinese website!