What is SQL injection?

SQL injection is to trick the server into executing malicious SQL commands by inserting SQL commands into the query string of the Web form submission or page request URL. Specifically, it takes advantage of an existing application’s ability to inject (malicious) SQL commands into the back-end database engine to execute them. It can get a database on a vulnerable website by typing (malicious) SQL statements into a Web form, rather than executing the SQL statements intended by the designer.

There is a landing box as follows:

SELECT * From table_name WHERE name= 'XX' and password= 'YY' and corporate= 'ZZ'Copy the code

How do you do that,?

SQL > alter table SQL > alter table SQL

SELECT * From table_name WHERE name= ' 'and password=' 'and corporate=' 'or 1=1-'Copy the code

As you can see from the code, the first half of the single quotes are closed, the second half of the single quotes are commented out by “-“, and there is a permanent condition “1=1” in the middle, which results in any character can be successfully logged in.

Important reminder

Don’t think that a check in the input field is enough, and remember that when we submit a form on the Web, we can simulate the URL and access it directly, bypassing the previous check. Therefore, it must be the back end, or data to check to effectively prevent.

(1) Check the validity of user input;

(2) Encrypt and save the user’s login name and password.

(3) Pre-processing SQL.

(4) Using stored procedures to implement queries, although not recommended, is an option.

How to prevent MySQL preprocessing?

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 function structure, as follows:

<select id="getBlogById" resultType="Blog"ParameterType = "int" > SELECT ID,title,author,content FROM blog WHERE id=#{id}

</select>
Copy the code

Here, parameterType represents the input parameterType, resultType represents the output parameterType. In response to the above, if we want to prevent SQL injection, it makes sense to work on input parameters. The above code is highlighted in yellow where the input parameters are concatenated in SQL. After passing in the parameters, print out the executed SQL statement. The SQL looks like this:

SELECT id,title,author,content FROM blog WHERE id = ?
Copy the code

No 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.

After all, can we use MyBatis to prevent SQL injection? Of course not, look at the following code:

<select id="getBlogById" resultType="Blog"ParameterType = "int" > SELECT ID,title,author,content FROM blog WHERE id=${id}

</select>
Copy the code

Notice that the format of the inline parameter changes from #{XXX} to ${XXX}. If we assign the parameter “id” to “3”, the printed SQL will look like this:

SELECT id,title,author,content FROM blog WHERE id = 3
Copy the code

(I added the comparison example above myself to provide a sharp contrast with the previous example.)

<select id="orderBlog" resultType="Blog"ParameterType = "map" > SELECT ID,title,author,content FROM blog ORDER BY${orderParam}

</select>
Copy the code

Notice that the format of the inline parameter changes from #{XXX} to ${XXX}. If we assign the parameter “orderParam” to “id”, the SQL will be printed as follows:

SELECT id,title,author,content FROM blog ORDER BY id
Copy the code

Obviously, this will not prevent SQL injection. In MyBatis, parameters in the form of “${XXX}” are directly involved in SQL compilation and cannot be protected against 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 】 The mapping statement of MyBatis should be used as much as possible"# {} XXX"That’s the format. If you have to use"${} XXX"Such parameters must be manually filtered to prevent SQL injection attacks.

#{} : is equivalent to a PreparedStatement in JDBC

The ${}: is the value of the output variableCopy the code

In short, #{} is precompiled and safe; ${} is not precompiled, just takes the value of a variable, is not secure, SQL injection exists.

If we use ${} after the order by statement, there is a risk of SQL injection without doing anything. If you tell me how to prevent it, I’m sorry to say that you’ll have to manually filter your input. For example, to determine whether the length of the input parameter is normal (injection statements are generally long), a more accurate filter can query whether the input parameter is in the expected parameter set.