Preventing SQL injection: The difference between MyBatis's #{} and ${} and the order by injection problem

#{} is equivalent to PreparedStatement in JDBC

${} is the value of the output variable

You might say it's unclear, so don't worry. Let's look at the 2 snippet code:

String sql = "SELECT * from Admin_domain_location order by?"; PreparedStatement st = con.preparestatement (SQL), st.setstring (1, "domain_id"); System.out.println (St.tostring ());

ResultSet rs = St.executequery ();
while (Rs.next ()) {
System.out.println (rs.getstring ("domain_id"));
}

Output Result:

[Email protected]: SELECT * from Admin_domain_location order by ' domain_id '
3
4
5
2
6

This is a JDBC PreparedStatement example, do not spit groove I write this is legal, here just to illustrate the problem.

The above example has the following information:

1) If the parameter is dynamically entered in the pre-compiled form after the order by, the actual inserted parameter is a string, in the example: Order by ' domain_id '

2) The output is not sorted, and we can infer from the SQL statement that this SQL statement is not legal at all (normally order by domain_id)

Modify the above code as follows:

String input = "domain_id"; String sql = "SELECT * from Admin_domain_location order BY" +INPUT; PreparedStatement st = con.preparestatement (SQL); System.out.println (St.tostring ()); ResultSet rs = St.executequery (); while (Rs.next ()) {    System.out.println (rs.getstring ("domain_id"));}
Output Result:

[Email protected]: SELECT * from Admin_domain_location ORDER by domain_id
2
3
4
5
6

This time we directly splicing the value of a variable SQL statement, from the results can be seen:

1) SQL statement stitching is normal

2) query results sorted correctly

You may have to ask what this and #{} have to do with ${}.

As already said #{} is equivalent to JDBC PreparedStatement, so the first example above is equivalent to #{}, then the second example naturally refers to the case of ${}.

You may say that thinking is still a little messy, don't worry. Let's look at the third example:

String sql = "SELECT * from Admin_domain_location where domain_id=?"; PreparedStatement st = con.preparestatement (SQL), st.setstring (1, "2"); System.out.println (St.tostring ()); ResultSet rs = St.executequery (); while (Rs.next ()) {    System.out.println (rs.getstring ("domain_id"));} =======================================string input = "2"; String sql = "SELECT * from admin_domain_location where domain_id= '" +input+ ""; PreparedStatement st = con.preparestatement (SQL); System.out.println (St.tostring ()); ResultSet rs = St.executequery (); while (Rs.next ()) {    System.out.println (rs.getstring ("domain_id"));} The output is: [Email protected]: SELECT * from Admin_domain_location where domain_id= ' 2 ' 2

This third example refers to the common problem with #{} and ${}, meaning that #{} and ${} are common in this case, but require little conversion. As the example requires manual

Make sure that the SQL statement is normal before and after a concatenation of single quotes ' to the value of the variable.

Simply said #{} is pre-compiled, is secure, and ${} is not pre-compiled, just take the value of the variable, is non-secure, there is SQL injection.

Here is the case of only ${}, from our previous example can also be seen, order by is definitely only with ${}, with #{} will be more than "cause the SQL statement invalidation. There is also a like statement also need to use ${}, simply think about

Can understand. Because ${} is simply a value, the previous method of SQL injection applies here, and if we use the ${} after the order BY statement, there is a risk of SQL injection when nothing is done. You say how to prevent, that I only

Can be tragic to tell you, you have to manually filter the input, such as determine whether the length of the input parameters is normal (the injection statement is generally very long), more accurate filtering can query whether the input parameters in the expected parameter set.

Preventing SQL injection: The difference between MyBatis's #{} and ${} and the order by injection problem