Sorted out some Java architecture, interview materials (micro services, clusters, distributed, middleware, etc.), partners in need can pay attention to the public number [programmer internal point], no routine to get
More choice
- Reeling off nine distributed ID generation methods, the interviewer gets a little confused
- How about the score sheet? You can play him off like this
- 30,000 words summary, the essence of Mysql optimization
- I was forced to learn JAVA crawlers in order not to copy and paste
- The technology department suddenly announced that all JAVA developers would have to know the interface automation testing framework
- Redis 5 kinds of data structure and the corresponding use scenario, plenary interview to bonus points
The introduction
MyBatis is a persistence layer framework between JDBC and Hibernate. MyBatis reduces the pain of handwritten SQL statements, users can use SQL statements flexibly, support advanced mapping. However, MyBatis is not just launched for security issues, there are many developers think that using MyBatis will not exist SQL injection, is it true?
Will there be no SQL injection with MyBatis? The answer is clearly NO. MyBatis is just a persistence layer framework, it doesn’t solve security problems for you. Of course, if you follow the specification and develop the way the framework recommends, you can avoid SQL injection problems. This article will MyBatis and SQL injection into these enmity pull pull pull. (Note that MyBatis refers to Mybatis3 by default)
Technical background
The origin of this article is mainly from a SQL injection discovered on the Intranet. SQL injection was found for the keyword parameter of a request on the Intranet.
Basically, this interface is a fuzzy query that implements the keyword in multiple fields, which should be a fairly common requirement. It’s just that there are multiple query criteria. After some searching, we found that the core of the problem was the following code:
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;
}
Copy the codeThe obvious requirement was to implement a fuzzy query for diaplay_name, org, status, and ID, but the developers created their own addKeywordTo method to create a fuzzy query condition involving multiple fields.
It is interesting to note that the injection points of most SQL injections found on the Intranet are basically fuzzy queries. Many developers may feel that fuzzy queries do not have SQL injection problems.
Analyze why the developer wrote it this way, since he was not aware of the SQL injection problems, he probably thought it was the easiest way to write the query conditions directly. The above code is the core of the problem, so let’s look at the corresponding XML file:
<sql id="Example_Where_Clause" > <where > <foreach collection="oredCriteria" item="criteria" separator="or" > <if test="criteria.valid" > <trim prefix="(" suffix=")" prefixOverrides="and" > <foreach collection="criteria.criteria" item="criterion" > <choose > <when test="criterion.noValue" > and ${criterion.condition} </when> <when test="criterion.singleValue" > and ${criterion.condition} #{criterion.value} </when> <when test="criterion.betweenValue" > and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} </when> <when test="criterion.listValue" > and ${criterion.condition} <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," > #{listItem} </foreach> </when> </choose> </foreach> </trim> </if> </foreach> </where> </sql>Copy the code<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.doctor.mybatisdemo.domain.userExample" > select <if test="distinct" > distinct </if> <include refid="Base_Column_List" /> from user <if test="_parameter ! = null" > <include refid="Example_Where_Clause" /> </if> <if test="orderByClause ! = null" > order by ${orderByClause} </if> </select>Copy the codeLet’s go back to the addCriterion method in the JAVA code above, which is generated through the MyBatis Generator.
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
Copy the codeThe addCriterion method here only takes one string argument, it’s actually using an overload, and there are other addCriterion methods that take a different number of arguments. The method used here only passes in one argument, which is understood as condition, so it just adds a Criterion with condition only. If criteria is iterated through, criterion only has condition and noValue, then criterion.noValue is used. This makes the formation of the entire SQL injection clear.
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
Copy the codeCorrect term
Since this is not the correct way to write it, what is the correct way to write it?
First, we can filter keword in the addKeywordTo method in a very simple and straightforward way, which also avoids SQL injection. Regular matching replaces all non-alphabetic or numeric characters in the keyword with empty strings, making SQL injection naturally impossible.
keyword = keyword.replaceAll("[^a-zA-Z0-9\s+]"."");
Copy the codeHowever, this is not a scientific approach. The downside of this approach is that if your keyword needs to include symbols, do you need to consider more scenarios, do you need to add more logical judgments, and do you have the potential to bypass them? So what’s the right way to write it? ‘Comple Queries’ =’ mybatis’
TestTableExample example = new TestTableExample();
example.or()
.andField1EqualTo(5)
.andField2IsNull();
example.or()
.andField3NotEqualTo(9)
.andField4IsNotNull();
List<Integer> field5Values = new ArrayList<Integer>();
field5Values.add(8);
field5Values.add(11);
field5Values.add(14);
field5Values.add(22);
example.or()
.andField5In(field5Values);
example.or()
.andField6Between(3.7);
Copy the codeThe equivalent SQL statement above is:
where (field1 = 5 and field2 is null)
or (field3 <> 9 and field4 is not null)
or (field5 in (8.11.14.22))
or (field6 between 3 and 7)
Copy the codeNow let’s change the original addKeywordTo method:
public void addKeywordTo(String keyword, UserExample userExample) {
userExample.or().andDisplayNameLike("%" + keyword + "%");
userExample.or().andOrgLike(keyword + "%");
userExample.or().andStatusLike("%" + keyword + "%");
userExample.or().andIdLike(keyword + "%");
}
Copy the codeThis is a more standard way to write it. The or() method generates a new Criteria object, adds it to oredCriteria, and returns the Criteria object, which can be expressed chaining and Criterion added to it. In this way, the added Criteria include condition and value. When the query is performed, the Criteria will be entered into criterion.singleValue, so the keyword parameter will only be passed into value. Value is passed in with #{}.
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
Copy the codeTo sum up, the cause of this SQL injection or development did not write in accordance with the specification, their own wheel wrote a method to carry out fuzzy query, but brought SQL injection vulnerability. In fact, Mybatis Generator already generates a rich set of methods for each field that, if used properly, are guaranteed to avoid SQL injection problems.
Can SQL injection be avoided using #{}?
If you suddenly see this question, you might hesitate, right? Use #{} to eliminate SQL injection. But if you break it down, the answer is yes. Let me explain the specific reasons to you.
First we need to understand how #{} is declared in MyBatis. When parameters are declared in #{}, they are executed in a PreparedStatement, which is a precompiled form. Precompilation should be familiar to you, as there is already a precompiled interface in JDBC.
Mybatis is not the core solution to SQL injection, precompilation is. Precompilation can not only escape SQL statements, avoid SQL injection, but also improve execution efficiency. Mybatis bottom layer is actually through JDBC to achieve. Take MyBatis 3.3.1 as an example, SqlRunner in JDBC is designed to achieve specific SQL statements.
Take the Update method as an example. PreparedStatement is used to precompile SQL statements in JAVA.
public int update(String sql, Object. args) throws SQLException { PreparedStatement ps =this.connection.prepareStatement(sql);
int var4;
try {
this.setParameters(ps, args);
var4 = ps.executeUpdate();
} finally {
try {
ps.close();
} catch(SQLException var11) { ; }}return var4;
}
Copy the codeIt’s important to note that preparedStatements here are not exactly the same as precompiled. In fact, precompilation is divided into client precompilation and server precompilation. After 4.1, MySql server already supports precompilation.
Many of the major persistence frameworks (MyBatis, Hibernate) do not actually use precompilation. Precompilation is configured in the parameter list ourselves. If we do not manually enable precompilation, JDBC driver versions after 5.0.5 are disabled by default.
To enable this function, configure the following parameters:
jdbc:mysql://localhost:3306/mybatis? &useServerPrepStmts=true&cachePrepStmts=true
Copy the codeThe database SQL execution consists of several phases as shown in the figure below, but in our case the precompilation for the SQL statement client is completed before it is sent to the server. The main concern on the server side is performance, which is not the focus of this article.
Of course, there may be some differences in how each database implementation is precompiled. However, to prevent SQL injection in MyBatis, just use #{}, because this will implement SQL statement parameterization, avoid direct introduction of malicious SQL statements and execution.
Use of MyBatis Generator
For using MyBatis, MyBatis Generator is definitely an essential tool to use. MyBatis is a code generation tool for MyBatis and iBATIS. It supports all versions of MyBatis and iBATIS 2.2.0 and above.
Because in the real business development, there will be a lot of tables involved, and it is impossible to write corresponding documents by hand. The MyBatis Generator generates the corresponding POJO files, SQL Map XML files, and optional JAVA client code.
The most common way to use MyBatis Generator is to use Maven’s MyBatis -generator-Maven-plugin directly. As long as you have prepared the configuration file and database information, you can use this plugin to generate the corresponding code.
<?xml version="1.0" encoding="UTF-8"? >
<generatorConfiguration>
<context id="MysqlTables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressAllComments" value="false" />
<property name="suppressDate" value="false" />
</commentGenerator>
<! -- Database link URL, username, password -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybaits_test"
userId="xxx"
password="xxx">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="true" />
</javaTypeResolver>
<javaModelGenerator targetPackage="com.doctor.mybatisdemo.domain" targetProject="src/main/java/">
<property name="constructorBased" value="false" />
<property name="enableSubPackages" value="false" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<sqlMapGenerator targetPackage="myBatisGeneratorDemoConfig" targetProject="src/main/resources">
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<javaClientGenerator type="XMLMAPPER" targetPackage="com.doctor.mybatisdemo.dao" targetProject="src/main/java/">
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<! To generate those tables (change tableName and domainObjectName) -->
<table tableName="user" domainObjectName="user"/>
</context>
</generatorConfiguration>
Copy the code
What I want to highlight here is the configuration of a key parameter, the targetRuntime parameter. This parameter has two configuration items: MyBatis3 and MyBatis3Simple. MyBatis3 is the default configuration item. MyBatis3Simple will only generate basic add, delete, change queries, while MyBatis3 will generate add, delete, change queries with conditions, all encapsulated in XXXexample.
With MyBatis3, enableSelectByExample, enableDeleteByExample, enableCountByExample and enableUpdateByExample are true, The corresponding dynamic statement is generated. This is why we generated the Example_Where_Clause above.
If you use the configuration item MyBatis3Simple, the generated SQL Map XML file will be very simple, containing only some basic methods and not producing the dynamic methods above. It is safe to say that if you use MyBatis3Simple and don’t modify it because all the variables are introduced through #{}, you can’t have SQL injection problems.
However, in real business, complex query conditions are often involved, and the development usually uses ancestral configuration files. Therefore, whether to use MyBatis3 or MyBatis3Simple needs to be considered in a case-by-case manner. However, if you are using the default configuration, you need to be careful. Keep in mind that external parameters are most likely unsafe and should not be imported directly. With this in mind, SQL injection problems are pretty much avoided.
conclusion
This article from the internal network of a SQL injection vulnerability caused by the use of MyBatis problem, the principle of #{} in MyBatis and the use of MyBatis generator to do further thinking.
The following points can be summarized:
- Do not use concatenation if possible, this should also be avoided
SQL injectionThe most basic principle - In the use of
The ${}When passing in variables, pay attention to importing and filtering variables. Avoid passing in external variables directly through ${} - Don’t own
To build the wheelsEspecially in the area of security, where frameworks already provide a standard approach. If developed according to specifications, it does not cause SQL injection problems - Note in MyBatis
targetRuntimeIf complex query conditions are not required, you are advised to use it directlyMyBatis3Simple. This is a better way to eliminate risk directly, because once there is a point of risk, there is a potential for problems.
Author: Madne@Ping an Bank Application Security team, view the original article
So much for today, if this article is a little help to you, I hope you can get a thumbs up 👍 oh
Your approval is my motivation to write!
Sorted out some Java architecture, interview materials (micro services, clusters, distributed, middleware, etc.), partners in need can pay attention to the public number [programmer internal point], no routine to get
