How to avoid SQL injection vulnerabilities

Time:2021-10-14

Introduction: This article will explain the underlying principles and causes of SQL coding defects that still occur frequently in the development process. And in the form of several common vulnerabilities, remind technical students to pay attention to relevant problems. Finally, solutions or mitigation solutions will be provided according to the principle.

How to avoid SQL injection vulnerabilities

Author | Alibaba cloud security team
Source: Ali technical official account
‍‍‍‍‍‍‍‍

I. Preface

This article will explain the underlying principles and causes of SQL coding defects that still occur frequently in the development process. And in the form of several common vulnerabilities, remind technical students to pay attention to relevant problems. Finally, solutions or mitigation solutions will be provided according to the principle.

II. Principle and causes of SQL injection vulnerability

SQL injection vulnerability is basically due to the wrong execution of external input as SQL code. At present, the best solution is precompiled.

During the execution of SQL statements, the following three basic steps are required:

  • Code semantic analysis
  • Develop an implementation plan
  • Get return results

An SQL statement consists of code and data, such as:

SELECT id, name, phone FROM userTable WHERE name = ‘xiaoming’;

Select id, name, phone from usertable where name = is the code, ‘Xiaoming’ is the data.

Precompiling, taking mybatis as an example, analyzes the semantics with placeholders in advance:

For example, select id, name, phone from usertable where id = #{name};

Then pass the data ‘Xiaoming’ into the placeholder. In this way, the code semantic analysis stage will not be mistaken for a part of the code.

In the earliest days, developers explicitly used JDBC to create their own connections and execute SQL statements. In this case, if external controllable data is spliced into SQL statements without sufficient filtering, a vulnerability will be generated. This situation is rare in the normal business development process. According to the company’s regulations, the ORM framework must be used to execute SQL under no special circumstances.

However, in some projects, JDBC will still be used to write some tool scripts, such as datamerge.java and databaseclean.java. With the flexibility of JDBC, these scripts can be used to perform batch database operations.

Such code should not appear in the online version to avoid being called externally due to various situations.

III. direct use of mybatis

1. Error prone points

At present, most of the platform code is based on mybatis to handle the interaction between the persistence layer and the database. There are two kinds of placeholders {} and #{} for the incoming data of mybatis. {} and #{}. {} can be understood as string splicing before semantic analysis. The parameters passed in are passed in intact.

for instance

SELECT id, name, phone FROM userTable WHERE name = ‘${name}’;
After passing in name = Xiaoming, it is equivalent to
SELECT id, name, phone FROM userTable WHERE name = ‘xiaoming’;

In practical application

SELECT id, name, phone FROM userTable WHERE ${col} = ‘xiaoming’;
Pass in col = “name”, which is equivalent to
SELECT id, name, phone FROM userTable WHERE name = ‘xiaoming’;

As mentioned in the introduction to precompiling principles, there is no injection problem when using #{} placeholders. However, some business scenarios cannot be used #{} directly.

For example, in order by syntax

If you write select id, name, phone from usertable order by #{}, An error will be reported during execution. Because the content after order by is a column name, which is part of the code semantics. If it is not determined in the semantic analysis section, it is equivalent to executing select id, name, phone from usertable order by. There must be grammatical mistakes.

Another example is in the like scenario

SELECT id, name, phone FROM userTable WHERE name like ‘%#{name}%’;

{} will not be parsed, resulting in an error.

In grammar and between grammar are the same, so how to solve this kind of problem?

2 correct writing

Use ${} in order by (group by) statements

1. Judgment of service conditions

How to avoid SQL injection vulnerabilities

2. Use the global filtering mechanism to restrict the variable content after order by to only numbers, letters and underscores.

If regular filtering is used:

keyword = keyword.replaceAll(“1“, “”);
It should be noted here that the white list is required for filtering, and the blacklist cannot be used. The blacklist cannot solve the injection problem.

Like statement

Since the keywords in like need to be wrapped in two% symbols, concat function can be used for splicing.

How to avoid SQL injection vulnerabilities

Be careful not to use concat (‘%’, ‘${stuname}’, ‘%’), as there is still a vulnerability. In other words, it is wrong to use the $symbol. It is safe to use the # symbol.

In statement

Similar to the like statement, direct use #{} will result in an error. The common error writing method is:

tenant_id in (${tenantIds})

The correct wording is:

How to avoid SQL injection vulnerabilities

