How to prevent sql injection and parameter passing in mybatis

Posted by dropfaith on Sun, 02 Jan 2022 23:24:57 +0100

environment

mysql is used. The database name is test and contains 1. The table name is users. The data in users is as follows

SQL injection under JDBC

There are two methods to execute SQL statements under JDBC, namely Statement and PrepareStatement, in which PrepareStatement is precompiled

Statement

SQL statement

SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'

When the incoming data is

username = admin
password = admin
SELECT * FROM users WHERE username = 'admin' AND password = 'admin';

That is, when there are data with username=admin and password=admin, the data of this user will be returned

Universal password: admin 'and 1=1#

[I > all resources acquisition < I]
1. 200 out of print e-books that can't be bought
2. Video materials inside 30G safety factory
3. 100 src documents
4. Common safety interview questions
5. Analysis of classic topics in ctf competition
6. Complete kit
7. Emergency response notes
8. Network Security Learning Route

The final sql statement becomes

SELECT \* FROM users WHERE username = \'admin\' and 1=1#

That is, all data with the user name admin and 1 = 1 are returned. 1 = 1 is always true, so all data is always returned

If you enter: admin 'or 1=1 # will return all data, because admin' or 1=1 is always true

Therefore, it is not safe for JDBC to use Statement, and programmers need to filter it well. Therefore, generally, programmers using JDBC prefer to use PrepareStatement for precompiling. Precompiling not only improves the efficiency of program execution, but also improves the security

PreParedStatement

The difference between PrepareStatement and Statement is that PrepareStatement precompiles SQL statements. The advantage of precompiling is not only to prevent SQL injection to a certain extent, but also to reduce the compilation times of SQL statements and improve performance. Its principle is to compile SQL statements first. Regardless of the final input, the precompiled statements are only executed as strings, SQL injection only destroys the compilation process. In the execution stage, the input string is only treated as data, and there is no need to parse the SQL Statement. Therefore, the injection problem is solved

Because the SQL statement compilation stage is used for lexical analysis, syntax analysis, semantic analysis and other processes, that is, the compilation process identifies keywords, execution logic and so on. After compilation, what the SQL statement can do is determined. The injected part after compilation can no longer change the execution logic. This part can only be equivalent to the input string being processed

The Statement method needs to be compiled every time it is executed, which will increase the system overhead. Theoretically, the efficiency and security of PrepareStatement will be better than that of Statement, but it does not mean that using PrepareStatement is absolutely safe and will not produce SQL injection.

PrepareStatement defense precompiled is written using? As a placeholder, and then precompile the SQL statement, because? As a placeholder, you have told the database the structure of the entire SQL statement, that is, the? The parameter passed in at is not the SQL statement, so even if the attacker passes in the SQL statement, it will not be parsed by the database

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";

//Precompiled sql statements

PreparedStatement pstt = connection.prepareStatement(sql);
pstt.setString(1,username);
pstt.setString(2, password);

ResultSet resultSet = pstt.executeQuery();// Returns the result set, which encapsulates all the query results of the production department

First, specify the structure of the SQL statement, and then insert the data into the placeholder. This will defend the SQL statement. The paylaod constructed by the attacker will be interpreted as an ordinary string. We can check what SQL statement will eventually become through output

It can be found that single quotation marks can also be escaped, generally only through wide byte injection. The following will show why precompiling can prevent SQL injection and explain why there is an extra escape character at the code level

Unsafe precompiling

Splicing

As we all know, the main reason why SQL injection can be used by attackers is that attackers can construct payload s. Although some developers use precompiling, they will directly construct SQL statements by splicing due to lack of security thought or laziness. At this time, precompiling cannot prevent SQL injection

Code (replace the above code slightly):

//Create sql statement
String sql = "SELECT * FROM users WHERE username = '" + req.getParameter("username") + "' AND password = '" + req.getParameter("password") + "'";
System.out.println(sql);
//Precompiled sql statements
PreparedStatement pstt = connection.prepareStatement(sql);
ResultSet resultSet = pstt.executeQuery(sql);//Returns the result set, which encapsulates all the query results of the production department

