SQL injection is a very simple attack method, but it is still very common to this day. The reason is nothing more than: No patch for stupid. Why do you say that? Let’s take JAVA as an example to illustrate:
Suppose there is a table like this in the database:
table user( id varchar(20) PRIMARY KEY , name varchar(20) , age varchar(20) );
Then use JDBC to operate the table:
private String getNameByUserId(String userId) { Connection conn = getConn();//Get connection String sql = "select name from user where id=" + userId; PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs=pstmt.executeUpdate(); ...... }The above code is often used by some developers. Imagine this situation, when the passed userId parameter is "3;drop table user;", the executed SQL statement is as follows:
select name from user where id=3; drop table user;
After the database is compiled and executed, the user table is deleted. Look, a simple SQL injection attack is in effect! This is because the above code does not comply with the programming specifications.
SQL injection does not exist when we program according to specifications. This is also the first way to avoid SQL injection: precompiled statements, the code is as follows:
Connection conn = getConn();//Get connection String sql = "select name from user where id= ?"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, userId); ResultSet rs=pstmt.executeUpdate(); ....
Why doesn't SQL injection exist in the above code? Because precompiled statements are used, the precompiled statement will compile the "select name from user where id= ?" statement in advance when executing, so when executing, you only need to replace it with the passed parameters? Placeholder. For the first case that does not comply with the specifications, the program will create SQL statements and then compile them with the content passed by the user. This is exactly the problem.
In addition to using precompiled statements, there is a second way to avoid SQL injection attacks: stored procedures. Stored Procedure is a set of SQL statements that complete specific functions. After compilation, it is stored in the database. Users can execute it by calling stored procedures and giving parameters (if the stored procedure has parameters), and can also avoid SQL injection attacks.
Connection conn = getConn(); stmt = conn.prepareCall("{call name_from_user(?,?)}"); stmt.setInt(1,2); stmt.registerOutParameter(2, Types.VARCHAR); stmt.execute(); String name= stmt.getString(2);The corresponding stored procedures in the above code are as follows:
use user; delimiter // create procedure name_from_user(in user_id int,out user_name varchar(20)) begin select name into user_name from user where id=user_id; end // delimiter ;
Of course, users can also do character checking on the front end, which is also a way to avoid SQL injection: for example, for the userId parameter above, the user will prompt an error when checking that the semicolon is included.
However, from the most fundamental reason, the SQL injection attack exists because the app does not use the minimum permissions when accessing the database. I think so, it seems that everyone has been using the root account to access the database.
So how does mybatis avoid SQL injection attacks? Let’s use the above table user as an example:
Assume that the mapper file is:
<select id="getNameByUserId" resultType="String"> SELECT name FROM user where id = #{userId} </select>The corresponding java file is:
public interface UserMapper{ String getNameByUserId(@Param("userId") String userId); }You can see that the input parameter is userId of String type. When we pass userId="34;drop table user;" the printed statement is as follows:
select name from user where id = ?
No matter what userID is entered, his SQL statements are like this. This is due to the use of precompiled statements in the underlying implementation. When the database executes this statement, it directly uses the precompiled statement and then replaces the placeholder with the passed userId? Just go to run. Doesn't exist to replace the placeholder first? The compilation process is carried out, so there is no room for survival for SQL injection.
So how does mybatis achieve pre-compilation of SQL? In fact, the framework is using the PreparedStatement class. The PreparedStaement class not only avoids SQL injection, because it has been precompiled. When the same SQL statement is executed N times, it saves (N-1) compilation time, thereby improving efficiency.
If you change the above statement to:
<select id="getNameByUserId" resultType="String"> SELECT name FROM user where id = ${userId} </select> When we enter userId="34;drop table user;" , the printed statement looks like this:
select name from user where id = 34;drop table user;
At this time, mybatis does not use precompiled statements. It will first perform string stitching and then perform compilation. This process is the process of SQL injection taking effect.
Therefore, when writing mybatis mapping statements, try to use the format "#{xxx}". If you have to use parameters like "${xxx}", you must manually do a good job of filtering to prevent SQL injection attacks.
Summarize
The above is a detailed explanation of the method of preventing SQL injection by mybatis introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!