Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
What is SQL injection
SQL injection is one of the most common network attacks. It does not use the bugs of the operating system to implement attacks, but aims at the negligence of the programmer when writing. Through SQL statements, users can log in without an account or even tamper with the database.
The general idea of SQL injection attack
1: find the location of SQL injection
2: Check the server type and background database type
3: SQL injection attacks for different server and database characteristics
The instance
String sql = "select * from user_table where username= ' "+userName+" ' and password=' "+password+" '"; SELECT * FROM user_table WHERE username= "or 1 = 1 -- and password= "" -- SELECT * FROM user_table WHERE username=" or 1 = 1 -- and password= "" -- Select * from user where username= "or 1=1"; -- and then you add two --, which means comment, it comments the following statements so that they don't work, so that the statement always -- executes correctly, so that the user can easily fool the system into being legitimate. SELECT * FROM user_table WHERE username=''; DROP DATABASE (DB Name) --' and password=' "" "Copy the codeDefends against SQL injection methods
Note: Program, whoever has the SQL injection vulnerabilities are program to accept from the client user input variables or URL parameter, and the variable or parameter is part of the SQL statement, or transfer the contents of this user input parameters, we should be vigilant, this is the safety in the field of the principle of “external data do not trust”, Looking at the various attacks in the Web security field, most of them are caused by developers violating this principle, so it is natural to start with variable detection, filtering, and verification to ensure that the variables are what the developer intended.
1. Check the variable data type and format
If your SQL statement is of the form where id={$id} and all ids in the database are numbers, you should check that the variable id is of type int before executing the SQL statement. If you accept a mailbox, you should check and make sure that the variable is in the format of the mailbox, as well as other types such as date, time, etc. To sum up: as long as there is a fixed format of the variable, before the SQL statement execution, should be strictly in accordance with the fixed format to check to ensure that the variable is the format we expect, which can largely avoid SQL injection attacks. For example, in our previous example of accepting the username parameter, our product design should have a username rule at the beginning of user registration, such as 5-20 characters, which can only be composed of upper and lower case letters, numbers and some security symbols, no special characters. At this point we should have a check_username function for a uniform check. However, there are still many exceptions to this rule, such as post systems, comment systems, etc. that must allow users to submit arbitrary strings, which requires other schemes such as filtering.
2. Filter special symbols
For variables whose fixed format cannot be determined, special symbol filtering or escape must be performed.
Bind variables, using precompiled statements
The mysqli driver of MySQL provides support for precompiled statements. Different programming languages have methods for using precompiled statements
In fact, the best way to prevent SQL injection is to use pre-compiled statements for bound variables. The semantics of pre-compiled SQL statements do not change. The hacker is unable to change the structure of SQL statements even if he or she is skilled
What is SQL precompilation
1.1: What are precompiled statements
In general, our SQL received in db and returned after the final execution can be divided into the following three processes:
- Lexical and semantic parsing
- Optimize SQL statements and make execution plans
- Execute and return the result
We call these common Statements Immediate Statements.
However,in many cases, a single SQL statement may be executed repeatedly, or only with different values each time it is executed (for example, the WHERE clause of query is different, the set clause of UPDATE is different, and the values of insert are different). If you need to go through the above lexical semantic analysis, sentence optimization, make execution plan, etc., then the efficiency is obviously not good.
Prepared Statements are Prepared Statements or Parameterized Statements. These Statements are usually called Prepared Statements or Parameterized Statements. The advantage of precompiled Statements is summarized as: One compilation, many times running, eliminating the process of parsing and optimization; In addition, precompiled statements prevent SQL injection. Of course, in terms of optimization, most of the time the optimal execution plan is not only determined by knowing the template of SQL statements, but also by estimating the cost through specific values.
Why does a PrepareStatement prevent SQL injection
The principle is to use the precompilation method, first compile the parameter set that can be controlled by the client in THE SQL statement to generate the corresponding temporary variable set, and then use the corresponding setting method to assign values to the elements in the temporary variable set. The assignment function setString() will perform mandatory type check and security check on the parameters passed in. Therefore, SQL injection is avoided.
Select *from tablename where username=? and password=?Copy the code- The SQL statement is precompiled with the database before receiving input from the user, so that no matter what username and password the user enters, the judgment is always the same logical relationship, preventing SQL injection
- To sum up, the reason why parameterization can prevent injection is that statements are statements, parameters are parameters, and parameter values are not part of statements. The database only runs according to the semantics of statements. As for whether to run with an ordinary backpack or a monster, it does not affect the route, but the difference between running faster and slower.
How to prevent SQL injection in Mybatis (# over $)
1, Let’s look at the difference between the following two SQL statements:
<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>
Copy the code
<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>
Copy the code# and $in mybatis
1, # treat all incoming data as a string, and place double quotes around automatically passed data. Such as: Where username=#{username} where username= ‘111’ where username= ‘111’ Where username=”id” where username=”id”; Whereusername = displays incoming data directly in SQL. \ where username= display the incoming data directly generated in SQL. For example, whereusername={username}, if the value passed in is 111, the value parsed to SQL is whereusername= 111. If the value passed in is; drop table user; Select id, username, password, role from user where username=; drop table user; 3, # mode can largely prevent SQL injection, mode does not prevent SQL injection. 4, the way does not prevent Sql injection. \ 4. The method does not prevent Sql injection. 5, generally can use # do not use, if you have to use “, if you have to use “, if you have to use “{XXX}” such parameters, to manually do a good job of filtering, to prevent SQL injection attacks. 6. In MyBatis, parameters in the format of “XXX” will directly participate in SQL compilation, so as not to avoid injection attacks. However, when it comes to dynamic table names and column names, only parameters in the format “{XXX}” will directly participate in SQL compilation, thus avoiding injection attacks. However, when it comes to dynamic table names and column names, only parameters in the format “XXX” will directly participate in SQL compilation, thus avoiding injection attacks. However, when it comes to dynamic table names and column names, only {XXX} can be used. Therefore, such parameters need to be handled manually in the code to prevent injection. 【 Conclusion 】 When writing the mapping statement of MyBatis, try to use the format of “#{XXX}”. If you have to use a parameter like “${XXX}”, do manual filtering to prevent SQL injection attacks.
How does Mybatis prevent SQL injection
MyBatis framework as a semi-automatic persistence layer framework, its SQL statements have to be written manually by ourselves, this time of course need to prevent SQL injection. In fact, MyBatis SQL is an “input + output” function, similar to the structure of the function, refer to the above two examples. ParameterType indicates the input parameterType, resultType indicates the output parameterType. In response to the above, if we want to prevent SQL injection, it makes sense to work on input parameters. In the above code, # is used to concatenate the input parameters in THE SQL. After passing in the parameters, print the executed SQL statement, and the SQL will look like this:
select id, username, password, role from user where username=? and password=?
Copy the codeNo matter what parameters you enter, the printed SQL looks like this. This is because MyBatis has enabled the precompilation function, before SQL execution, the above SQL will be sent to the database for compilation; To execute, directly replace the placeholder “? “with the compiled SQL. That’s it. Since SQL injection only works with the compile process, this approach is a good way to avoid SQL injection problems.
MyBatis how to achieve SQL precompilation? Underneath the framework, PreparedStatement is the JDBC PreparedStatement class. PreparedStatement is a subclass of the familiar Statement, whose object contains compiled SQL statements. This “ready to go” approach not only improves security, but also improves efficiency when executing the same SQL multiple times. The reason is that the SQL is already compiled and does not need to be compiled when it is executed again