Shulou(Shulou.com)05/31 Report--

This article introduces how to inject SQL from the perspective of Java. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Preface shooting range preparation

First of all, let's prepare a web interface service, which can provide the administrator's information query. Here we use springboot + jersey to build the web service framework, and the database uses the most commonly used mysql. Next, let's prepare the test environment, first create a user table jwtk_admin,SQL as follows:

Then insert the default administrator:

In this way, we have two built-in administrators, the administrator password uses MD5 to Hash, of course, this is a very simple table for research shooting range, so there are no very complete fields.

Next, we create the RESTFul web service built by spring boot + jersey. Here, we provide an API to query the administrator's specific information through the administrator's user name, as follows:

SQL injection test

First of all, we send an administrator query request to the web service as the developer is thinking. Here we use the PostMan tool to send a GET request.

As we and the developers expected, the Web interface returned the desired result, the administrator information with the user name admin. OK, now that the development task is complete and the Git Push,Jira task point is to be tested, is there really no problem with such an interface? Now we send a GET request like this:

After sending the request, we found that PostMan did not receive the returned result, but the Web service backend began to throw a MySQLSyntaxErrorException exception. The error is as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near''xxxx''' at line 1

The reason is that the syntax of the sql statement at the xxxx' we queried is incorrect. Instead of discussing the SQL syntax here, let's continue the experiment and construct a GET query request again:

At this point, we can be surprised to find that instead of reporting an error, the query interface queried all the administrator information in our database jwti_ admin table:

What the heck is this? are there any name=xxxx'or'a'='a users in the administrator table? this is SQL Injection.

Analysis of injection principle

A name parameter of type String is accepted in the interface, and the query statement is constructed by string concatenation. Under normal circumstances, users will pass in a legitimate name for query, but hackers will pass carefully constructed parameters. As long as the parameter is still a legitimate SQL query after string concatenation, SQL injection occurs. As we entered above, name=xxxx'or'a'='an is concatenated with the query statement in our API to form the following SQL statement:

When the interface executes this SQL, the system background is equivalent to handing it over to the hacker. As soon as the hacker sees the administrator password this hash, he does not have to go to cmd5 to check it, and directly uses the 123456 password to log in to your background system. Why? Because 123456 md5 hash is too common, don't laugh, this is the reality of many small and medium-sized websites, weak passwords run rampant, do not cry until you see the coffin!

Well, now we should understand that the reason for SQL Injection is that the passed parameters are spliced with the SQL of the system into a legal SQL, and its essence is to execute the data entered by the user as code. In the system, as long as a SQL injection point is found by the hacker, the hacker can basically execute any SQL statement that he wants to execute, such as adding an administrator, querying all tables, or even "taking off his pants" and so on. Of course, this article is not an article on SQL injection techniques, here we only discuss the causes of SQL injection and preventive measures.

Pretreatment of JDBC

In the above API, DAO uses the basic JDBC method for database operation, so it is common for JDBC to build DAO directly in older systems, but this does not mean that using JDBC is necessarily unsafe. If I use the passed parameter xxxx'or'a'='an as a parameter for name query, then SQL injection will not be generated. In JDBC, a way of PreparedStatement (preprocessing execution statement) is provided to parameterize the query of the SQL statement. The code after preprocessing is as follows:

Again, we used the injection method above, and at this point we found that the SQL injection did not succeed. Now, let's print the preprocessed SQL and see what has changed:

Did you see that? All 'were' escaped, thus ensuring that the query parameters of SQL are parameters and will not be executed maliciously, thus preventing SQL injection.

Injection Prevention under Mybatis

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping, avoiding almost all JDBC code and manually setting parameters and getting result sets. At the same time, MyBatis can use simple XML or annotations for configuration and native Map to map interfaces and Java's POJOs (Plain Old Java Objects, ordinary Java objects) to records in the database, so mybatis is now very popular in the market. Here, we define the following mapper to implement the API for querying administrators through user names:

Web access interface is also provided:

Next, we try to inject the name field into SQL, and we can see that the injection is not successful. By printing the Log of mybatis, we can see that the mybatis framework preprocesses the parameters, thus preventing injection:

Is it possible to avoid the danger of SQL injection as long as you use mybatis? We modify the mapper as follows, change the parameter # {name} to ${name}, and use name='xxxx' or 'ainjectionrooma' as the parameter of the GET request to see whether SQL injection has occurred:

So why, what's the difference between mybatis ${} and # {}?

It turns out that if parameters are passed to SQL as formal declaration in mybatis, mybatis will not preprocess the parameters and will directly dynamically splice SQL statements, so there is a risk of injection, so when using mybatis as a persistence framework, we should try to avoid using the

If formal declaration is used to pass parameters to SQL, mybatis will not preprocess parameters and will directly dynamically splice SQL statements, so there is a risk of being injected. Therefore, when using mybatis as a persistence framework, you should try to avoid passing parameters as SQL by formal declaration.

Mybatis will not perform parameter preprocessing and will dynamically splice SQL statements directly, so there is a risk of being injected. Therefore, when using mybatis as a persistence framework, you should try to avoid passing parameters in the form of {}. If it is unavoidable (programmers may still choose ${} to pass parameters in some SQL, such as like, in, order by, etc.), then you need to escape and filter the incoming parameters.

JPA injection prevention

JPA is the Java Persistence API (java persistence layer API) defined by Sun to integrate ORM technology and achieve the unified ORM standard. JPA is just a set of interfaces. At present, projects that introduce JPA will use Hibernate as its specific implementation. With the popularity of the unconfigured Spring Boot framework, JPA has increasingly become the preferred technology for persistence, because it allows programmers to write less code to complete the existing functions.

For example, powerful JpaRepository, conventional SQL queries only need to define interfaces according to naming rules, and you can query data without writing SQL (JPQL/SQL). From the point of view of SQL injection prevention, this kind of security responsibility to the framework is far better than the insurance that depends on the programmer's own control. Therefore, if the project uses JPA as the data access layer, the risk of SQL injection can be largely eliminated.

But you can't go too far. In a Spring Boot project I've seen, although JPA is used as a persistence framework, there is an old programmer who is not familiar with using JPQL to build query interfaces, but still uses string concatenation to achieve business, which lays a hidden danger for project security.

Security needs to be meticulous, security is 100-1 = 0 business, even if you defend 99% of the attacks, that is not a victory, as long as there is one intrusion, it may bring serious consequences to the company.

On JPA SQL injection, we will not discuss in detail, because the framework injection vulnerabilities belong to the framework vulnerability category (such as CVE-2016-6652), programmers as long as follow the JPA development specifications, do not have to worry about injection, the framework has done a good job for you behind the scenes.

Other precautions against SQL injection

Many companies will have the problem that there are a large number of SQL injection risk codes in the old system, but because they have steadily supported the company's business for a long time, it is not suitable to use large-scale code updates to eliminate the hidden dangers of injection, so it is necessary to consider other ways to prevent SQL injection. In addition to preventing SQL injection in the SQL execution mode, it is often possible to prevent SQL injection through architecture or other filtering methods.

All input is unsafe: format matching should be performed for the calling parameters of the interface, such as admin's interface queried through name, and the matching Path should use regular matching (because there should be no special characters in the user name), thus ensuring that the incoming parameters are within the scope of the program control, that is, only the known good input values are accepted and bad inputs are rejected. Note: the validation parameter should be used in conjunction with the output coding technique.

Use hierarchical design to avoid danger: the front end should be static as far as possible, and the interfaces that can access the DAO layer can be exposed to the public network environment as little as possible. if it is difficult to modify the code that is injected, you can consider adding WAF to filter traffic before the web service. Of course, the best solution is to leave hacker with no attack loopholes in the code. You can also use OpenRestry to filter traffic and escape some special characters under the framework of nginx.

Use precompiled SQL statements whenever possible: because dynamic SQL statements are the source of SQL injection. Precompiled statements should be used to assemble SQL queries.

Standardization: input installation prescribed coding and decoding is followed by input parameter filtering and output coding processing; all non-standard format coding is rejected.

In fact, with the development of ORM technology, Java web development has become more and more away from the problem of SQL injection in the general trend, and ASP.NET MVC, which has the support of Entity Framework framework, has always been a cold model. In the current Internet, the web application built with PHP and Python is the hardest hit area of SQL injection.

So much for sharing on how to do SQL injection from the perspective of Java. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.