Shulou(Shulou.com)06/01 Report--

This article is a detailed introduction to "SQL injection instance analysis of Mybatis". The content is detailed, the steps are clear, and the details are properly handled. I hope this article "SQL injection instance analysis of Mybatis" can help you solve your doubts. Let's go deeper and learn new knowledge together with the ideas of the small editor.

preface

MyBatis3 offers new annotation-based configurations. Mainly in MapperAnnotation Builder, relevant annotations are defined:

public MapperAnnotationBuilder(Configuration configuration, Class type) { ... sqlAnnotationTypes.add(Select.class); sqlAnnotationTypes.add(Insert.class); sqlAnnotationTypes.add(Update.class); sqlAnnotationTypes.add(Delete.class); ...... sqlProviderAnnotationTypes.add(SelectProvider.class); sqlProviderAnnotationTypes.add(InsertProvider.class); sqlProviderAnnotationTypes.add(UpdateProvider.class); sqlProviderAnnotationTypes.add(DeleteProvider.class);}

Add, delete and query occupy most of the business operations. Annotations no longer need to configure complicated xml files. More and more sql interactions are realized through annotations. As you can see from MapperAnnotations Builder, Mybatis provides the following related annotations:

@Select

@Insert

@Update

@Delete

@SelectProvider

@InsertProvider

@UpdateProvider

@DeleteProvider

For example, the following example uses @Select annotation to write SQL directly to complete data query:

@Mapperpublic interface UserMapper { @Select("select * from t_user") List list();}

Advanced annotations such as @SelectProvider allow you to specify a tool class's approach to writing SQL dynamically to address complex business requirements.

Take @SelectProvider as an example to see the specific implementation. It mainly contains two annotation attributes, where type represents the tool class and method represents a method of the tool class, which is used to return specific SQL:

@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface InsertProvider { //used to specify the specified class for obtaining sql statements Class type(); //specify the method in the class to execute the get sql statement String method();}

The method is as follows: define the relevant sql in the getContentByProjectIds method of the ProjectSql class. The definition of sql can be quickly implemented through org.apache.ibatis.jdbc.SQL:

@SelectProvider(type = ProjectSql.class, method = "getContentByProjectIds")

List getContentByProjectIds(List projectIds);

Common Injection Scenarios 2.1 General Notes

In fact, the tag syntax is the same as the corresponding tag in xml configuration (for example,@Select corresponds to the tag), so the injection scenario is similar.

In Mybatis,#mainly replaces placeholders?,$in Precompiled Statements It is a direct SQL splice. Take fuzzy queries like for example:

For example:

Like xml configuration, like fuzzy query directly using #pre-compiled annotation will trigger exceptions, so many times directly use $annotation:

@Select("SELECT id, name, age, email FROM user where name like '${name}'")List queryUserByName(@Param("name") String name);

At this time, if the name front-end user is controllable, it will lead to SQL injection risk.

Picture View sql log, successfully executed 1/0 trigger sql error, indicating successful injection:

Dealing with this type of SQL problem is also very simple, using sql built-in functions for splicing, splicing and then using #pre-compiled way to query. For example, the above example is h3 database, using '||'Splicing and then pre-editing:

@Select("SELECT id, name, age, email FROM user where name like '%'||#{name}||'%'")List queryUserByName(@Param("name") String name);

SQL queries have been made using precompilation:

In addition, similar to Order by, dynamic table names, can not be precompiled in the way, you can use indirect references at the code level to handle.

For range queries in, if you are familiar with mybatis injection, you need to use Mybatis's own loop instruction foreach to solve SQL statement dynamic concatenation. When using annotations, you need to use

Tags to introduce foreach out.

2.2 Dynamic sql 2.2.1 usage

To use dynamic SQL in a plain annotated mapper interface class, use the script element. Similar to XML, it consists mainly of the following elements:

ifchoose (when, otherwise)trim (where, set)foreach

The associated injection scenario is similar to 2.1. It is also not $. In addition, when performing multi-valued queries with the same condition (such as range query in), you can use MyBatis 'own loop instruction foreach to solve the problem of SQL statement dynamic concatenation.

2.2.2 Using Provider Annotations

SQL can be written dynamically by specifying the methods of a tool class using Provider annotations. Take @SelectProvider for example:

First, use @SelectProvider in the mapper to define related methods, where type represents the tool class and method represents a method of the tool class, which is used to return specific SQL. For example:

By passing userIds and name, query the relevant user information, and define the specific SQL content in the getUserInfoBids method of UserInfoSql class:

/** * @param userIds Required * @param name Optional * @return */ @SelectProvider(type = UserInfoSql.class, method = "getUserInfoByids") List getUserInfoByids(List userIds, String name); class UserInfoSql { public String getUserInfoByids(List userIds, String name) { SQL sql = new SQL(); sql.SELECT("id, name, age, email"); sql.FROM("user"); sql.WHERE("id in(" + Joiner.on(',').join(userIds) + ")"); if(StringUtil.isNotBlank(name)){ sql.WHERE("name like '%" + name + "%'"); } sql.ORDER_BY("id desc"); return sql.toString(); } }

SQL queries can be performed by calling specific methods in Controller:

@RequestMapping(value = "/getUserInfoByids") public List getUserInfoByids(String name,@RequestParam List userIds){ List userList = userMapper.getUserInfoByids(userIds,name); return userList; }

Normal requests return corresponding user information:

The previous section is about generating SQL through the tool class org.apache.ibatis.jdbc.SQL provided by MyBatis 3. This class provides methods like select, where, ORDER_BY, etc. to complete SQL generation operations. There is a misconception here that many developers assume that tool classes will be precompiled accordingly.

In fact, the Provider only needs to return a SQL string, the tool class just uses some keywords to format it, and even can directly use StringBuffer to concatenate SQL statements. Also in the above example, List userIds is of type long, but name is of type String, you can try injecting:

Check the related logs, successfully execute 1/0 logic trigger SQL error, which also confirms that the Provider is actually just SQL concatenation, without relevant security processing:

Compared with @Select@, SelectProvider is only different in the way of defining annotations. The former is to directly define sql, and the other is to directly reference sql defined externally. There is no essential difference, so the solution is to use #to pre-compile and process in the corresponding sql scenario, such as like fuzzy query and in range query here:

@SelectProvider(type = UserInfoSql.class, method = "getUserInfoByids") List getUserInfoByids(@Param("userIds")List userIds,@Param("name")String name); class UserInfoSql { public String getUserInfoByids(@Param("userIds")List userIds, @Param("name")String name) { StringBuilder sql = new StringBuilder(128); sql.append("

SELECT id, name, age, email FROM user WHERE (id in"); sql.append("#{item}"); if(StringUtil.isNotBlank(name)){ sql.append("and name like '%'||#{name}||'%')"); } sql.append("ORDER BY id desc"); return sql.toString(); } }

View sql log, at this time use pre-compilation for sql processing, avoid SQL injection risk.

Read here, this article "Mybatis SQL injection instance analysis" article has been introduced, want to master the knowledge of this article also need to practice to understand, if you want to know more about the content of the article, welcome to pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.