MyBatis Study Note (ii): MyBatis SQL injection problem

MyBatis Study Note (ii): MyBatis SQL injection problem SQL injection attack

First understand the concepts below, what is called SQL injection:

SQL injection attacks, referred to as SQL attacks or injection attacks, are security vulnerabilities that occur at the database level of the application. In short, it is injected into the string of SQL instructions, in the poorly designed program ignored the inspection, then these injected instructions will be mistaken for the database server is normal SQL command to run, and therefore be corrupted or intrusion.

The most common is that we use string join in the application of the combination of SQL instructions, the heart of the people will write some special symbols, malicious tampering with the original SQL syntax, to achieve the purpose of injecting attacks.

Give me a chestnut:

For example, to verify that the user login requires username and password, write the following SQL statement:

select * from user where (name = '"+ username +"') and (pw = '"+ password +"');

Username and password fields are filled in maliciously

username = "1' OR '1'='1";

And

password = "1' OR '1'='1";

Will cause the original SQL string to be filled with:

select * from user where (name = '1' or '1'='1') and (pw = '1' or '1'='1');

The SQL statement that actually runs will become:

select * from user;

That is, no longer need username and password account to achieve the purpose of login, the results are self-evident.

MyBatis Solving SQL injection problems

When we use MyBatis to write SQL statements, we will inevitably use the method of fuzzy query, MyBatis provides two ways #{} and ${} .

• #{value}When preprocessing, the parameter part is used as a placeholder? Override where value represents the name of the accepted input parameter. can effectively solve SQL injection problems
• ${}This means that using a stitching string, the contents of the received parameter will be spliced into SQL without any modifiers, and using ${} stitching SQL will cause SQL injection problems.

Give me a chestnut:

1 querying the records in the Database sample table user, we deliberately use special symbols to see if we can cause SQL injection. Using MyBatis to write SQL statements in the Mapper.xml configuration file, we first use the concatenation string to see how the results are:

 <select id="findUserByName" parameterType="java.lang.String" resultType="cn.itcast.mybatis.po.User">        <!-- 拼接 MySQL,引起 SQL 注入 -->        SELECT * FROM user WHERE username LIKE '%${value}%'    </select>

Note When you write SQL statements in a configuration file, you do not need to add semicolons behind them.

Call the configuration file, write the test file, query the database contents, and use special symbols to cause SQL injection:

    @Test    public void testFindUserByName() throws Exception{        SqlSession sqlSession=sqlSessionFactory.openSession();        //创建UserMapper代理对象        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);        //调用userMapper的方法        List<User> list=userMapper.findUserByName("' or '1'='1");        sqlSession.close();        System.out.println(list);    }}

The results of the operation are as follows:

You can see that the execution statement is actually changed to

select * from user

Print out all the records in the user table. A SQL injection has occurred.

2 SQL injection can be avoided if the SQL statements in the configuration file are changed to #{} form.

 <select id="findUserByName" parameterType="java.lang.String" resultType="cn.itcast.mybatis.po.User">        <!-- 使用 SQL concat 语句,拼接字符串,防止 SQL 注入 -->        SELECT * FROM USER WHERE username LIKE CONCAT('%',#{value},'%' )    </select>

Run the test program again and the console output is as follows:

Can you see the Parameters section in the program? Instead, it solves the problem of SQL statements well and prevents SQL injection. The query result will be empty.