preface

SQL injection is one of the more dangerous types of attacks, and as more and more of our persistence layer frameworks deal with it, we pay less and less attention to it. Recently, when the department sorted out security vulnerabilities, it mentioned some modification points about SQL injection, so let’s record and learn together.

The body of the

The principle of

SQL injection is an attack that disguises SQL code in input parameters and sends it to the server for parsing and execution. In other words, some SQL code is embedded in the parameters of some requests to the server. The server will concatenate the corresponding parameters when executing SQL operations, and also concatenate the “SQL” of some SQL injection attacks, resulting in some unexpected operations.

Example:

For example, we use the login interface: the login interface includes the user name and password input box, as well as the submit button, input the user name and password, submit.

During login, the interface /user/login/ is called and the parameters username and password are added to connect to the database. Then, the background verifies the username and password carried in the request parameters, which is the SQL query process. Assume that the correct user name and password are ls and 123456. Enter the correct user name and password and submit. The following SQL statements are invoked.

SELECT * FROM user WHERE username = 'ls' AND password = '123456'
Copy the code

If we use ‘or 1=1 #’ as the username argument, then the server builds the SQL statement as follows:

select * from users where username=' ' or 1=1#' and password='123456'
Copy the code

# ignores the following statement, so the above SQL is equivalent to:

select * from users where username=' ' or 1=1
Copy the code

1=1 is a constant type condition, so the SQL becomes the following, query all login users.

select * from users
Copy the code

The SQL injection is just a manipulation of the parameter level. It is even more dangerous if it introduces some functional SQL, such as the login interface above, if the user name uses’ or 1=1; delete * from users; # “, then in “;” After that, it is equivalent to a new SQL, which is a very dangerous operation, so the SQL injection should be paid special attention to.

Addresses SQL injection principles

SQL precompiled

After knowing the principle of SQL injection, we also know that mysql has the function of pre-compilation, which means that when the server starts, the mysql client sends the TEMPLATE of SQL statements (variables are occupied by placeholders) to the mysql server, and the mysql server compiles the template of SQL statements. Compiled according to the statement of optimization analysis to optimize the corresponding index, in the final binding parameters when the corresponding parameters to the mysql server, perform directly, save the SQL query time, as well as the mysql server resources, achieve the goal of a compiler, executed multiple times, besides, still can prevent SQL injection.

How do you prevent SQL injection? In fact, when the bound parameter is passed to the mysql server, the mysql server compiles the parameter, i.e. populates it with the corresponding placeholder, and escapes it. JDBC, which we commonly use, has pre-compilation capabilities that not only improve performance, but also prevent SQL injection.

        String sql = "select id, no from user where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeQuery();
Copy the code

Strict parameter verification

Parameter verification is not to say, in some parameters should not have special characters in advance of the special character verification can be.

Framework support – Mybatis

Mybatis, a persistent layer framework commonly used in the Java ecosystem, can prevent SQL injection well. The following two mapper files can prevent the former, but the latter can not.

<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>
Copy the code
  1. # Treat all incoming data as a string and place double quotes around automatically passed data. Where username=#{username} where username=#{username} where username=#{username} where username=#{username} where username=#{username} where username=#{username}
  2. {username} where username=111; If the value passed in is; drop table user; Select id, username, password, role from user where username=; drop table user;

So if you’re not really doing functional SQL like dropping tables, creating tables, etc., you still need # to avoid SQL injection. Mybatis still uses JDBC precompilation function.

conclusion

The above is the SQL injection method, principle and harm, problem solving to make some small summary, if there is a problem I hope we can point out, a lot of communication.