IV. security of mybatis generator

Under the pressure of heavy crud code, developers slowly began to automatically generate mapper, POJO, Dao and other files through mybatis generator, idea mybatis generator plug-in, general mapper and mybatis generator plus.

These tools can automatically generate the files required by crud, but if they are not used properly, SQL injection vulnerabilities will be generated automatically. Let’s take the most commonly used org.mybatis.generator as an example to explain possible problems.

1. Dynamic statement support

Mybatis generator provides functions to help users connect various SQL conditions, such as like syntax for multiple parameters and comparison syntax for multiple parameters. In order to ensure the simplicity of use, some semantic codes need to be spliced into SQL statements. If the developer uses it improperly, the external input is also passed into the {} placeholder. There will be loopholes.

2. Targetruntime parameter configuration

When configuring the generator, there is a targetruntime attribute in the configuration file generator-rds.xml, which defaults to mybatis3. In this case, the dynamic statement support of mybatis will be started, and the functions of enableselectbyexample, enabledeletebyexample, enablecountbyexample and enableupdatebyexample will be started.

Taking enableselectbyexample as an example, the following dynamic modules will be substituted into the XML Mapping File:

How to avoid SQL injection vulnerabilities

Developers can add the where condition by including the module, but improper use will lead to SQL injection vulnerabilities:

How to avoid SQL injection vulnerabilities

And add functions with custom parameters:

public Criteria addKeywordTo(String keyword) {
  StringBuilder sb = new StringBuilder();
  sb.append("(display_name like '%" + keyword + "%' or ");
  sb.append("org like '" + keyword + "%' or ");
  sb.append("status like '%" + keyword + "%' or ");
  sb.append("id like '" + keyword + "%') ");
  addCriterion(sb.toString());
  return (Criteria) this;
}

The purpose is to realize simultaneous display_ Like operations of name, org, status and ID. Addcriterion is the function of mybatis generator:

protected void addCriterion(String condition) {
    if (condition == null) {
        throw new RuntimeException("Value for condition cannot be null");
    }
    criteria.add(new Criterion(condition));
}

The mistake here is that addCriterion itself provides support for multiple conditions, but developers think that they need to combine multiple conditions and simultaneous interpreting the addCriterion method. As with the code in the case, only one parameter is finally passed in addcriterion. To execute example_ Where_ In Clause statement:

How to avoid SQL injection vulnerabilities

In other words, developers directly substituted their spliced SQL statements into ${criterion. Condition}, resulting in vulnerabilities.
According to the document of mybatis generator, the correct writing method should be:

public void addKeywordTo(String keyword, UserExample userExample) {
  userExample.or().andDisplayNameLike("%" + keyword + "%");
  userExample.or().andOrgLike(keyword + "%");
  userExample.or().andStatusLike("%" + keyword + "%");
  userExample.or().andIdLike("%" + keyword + "%");
}

The or method is responsible for creating criteria, and the logic triggered is

How to avoid SQL injection vulnerabilities

${criterion. Condition} is replaced by like without single quotation marks. Like is used as semantic code and spliced into SQL statements before semantic analysis, while “%” + keyword + “%” will be added to precompiled #{criterion. Value} as data to avoid injection.

Similarly, safe usage of in syntax is also provided:

How to avoid SQL injection vulnerabilities

Safe use of beetween:

example.or()
  .andField6Between(3, 7);

The order by statement generated by mybatis generator by default is also spliced directly using ${}:

How to avoid SQL injection vulnerabilities

Without additional filtering of the incoming parameters, injection problems can result.

3 order by

In addition to the SQL statements written by myself, the order by statements generated by mybatis generator by default are spliced directly using ${}:

How to avoid SQL injection vulnerabilities

Without additional filtering of the incoming parameters, injection problems can result.

PS: in the actual minesweeping process, it is found that many statements automatically generate order by syntax, but the optional parameter is not passed in during the upper layer call. In this case, the redundant order by syntax should be removed.

4 other plug-ins

The security defects between plug-ins are not the same. Here are some common plug-ins.

idea-mybatis-generator

This is the plug-in of idea, which can automatically generate the files required in crud from the IDE level during the development process. When using this plug-in, there are also some default security risks to be noted.

1) Custom order by processing

Like \ in \ between can be used with reference to official documents without potential safety hazards.

However, the plug-in has no built-in order by processing and needs to be written by itself. When writing, refer to case2

