How mybatis is preventing SQL injection

MyBatis is how to prevent SQL injection 1, first look at the following two SQL statements the difference:

 <  select  id  = "Selectbynameandpassword"   ParameterType  = "Java.util.Map"   Resultmap  = "Baseresultmap"  >  select ID, username, password, rolefrom userwhere username = #{username,jdbctype=varchar }and password = #{password,jdbctype=varchar}  </ select  >  

<id= "Selectbynameandpassword"  parametertype= "Java.util.Map"  resultmap= "Baseresultmap">Select ID, username, password, rolefrom userwhere Username = ${username,jdbctype=varchar}and Password = ${password,jdbctype=varchar}</Select  >

The difference between # and $ in MyBatis:

1. #将传入的数据都当成一个字符串, a double quotation mark is added to the automatically passed data.
For example: where Username=#{username}, if the value passed in is 111, then the value parsed into SQL is where username= "111", if the value passed in is an ID, the parsed SQL is the where username= "id".
2. $ will generate the incoming data directly in SQL.
For example: where Username=${username}, if the value passed in is 111, then the value parsed into SQL is where username=111;
If the value passed in IS; drop table user; The SQL to parse is: Select ID, username, password, role from user where username=;d rop table user;
3. #方式能够很大程度防止sql注入, the $ method cannot prevent SQL injection.
4, the $ method is typically used to pass in database objects, such as incoming table names.
5, generally can use the # do not use $, if you have to use "${xxx}" such parameters, to do the filtering work manually, to prevent SQL injection attacks.
6, in MyBatis, "${xxx}" parameters such as the format will be directly involved in SQL compilation, so as not to avoid injection attacks. But when it comes to dynamic table names and column names, you can only use parameter formats such as "${xxx}". So, such parameters need to be handled manually in the code to prevent injection.
"Conclusion" when writing MyBatis mapping statements, use the format "#{xxx}" as much as possible. If you have to use parameters such as "${xxx}", do the filtering work manually to prevent SQL injection attacks.

2. What is SQL injection

SQL injection Interpretation: is a code injection technique used to attack data-driven applications where a malicious SQL statement is inserted into the Entity field being executed (for example, in order to dump the database contents to an attacker)

SQL injection , everyone is not unfamiliar, is a common way of attack. An attacker could enter some strange SQL fragment (such as "or ' 1 ' = ' 1 ') on the form information or URL of the interface, potentially invading an application with insufficient parameters . Therefore, we need to do some work in our application to guard against such attack mode. in some security-demanding applications, such as banking software, it is often used to prevent SQL injection by replacing all SQL statements with stored procedures . This is certainly a safe way , but we may not need this kind of rigid way in our usual development.

3, MyBatis is how to prevent SQL injection

The MyBatis framework, as a semi-automated persistence layer framework, has its SQL statements written manually by us, which of course needs to prevent SQL injection. In fact, MyBatis SQL is a function with " input + output ", similar to the structure of the function, refer to the above two examples. Where parametertype represents the input parameter type, and Resulttype represents the output parameter type. In response to the above, if we want to prevent SQL injection, we naturally have to work on the input parameters. The above code uses the # input parameter in the SQL splicing part, after passing in the parameters, print out the executed SQL statement, you will see that SQL is:

Select ID, username, password, role from user where username=? and password=?

Regardless of the input parameters, the printed SQL is like this. This is because MyBatis enabled the pre-compilation feature, before SQL execution, the above SQL will be sent to the database to compile, when executed, directly using the compiled SQL, replace the placeholder "?" You can do it. Because SQL injection can only work on the compilation process, this is a good way to avoid the problem of SQL injection.

How does the "underlying implementation principle" MyBatis do SQL precompilation? In fact, at the bottom of the framework, the PreparedStatement class in JDBC is working, PreparedStatement is a subclass of statement that we are familiar with, and 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 SQL has been compiled and is not compiled again when executed again.

// secure, pre-compiled Connection conn = Getconn (); // Get Connected // pre-compile number before executing SQL this clause PreparedStatement pstmt = conn.preparestatement (sql); pstmt.setstring (1, id); ResultSet rs=pstmt.executeupdate (); ......

// Unsafe , not pre-compiled Private string Getnamebyuserid (String userId) {    = Getconn ();   Get the connection    String sql = "Select Id,username,password,role from user where id=" + ID    ; // when the ID parameter is "3;drop table user;" , the SQL statement executed 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

Simply put, #{} is pre-compiled, is secure, ${} is not precompiled, just takes the value of the variable, is non-secure, there is SQL injection.
If we use ${} after the order BY statement, there is a risk of SQL injection when we do not do any processing. You say how to prevent, then I can only sad to tell you, you have to manually deal with filtering the input content. If you determine whether the length of the input parameters is normal (the injection statement is generally very long), more accurate filtering can be queried whether the input parameters in the expected set of parameters.

4. Reference Articles

http://blog.csdn.net/yizhenn/article/details/52384601

Https://www.cnblogs.com/200911/p/5869097.html

Http://www.jb51.net/article/95314.htm

How mybatis is preventing SQL injection