This is the sixth day of my participation in the Novembermore Challenge.The final text challenge in 2021

preface

SQL injection is a way to attack the database by injecting SQL statements into the input. Today, using the Java language, we will analyze the problems related to SQL injection.

What is SQL injection

SQL injection is refers to the legitimacy of the web application to user input data without judgment or filtering is lax, the attacker can be defined in advance in the web application at the end of the query statement to add extra SQL statements, the administrator unwittingly illegal operation, in order to realize deceived any query of the database server to perform unauthorized, Thus further get the corresponding data information.

Second, simulate SQL injection

Let’s start with a simple database and a user table:

create database test;
use database test;
create table user(username varchar(20), password(20));
Copy the code

We insert two data into the table:

insert into user values('zack'.'123456');
insert into user values('rudy'.'123456');
Copy the code

Let’s look at a simple Java program:

package com.zack.sql;

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

public class Sql {
	// The url of the link
	private static String url = "jdbc:mysql:///all_test? serverTimezone=GMT";
	/ / user name
	private static String user = "root";
	/ / password
	private static String password = "123456";
	// For input
	Scanner sc = new Scanner(System.in);
	
	public static void main(String[] args) throws Exception {
		// Load the driver
		Class.forName("com.mysql.jdbc.Driver");
		// Get the connection
		Connection conn = DriverManager.getConnection(url, user, password);
		// Get the statment object
		Statement stat = conn.createStatement();
		
		// Ask the user to enter a user name and password
		System.out.println(Please enter your user name:);
		String name = sc.next();
		System.out.println("Please enter your password:");
		String pwd = sc.next();		
		
		Code1. Query whether the user exists in the database according to the username and password entered by the user
		ResultSet set = stat.executeQuery("select * from user where username = '" + name + "' and password = '" + pwd + "'");

		// Print the matched data
		while(set.next()) {
			String username = set.getString("username");
			String password = set.getString("password");
			System.out.println("name:" + username + ", pwd:"+ password); }}}Copy the code

We see code1, assuming we enter the following:

zack
123456
Copy the code

This happens to match our database, so the SQL statement executed in Code1 is as follows:

select * from user where username = 'zack' and password = '123456';
Copy the code

We originally assumed that if we entered mismatched data, we would not be able to find something in the database, but we typed as we liked:

zack' or '1'='1
111
Copy the code

At this point, the following statement is executed in CODE1:

select * from user where username = 'zack' or '1'='1' and password = '123456';
Copy the code

Where ‘1’ = ‘1’ is always true, so the SQL statement does not determine whether the password is correct, thus completing the effect of SQL injection attack.

How to prevent SQL injection

The way to prevent SQL injection is also very simple. In JDBC, there is a pre-compiled object for SQL statements, which we can implement through the PrepareStatement class. Suppose we still want to perform the query, execute the following statement:

String sql ="select * from user where username = ? and password = ?" ;Copy the code

Here we use “?” Represents the value of the field. Then we create a PrepareStatement object, which is slightly different from Statement:

// The SQL statement is passed in when the PrepareStatement object is created
PrepareStatement preStat = conn.prepareStatement(sql);
Copy the code

Here the SQL Statement is passed in when the PrepareStatement object is created, whereas the Statement is passed in when the query operation is executed. Since we have passed in the SQL statement, we do not need to pass in the SQL statement when executing the query, but take one more step to match the parameters:

// Replace the value of name with the first SQL statement?
preStet.setString(1, name);
// Replace the value of name with the second SQL statement?
preStat.setString(2, pwd);
Copy the code

Where name and PWD are the string variables we entered. Now we can perform the query:

ResultSet set = preStat.executeQuery();
while(set.next()) {
	String username = set.getString("username");
	String password = set.getString("password");
	System.out.println("name:" + username + ", pwd:" + password);
}
Copy the code

This is the same as before, except that no SQL statement is passed in. The complete code is as follows:

package com.zack.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Sql {
	
	private static String url = "jdbc:mysql:///all_test? serverTimezone=GMT";
	private static String user = "root";
	private static String password = "123456";
	private static Scanner sc = new Scanner(System.in);
	private static String sql = "select * from user where username = ? and password = ?";
	
	public static void main(String[] args) throws Exception {
		// Database operation
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection(url, user, password);
		PreparedStatement stat = conn.prepareStatement(sql);
		
		// Ask the user to enter a user name and password
		System.out.println(Please enter your user name:);
		String name = sc.next();
		System.out.println("Please enter your password:");
		String pwd = sc.next();		
		
		// Query whether there is a changed user in the database according to the user name and password
		stat.setString(1, name);
		stat.setString(2, pwd);
		ResultSet set = stat.executeQuery();
		while(set.next()) {
			String username = set.getString("username");
			String password = set.getString("password");
			System.out.println("name:" + username + ", pwd:"+ password); }}}Copy the code

Four,

SQL injection is a popular way to attack the database in the early days, but it is rarely used directly for database operations using JDBC, let alone directly using THE WAY of SQL statement concatenation. So in most cases SQL injection is not valid, may be in an older url is still valid, you can try the following.