In this way, even if precompiled is used, the precompiled statements are already constructed by the attacker, so SQL injection cannot be prevented

Or is it used earlier? After placeholders, the statements are spliced, which will also lead to SQL injection

To prevent sql injection, you must first use? As a placeholder, specify the structure of the sql statement, and then do not destroy the structure later

Using the in statement

String sql = \"delete from users where id in(\"+delIds+\");

This deletion statement is mostly used in the check box. The reason for using splicing instead of placeholders for precompiling in in is that it is often impossible to determine how many objects are contained in deIds

Input: 1,2

Normally, only the values with id 1 and 2 will be output

If you enter: 1,2) or 1=1#

SQL injection will be formed to output all the values in the bitter database

Correct writing:

Or does precompiling need to be used, so we need to process the incoming objects, first determine the number of objects, and then add the same number of placeholders? For precompiling

public int gradeDelete(Connection con, String delIds) throws Exception{
    String num = "";
    //Split the object, and the split point depends on the actual situation
    String[] spl = delIds.split(".");

    //Add the same number of placeholders according to the number of objects?, Used for precompiling
    for(int i = 0; i< spl.length; i++){
        if(i == 0){
            num += "?";
        } else {
            num += ".?";
        }
    }
    String sql = "delete from users where id in("+num+")";
    prepareStatement pstmt = con.prepareStatement(sql);
    try {
        for(int j = 0; j < spl.length; j++){
            pstmt.setInt(j+1, integer.parseint(spl[j]));
        }
        return pstmt.executeUpdate();
    } catch(Exception e){
        //
    }

    return 0;
}

Take bilibili's deleted videos as an example. When I cancel the collection of multiple videos, the package caught is

892223071%3A2%2C542789708%3A2%2C507228005%3A2%2C422244777%3A2%2C549672309%3A2%2C719381183%3A2%2C976919238%3A2%2C722053417%3A2
 Decoded as
892223071:2,542789708:2,507228005:2,422244777:2,549672309:2,719381183:2,976919238:2,722053417:2

It can be found that: 2, segmentation, then we just need to fill in the split

