1. Concept: what is SQL injection

sql injection : is a code injection technology, used to attack data-driven applications, malicious SQL statements are inserted into the executed entity fields.

It’s a common attack. If the attacker enters some special SQL fragments (such as “or 1 = 1”) on the form information or URL of the interface, it is possible to invade the application with incomplete parameter verification. Therefore, some work should be done in application development to prevent SQL injection. In some applications with high security requirements (such as banking software), SQL statements are usually replaced by stored procedures to prevent SQL injection. It’s a very safe way to deal with it.

answer: mybatis can prevent SQL injection, please continue to read.

2. Implementation: mybatis prevents SQL injection

<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
  select id, username, password, role
     from user
        where username = #{username,jdbcType=VARCHAR}
        and password = #{password,jdbcType=VARCHAR}
</select>
<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
    select id, username, password, role
        from user
        where username = ${username,jdbcType=VARCHAR}
        and password = ${password,jdbcType=VARCHAR}
</select>

the difference between # and $ in mybatis:

1. # will treat all incoming parameters as a string, and will automatically add a double quotation mark to it.

For example: where user name = # {user name}, if the value passed in is 111, then the value parsed into SQL is where user name = # {user name}. If the value passed in is ID, then the value parsed into SQL is where user name = # {user name}

2. $ directly displays and generates the incoming data in SQL.

For example: where user name = ${user name}, if the value passed in is 111, then the value parsed into SQL is where user name = 111

If the passed in value is; drop table user;, then the parsed SQL is: select id, username, password, role from user where username =; drop table user

3. The # method can prevent SQL injection to a great extent, while the $ method cannot prevent SQL injection.

4. The $ method is generally used to pass in database objects, such as table names

5. It is recommended to use #, to avoid using $. If you have to use parameters like “${XXX}”, you need to do a good job in filtering to prevent SQL injection attacks.

6. In mybatis, parameters with “${XXX}” format will directly participate in SQL compilation, so injection attack cannot be avoided. But when it comes to dynamic table names and column names, we can only use parameter formats like “${XXX}”. Therefore, such parameters need to be handled manually in the code to prevent injection.

[Conclusion] the format of “# {XXX}” should be used as much as possible when writing the mapping statement of mybatis. If you have to use parameters like “${XXX}”, you should do a good job of filtering manually to prevent SQL injection attacks.

3. Principle analysis: how does mybatis prevent SQL injection

Mybatis framework is a semi-automatic persistence layer framework , SQL statements need to be written manually by developers, at this time, SQL injection must be prevented. In fact, mybatis SQL has the function of “ input + output “, which is similar to the function structure. Please refer to the above two examples. Among them, parametertype indicates the input parameter type and resulttype indicates the output parameter type. In retrospect, if you want to prevent SQL injection, you have to do something about the input parameters. In the above code, #, is the part where input parameters are spliced in SQL. After the parameters are passed in, the executed SQL statement will be printed out, as follows:

select id, username, password, role from user where username=?and password=?

That is, no matter what kind of parameters you enter, the printed SQL will look like the above. Reason: mybatis enables the precompile function. Before SQL execution, SQL will be sent to the database for compilation. During execution, the compiled SQL will be used directly, and the parameter can replace the “?” in the place holder. Because SQL injection can only work on the compilation process, this way can avoid SQL injection attack.

[underlying implementation principle] how does mybatis precompile SQL?In fact, at the bottom of the framework, it is the Preparedstatement class in JDBC that works. Preparedstatement is a subclass of statement that we are very familiar with. Its objects contain compiled SQL statements. This “ready” approach not only improves security, but also improves efficiency when executing the same SQL multiple times. The reason is that the SQL has been compiled and there is no need to compile it again.

//Safe, pre-compiled
Connection conn = getConn();//Get the connection
String sql = "select id, username, password, role from user where id=?" ; // the statement will be pre-compiled before executing sql
PreparedStatement pstmt = conn.prepareStatement(sql); 
pstmt.setString(1, id); 
ResultSet rs=pstmt.executeUpdate(); 
......

//Unsafe, not pre-compiled
private String getNameByUserId(String userId) {
    Connection conn = getConn();//connected
    String sql = "select id,username,password,role from user where id=" + id;
    //When the id parameter is "3;drop table user;", the executed sql statement is as follows:
    //select id,username,password,role from user where id=3; drop table user;  
    PreparedStatement pstmt =  conn.prepareStatement(sql);
    ResultSet rs=pstmt.executeUpdate();
    ......
}

Conclusion

#{}: equivalent to Preparedstatement in JDBC

${}: is the value of the output variable

In short: # {} needs to be precompiled, which is safe; ${} only takes variable values without precompiling, which is not safe, and there is a risk of SQL injection attack.

If ${} is used after the order by statement, there is a risk of SQL injection when nothing is done. Need to develop this code level parameter verification, filtering parameters, parameter length, whether in the expected set, whether contains special or database keywords.