2) Before the default if condition, you need to judge whether it is empty

The default syntax generated by the plug-in is as follows:

How to avoid SQL injection vulnerabilities

When the ID parameter is null, the logic under the if tag will not be added to the SQL statement, which may lead to DOS, permission bypass and other vulnerabilities. Therefore, before the parameter is passed into the query statement, you need to confirm that it is not empty.

com.baomidou.mybatis-plus

When passing parameters to the apply method, {}

The principle of the built-in last method is that it is directly spliced to the end of the SQL statement, and there is an injection vulnerability.

V. other ORM frameworks

1 Hibernate

The full name of ORM is object relational mapping. In short, it is to map tables in the database into Java objects. This kind of object with only attributes and no business logic is also called POJO (plain ordinary Java object) object.

Hibernate is the first widely used ORM framework. It manages database connections through XML, provides a full table mapping model, and has a high degree of encapsulation. After configuring the mapping file and database link file, hibernate can operate the database through the session object. Developers do not need to contact SQL statements, but only need to write HQL statements.

Hibernate is often used with struts and spring, which is the classic SSH framework in the Java world.

Compared with SQL, HQL has many more syntax restrictions:

You cannot query unmapped tables. You can use union syntax only when the relationship between models is clear.

Table names and column names are case sensitive.

No *, #, –.

There is no delay function.

Therefore, HQL injection utilization is much more difficult than SQL injection. From the perspective of code audit, it is consistent with ordinary SQL injection:

Splicing can lead to injection vulnerabilities:

How to avoid SQL injection vulnerabilities

Placeholders and named parameters can be used to prevent SQL statements, which are precompiled in nature.

How to avoid SQL injection vulnerabilities

How to avoid SQL injection vulnerabilities

Hibernate has many shortcomings in its use:

The full table mapping is not flexible, and all fields need to be sent during update, which affects the running efficiency of the program.

Poor support for complex queries.

Poor support for stored procedures.

HQL has poor performance and cannot be optimized according to SQL.

When auditing hibernate related injection, you can quickly locate the location of SQL operations by global search createquery.

2 JPA

JPA, fully known as Java persistence API, is a data persistence specification provided by Java EE, which allows developers to persist an object to the database through XML or annotation.

It mainly includes three aspects:

1. ORM maps metadata to describe the corresponding relationship between objects and data tables through XML or annotations. The framework can automatically save the data in the object to the database.

Common annotations are: @ entity, @ table, @ column, @ transient

2. Data operation API and built-in interface facilitate crud operation on a data table and save developers’ time in writing SQL.

Common methods are: entitymanager. Merge (T);

3. Jpql provides an object-oriented rather than database oriented query language to decouple the program from the database and SQL.

JPA is a set of specifications, which hibernate implements.

How to avoid SQL injection vulnerabilities

In the spring framework, a simple version of JPA implementation – Spirng data JPA is provided. If you write Dao layer interface according to the agreed method naming rules, you can access and operate the database without writing interface implementation. At the same time, it provides many functions other than crud, such as paging, sorting, complex query and so on. It is easier to use, but the underlying layer is still using hibernate’s JPA implementation.

Like HQL injection, if the splicing method is used to substitute user controllable data into the query statement, it will lead to SQL injection.

Secure queries should use precompiled techniques.

The precompiled writing method of spring data JPA is:

String getUser = "SELECT username FROM users WHERE id = ?";
Query query = em.createNativeQuery(getUser);
query.setParameter(1, id);
String username = query.getResultList();

Tip: in fact, hibernate appeared earlier than the JPA specification. After hibernate gradually matured, the Java EE development team invited hibernate core developers to formulate the JPA specification. After that, spring data JPA is further optimized according to the specification. In addition, there are many products for the implementation of JPA specification, such as eclipse’s TopLink (Oracle link).

Vi. summary

After the above introduction, especially the discussion on mybatis error prone points, we can draw the following conclusions:

There are many kinds of persistence layer components.

Developers’ wrong understanding of the use of tools is the main reason for the emergence of vulnerabilities.

Due to the dynamic nature of automatic plug-in generation, automatic discovery of SQL vulnerabilities cannot be simply found by using ${}.

Detailed matching rules must be made according to the global persistence layer component characteristics.

Original link
This article is the original content of Alibaba cloud and cannot be reproduced without permission.


  1. a-zA-Z0-9_\s+

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]