Simple understanding of mybatis how to achieve SQL injection prevention

Time:2020-10-27

In this article, we need to learn how to use mybatis code to learn more about this article

Mybatis framework is used in daily development. For example, there is a question in the interview: the difference between $and ා. The difference between them is that the use of ා can prevent SQL injection. Today, let’s take a look at how it implements SQL injection.

What is SQL injection

Before discussing how to implement it, let’s first understand what SQL injection is. We have a simple query operation: query a user’s information by ID. Its SQL statement should look like this:select * from user where id =。 We fill in the ID according to the incoming condition to query.

If normal operation, pass in a normal ID, such as 2, then this statement becomesselect * from user where id =2。 This statement works and works as expected.

However, if the parameter passed in becomes’ or 1 = 1, the statement becomesselect * from user where id = '' or 1=1。 Let’s think about the result of this statement? It will query all the data in our user table, which is obviously a big mistake. This is SQL injection.

How does mybatis prevent SQL injection

As mentioned at the beginning, you can use ා, which is written as follows:


			<select resultMap="testUser">
			SELECT * FROM user where id = #{id}
	</select>

In mybatis, another way to write a query is to use $, which is written as follows:


			<select resultMap="testUser">
			select * from user where id = ${id}
	</select>

When we continue to call these two methods from outside, we find that if we pass in a safe parameter, the results of the two methods are not different. If we pass in unsafe parameters, the first method using ා, can not query the results(select * from user where id = '' or 1=1), but this parameter gets all the results under the second one, which is $.

And if we print the SQL, we will find that when adding ා, the SQL executed to the database is:select * from user where id = ' \'\' or 1=1 'It will add a layer of quotation marks around our parameters. When using $, its execution SQL isselect * from user where id = '' or 1=1

Is it OK to discard the dollar

We can also use ා to complete the function of ා, and it is dangerous to use ා, so we can not use $in the future.

No, it’s just that there will be problems in our scenario, but it can still play an irreplaceable role in some dynamic query scenarios, such as dynamically modifying the table nameselect * from ${table} where id = #{id}。 We can change the query table dynamically when the returned information is consistent. This is also where mybatis is is dynamic and powerful.

How to implement SQL injection without mybatis

In fact, mybatis also connects to the database through JDBC. If we look at the use of JDBC, we can get this reason.

#The Preparedstatement is used for preprocessing, and then the place holder is set by the way of set, while $is directly queried through the statement, and directly spliced when there are parameters.

So we can use JDBC to implement SQL injection.

Take a look at the code for these two:

public static void statement(Connection connection) {
			System.out.println("statement-----");
			String selectSql = "select * from user";
			//It is equivalent to using $, which is used in mybatis. After getting the parameter, it is directly spliced
			String unsafeSql = "select * from user where id = '' or 1=1;";
			Statement statement = null;
			try {
			statement = connection.createStatement();
			} catch (SQLException e) {
			e.printStackTrace();
			}
			try {
			ResultSet resultSet = statement.executeQuery(selectSql);
			print(resultSet);
			} catch (SQLException e) {
			e.printStackTrace();
			}
			System.out.println("---****---");
			try {
			ResultSet resultSet = statement.executeQuery(unsafeSql);
			print(resultSet);
			} catch (SQLException e) {
			e.printStackTrace();
			}
			}

			public static void preparedStatement(Connection connection) {
			System.out.println("preparedStatement-----");
			String selectSql = "select * from user;";
			//Equivalent to ා in mybatis, preprocess the SQL to be executed, set the place holder, and then set the parameters
			String safeSql = "select * from user where id =?;";
			PreparedStatement preparedStatement = null;
			try {
			preparedStatement = connection.prepareStatement(selectSql);
			ResultSet resultSet = preparedStatement.executeQuery();
			print(resultSet);
			} catch (SQLException e) {
			e.printStackTrace();
			}
			System.out.println("---****---");
			try {
			preparedStatement = connection.prepareStatement(safeSql);
			preparedStatement.setString(1," '' or 1 = 1 ");
			ResultSet resultSet = preparedStatement.executeQuery();
			print(resultSet);
			} catch (SQLException e) {
			e.printStackTrace();
			}
			}

			public static void print(ResultSet resultSet) throws SQLException {
			while (resultSet.next()) {
			System.out.print(resultSet.getString(1) + ", ");
			System.out.print(resultSet.getString("name") + ", ");
			System.out.println(resultSet.getString(3));
			}
	}

summary

  • The use of ා in mybatis can prevent SQL injection, while $cannot prevent SQL injection
  • The principle of mybatis to implement SQL injection is to call Preparedstatement in JDBC for preprocessing.

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.