String\[\] spl = delIds.split(\":2,\");

The result is:

Then use precompiling

Using like statements

boolean jud = true;
String sql = "select * from users ";
System.out.println("Please enter the content to query:");
String con = sc.nextLine();
for (int i = 0; i < con.length(); i++){
    if(!Character.isDigit(con.charAt(i))){
        jud = false;
        break;
    }
}
if(jud){
    sql += "where password like '%" + con + "%'";
}else{
    sql += "where username like '%" + con + "%'";
}

When the user input is a string, query the user information of the user name and password containing the input content. When the user input is a pure number, query the password alone, which will cause SQL injection by splicing

Normal execution:

SQL injection

Correct writing

First, we need to change all the splices to? Make placeholders, but after using placeholders, use setString to replace the passed parameters with placeholders, so we need to judge how many placeholders need to be inserted and replaced first

boolean jud = true;
int v = 0;
String sql = "select * from users ";
System.out.println("Please enter the content to query:");
String con = sc.nextLine();
for (int i = 0; i < con.length(); i++){
    if(!Character.isDigit(con.charAt(i))){
        jud = false;
        break;
    }
}
if(jud){
    sql += "where password like ?";
    v = 1;
}else{
    sql += "where username like ? and password like ?";
    v = 2;
}

//Precompiled sql statements
PreparedStatement pstt = connection.prepareStatement(sql);
if(v == 1){
    pstt.setString(1, "%"+con+"%");
}else if (v == 2){
    pstt.setString(1, "%"+con+"%");
    pstt.setString(2, "%"+con+"%");
}

Try SQL injection

Found escaped

Using the order by statement

Through the use of the in keyword and the like keyword above, it seems that you can completely prevent SQL injection by precompiling only by using placeholders for the positions where parameters are to be passed. However, this is not the case. Precompiling cannot be used when using the order by statement, because the field name or field position needs to be added after the order by clause, and the field name cannot be quoted, Otherwise, it will be considered as a string rather than a field name. However, using preparestatement will force 'to be added to the parameter. I will analyze why the parameter is handled in this way at the code level below

Therefore, when using the order by Statement, you must use the spliced Statement, which will cause SQL injection. Therefore, you should be prepared for defense in filtering

Debug and analyze the principle of PrepareStatement to prevent SQL injection

Enter debugging and check in depth how PrepareStatement precompiling prevents sql injection

Enter admin for the user name and admin for the password. The purpose is to see how the precompile handles a reasonable string and an unreasonable string

Since the username and password we entered are admin and admin ', respectively, and admin' is an illegal value, we only

pstt.setString(2, password);

Hit the breakpoint, and then go to the setString method

Step to line 2275, where there is a boolean variable named needsQuoted, which defaults to true

Then enter the if judgment. One of the methods is isescapeeneedforstring

After entering, it is found that there is a Boolean needsHexEscape, which is false by default. Then, the string, that is, the passed in parameter admin ', is parsed word by word to determine whether there are illegal characters. If so, set needsHexEscape to true and break the loop, and then return needsHexEscape

Because we pass in admin 'with' single quotation mark, we will catch it in the process of switch, set needsHexEscape = true, directly break the loop, and then directly return needsHexEscape

Go back to the setString method, run the code in the if body after the if judgment, first create a StringBuilder with the length of admin+2, and then add 'at the beginning and end of the parameter respectively

In short, the function of this switch body is to directly add the same characters to StringBuilder without processing normal characters. If illegal characters are added, the transferred illegal characters will be added. Because they are not replaced directly, they are added. In short, the parameters passed in by the user are not used at all, so protection is naturally achieved

The 'admin' we passed in is' admin ', and' and 'will be added in StringBuilder after it is captured by switch. Finally, our' admin 'will become' admin \ ', that is,' admin ', which also prevents the most important part of SQL injection - closed statements

Then insert according to the position where you want to insert the placeholder

SQL injection under Mybatis

Two parameter transmission methods of Mybatis

First of all, we need to understand that there are two parameter transfer methods under mybatis: KaTeX parse error: Expected 'EOF', got '#' at position 5: {} and# ̲ {}. The difference is that {} is used to pass parameters. Mybatis directly splices the passed parameters to SQL statements. Second, using #{} to pass parameters is converted into placeholders for precompiling like JDBC

Results run under #{}:

select * from users where username = #{username} and password = #{password}

Results run under ${}:

select * from users where username = "${username}" and password = "${password}"

SQL injection

${}

PeopleMapper settings

<select id="getPeopleList1" resultType="com.g1ts.People">
    select * from users where username = #{username} and password = #{password}
</select>

Normal operation:

username:admin
password:admin

sql injection:

username:admin" and 1=1#
password:sef

Successful sql injection

#{}

Mapper settings

<select id="getPeopleList2" resultType="People">
    select * from users where username = #{username} and password = #{password}
</select>

normal operation

username:admin
password:admin

Try SQL injection

username:admin" and 1=1#
password:sef

SQL injection failed

Using like statements

Correct writing

mysql:
    select * from users where username like concat('%',#{username},'%')
oracle:
    select * from users where username like '%'||#{username}||'%'
sqlserver:
    select * from users where username like '%'+#{username}+'%'

Using the in statement

Correct writing

mysql:
    select * from users where username like concat('%',#{username},'%')
oracle:
    select * from users where username like '%'||#{username}||'%'
sqlserver:
    select * from users where username like '%'+#{username}+'%'

Using the order by statement

In the same way as JDBC, using #{} mode to pass reference will cause the order by statement to be invalid, so you still need to do a good job in filtering when using the order by statement

Debug and analyze the principle of Mybatis to prevent SQL injection

I'm not good at learning and can't locate on XMLScriptBuilder all the time, so I just look at the mybatis parsing process written by others and locate the method location through the parsing process

First, let's talk about the conclusion. First, Mybatis will judge the SQL statements in mapper. The judgment content is whether to pass parameters in ${} or #{}. If you pass parameters in #{}, it will be used? As placeholders for precompiling, Mybatis will only handle the placeholders of SQL statements. The last step of processing the incoming parameters is to call JDBC precompiling

Complete call process:

${} parsing execution process

First, parseDynamicNode() in XMLScriptBuilder

I made a judgment here. Let's start with the conclusion. This isDynamic judgment is actually the judgment mapper Do sql statements in XML use #{} precompiling or Spell meet ， send use {} splice, using Splicing, use {} to enter DynamicSqlSource, otherwise enter RawSqlSource

After entering the parseDynamicTags method, you can find that there are two situations that will make isDynamic true, and isDynamic() is used to determine

Enter isDynamic()

You can see that two methods are running, DynamicCheckerTokenParser() and createParser(), mainly on createParser(). Check the method body

It is found that the GenericTokenParser method is called, and three parameters openToken, closeToken and handler are passed in. The value of openToken is , c l o s e T o k e n of value by ， very bright display Just yes yes s q l language sentence enter that 's ok solution Analysis ， sentence break yes no by {the value of closeToken is}. Obviously, it is to parse the sql statement to determine whether it is} The value of closeToken is. Obviously, it is to parse the sql statement, judge whether it is passed in {} mode, and enter the GenericTokenParser method

However, it simply sets the value of the variable and returns up to isDynamic() to enter the next statement, parser parse(this.text);

After debugging, you can clearly see the value passed in, ${} and sql statements appear at the same time. It is speculated that this is where the match is made

Enter the parse method, which parses the sql statement. When the ${} field is encountered, the position will be empty (null), as can be seen from the returned StringBuilder value

After execution, return to the isDynamic() method. Recursion in the return value is actually to return the isDynamic value, and then return up to the parseScriptNode() method

The end result is a DynamicSqlSource object

At this point, the parsing of SQL statements is over until running to peoplemapper Getpeoplelist1 (people1), step into the invoke method

The previous method is roughly to obtain the passed in parameters and SQL statements, and step to the execute method, which is used to judge the type of SQL statements

Because our SQL statement makes select, it will fall into the select of the s witch body and enter the excuteForMany method of case select

Continue to enter the selectList method. I don't know the exact process here. Anyway, after some debugging, I finally came to the query method, and then enter the getBoundSql method

After entering the getBoundSql method, you can take a look at the parameters and find that the type of sqlSource is the DynamicSqlSource set earlier

Continue to the getBoundSql method, and then to rootsqlnode Apply method

There is a pit here. It may be because I don't have enough technology. Because there are many implementations of this apply method, direct stepping will run to MixerSqlNode, but I checked the data and found that it is actually in TextSqlNode

Step into the createParser method and find that GenericTokenParser is called, which is the same as the parsing process above

From the StringBuider returned from the parse method, it can be found that the parameters and SQL statements have been successfully spliced together

#{} parse execution process

In the previous analysis of ${}, we mentioned the parseDynamicNode() method in XMLScriptBuilder to judge the mapper Whether the SQL statement in the XML file passes parameters in ${} or #{}. If it is #{}, the RawSqlSource method will eventually be called

Enter RawSqlSource method

Continue to run and go to sqlsourceparser Parse method

You can find that the functions used to parse ${} appear

GenericTokenParser parser = new GenericTokenParser("#{", "}", handler);

After entering the method body, it is found that the purpose is to set the values of openToken and closeToken to #{and} respectively

What really operates on SQL statements is

String sql = parser.parse(originalSql);

Enter parser Parse method, after running to the end, check the value of StringBuilder, and find that the function uses #{} the? Replaced

At this point, the parsing process ends and runs until peoplemapper Getpeoplelist1 (people1), enter the invoke method, and then the previous process is roughly consistent with the ${} parsing, and enter mappermethod Execute method, and then judge the type of sql statement to be executed, then enter the executeForMany method, run until the selectList method, and finally enter the query method

The query method calls itself as the return value

The return value of this method will call delegate Query method, which is the method I execute #{} and runs until

else {
    list = this.queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);
}

Post entry

After entering the queryFromDatabase method, run to

try {
    list = this.doQuery(ms, parameter, rowBounds, resultHandler, boundSql);
}

Enter the doQuery method and the prepareStatement() method

among

Connection connection = this.getConnection(statementLog);

Is an object that establishes a connection to the database

Enter the parameterize() method

Continue to the setParameters method

The setParameters method is used to splice the SQL statement and the passed in parameters

In list < parametermapping > parametermappings = this boundSql. getParameterMappings(); In, the boundsql is obtained, that is, the set sql statement is obtained

ParameterMapping parameterMapping = (ParameterMapping)parameterMappings.get(i);

The required parameters in the SQL statement are obtained. My SQL statement is select * from users where
username = #{username} and password = #{password}, so two parameters username and password are required

Run to

Enter setParameter method

Place a breakpoint at the figure and enter the setNonNullParameter method

Continue to mark the breakpoint at the figure and enter the setParameter method

Continue to place a breakpoint at the figure and enter the setNonNullParameter method

Although the method name is the same, it is not the same method. Enter the setString method

The dynamic proxy is used here. In the end, it calls back to jdbc's prestatement, marks a breakpoint at the figure and walks into the

It is found that this setString uses a function for the precompiling of JDBC mentioned above, and the subsequent compilation method is the same as that of JDBC

Hibernate

Two methods of executing statements in Hibernate

Hibernate can use hql to execute SQL statements or directly execute SQL statements. Either way may lead to SQL injection

SQL injection under Hibernate

HQL

hql statement:

String hql = "from People where username = '" + username + "' and password = '" + password + "'";

First, observe the normal login and error login

Normal login:

Hibernate: 
    /* 
from
    People 
where
    username = 'admin' 
    and password = 'admin' */ select
        people0_.id as id1_0_,
        people0_.username as username2_0_,
        people0_.password as password3_0_ 
    from
        users people0_ 
    where
        people0_.username='admin' 
        and people0_.password='admin'
admin

Error login:

Hibernate: 
    /* 
from
    People 
where
    username = 'admin' 
    and password = 'adadawd' */ select
        people0_.id as id1_0_,
        people0_.username as username2_0_,
        people0_.password as password3_0_ 
    from
        users people0_ 
    where
        people0_.username='admin' 
        and people0_.password='adadawd'

It can be found that the difference is that the user name is returned after successful login

Try SQL injection:

Input:

Please enter user name:
admin' or '1'='1
 Please input a password
qwer

return:

Hibernate: 
    /* 
from
    People 
where
    username = 'admin' 
    or '1'='1' 
    and password = 'qwer' */ select
        people0_.id as id1_0_,
        people0_.username as username2_0_,
        people0_.password as password3_0_ 
    from
        users people0_ 
    where
        people0_.username='admin' 
        or '1'='1' 
        and people0_.password='qwer'
admin

It can be found that after splicing, the SQL statement becomes

from People where username = 'admin' or '1'='1' and password = 'qwer'

It shows that using this splicing method, like jdbc and mybatis, will produce sql injection

Correct writing:

Correctly using the following HQL parameter binding methods can effectively avoid injection

Positional parameter

String parameter = "g1ts";
Query<User> query = session.createQuery("from users name = ?1", User.class);
query.setParameter(1, parameter);

named parameter

Query<User> query = session.createQuery("from users name = ?1", User.class);
String parameter = "g1ts";
Query<User> query = session.createQuery("from users name = :name", User.class);
query.setParameter("name", parameter);

named parameter list

List<String> names = Arrays.asList("g1ts", "g2ts");
Query<User> query = session.createQuery("from users where name in (:names)", User.class);
query.setParameter("names", names);

Class instance (JavaBean)

user1.setName("g1ts");
Query<User> query = session.createQuery("from users where name =:name", User.class);
query.setProperties(user1);

SQL

Hibernate supports the use of native SQL statements, so its risk is consistent with JDBC. The direct use of splicing method will lead to SQL injection

The statement is as follows:
Query<People> query = session.createNativeQuery("select * from user where username = '" + username + "' and password = '" + password + "'");

Correct writing

String parameter = "g1ts";
Query<User> query = session.createNativeQuery("select * from user where name = :name");
query.setParameter("name",parameter);