Are you sure SQL injection is dead?

For a long time, I thought that the most common security problem in back-end development was SQL injection. Through the magical SQL writing method where 1=1, you can easily attack a problematic system, and eventually evolve into the existence of an artifact like sqlmap.

The later fastjson refreshed my understanding. This framework can also be regarded as a promotion of the concept of Internet security. Even bosses who don't understand technology know that fastjson is extremely fast, and as a programmer, the safety concept has been improved.

Recommended (free):

sql##Why do you have a soft spot for sql injection? Because there are too many places where developers deal with SQL. Some students who specialize in report development even write more lines of SQL than lines of code!

The issue is. A long time ago, as early as 10 years ago, some people were shouting that SQL injection was dead, but to this day, there are still a large number of SQL injection tutorials and SQL injection cases.

SQL injection is the king of vulnerabilities, this is not a boast.

Of course, in this regard, PHP has made the greatest contribution, and Java is at a disadvantage.

The reason why SQL injection is popular is that developers are too confident in themselves, or the tools they use are too primitive and have not been filtered by the framework layer. If you use MyBatis or JPA in the Java world, the possibility of SQL injection becomes very low. Now PHP also has a framework similar to

thinkphp

, which means that there are fewer and fewer SQL injection vulnerabilities.

But that doesn’t mean there isn’t, it just means the threshold has been raised. Let's take MyBatis as an example to see if SQL injection can still occur.

SQL injection still exists in MyBatis

Students who use Mybatis, the first concepts they come into contact with are

# and

$ difference. These two symbols are very similar to the magic symbols in Shell, but fortunately there are only two situations.

• # represents the use of sql pre-compilation, which is safe and reliable

$
• represents The splicing method is used, and there is a risk of SQL injection
  For example, the following xml configuration is an absolutely safe way of writing. Because the entire
#{id}

will be replaced with

?.

<select id="queryAll"  resultMap="resultMap">
  SELECT * FROM order WHERE id = #{id}
</select>

But unfortunately, in some scenarios, precompilation cannot be used (or you just don't know or are lazy). For example, in some code refactorings, when fields such as table name/column name/sort are dynamically passed in, SQL splicing is inevitably required, and SQL injection still occurs.

But the more likely problems are statements like

LIKE

and

IN. The following is how to write two sentences of Like fuzzy query. In actual testing, it will be found that using

# is not easy to use and an error will be reported. You need to use sql splicing

$ . This is where the problem arises.

SELECT * FROM order WHERE name like '%#{name}%'  //会报语法错
SELECT * FROM order WHERE name like '%${name}%'  //可以运行

The correct way to write it is to use function splicing. But the construction deadline is overwhelming, and without even realizing it, most people choose the simple way of writing. After all, function comes first, and it is also the most important way to reflect workload.

SELECT * FROM order WHERE  name like concat(‘%’,#{name}, ‘%’) //正确的写法

The same problem exists in the

IN

statement.

in (#{tag}) //报错
in (${tag}) //可以运行

Since it can be run with just a few characters, of course no one chooses the complicated writing method below.

tag in
<foreach collection="tag" item="item" open="("separatosr="," close=")">
#{tag} 
</foreach>

Also order by, don’t take it lightly, otherwise you will be doomed.

SELECT * FROM order order by createDate #{sortType} //报错
SELECT * FROM order order by createDate ${sortType} //正常

In this case, you need to whitelist sortType. It’s not just ASC and DESC. You sent me a long string. What’s going on?

Summary

SQL injection still exists in 2021 , but the threshold has been raised. The decrease in SQL injection now is all due to the framework and has nothing to do with the level of programmers. The situation of sql splicing will never go away because it is the fastest and easiest way and will make people addicted to it. There are countless outsourcing projects, and there are many systems that have been lying dormant for more than ten years. It is a dream to hope that SQL injection will be eliminated at the framework layer.

Because its opponent is human laziness. No one can defeat it.

The above is the detailed content of Are you sure SQL injection is dead?. For more information, please follow other related articles on the PHP Chinese website!