Do I have to worry about SQL injection when using java PreparedStatement?

Do I have to worry about SQL injection when using java PreparedStatement?

First, I felt that I had not written a blog for a long time. First, I was too busy at work, and second, I was not very good at my health. Fortunately, I finally found out the cause of the problem. Now I am idle and can't wait to communicate with readers, the last piece of advice: The body is the cost of living!

To put it bluntly, students who know java have basically experienced JDBC and have basically understood PreparedStatement. Compared with Statement, PreparedStatement basically solves the SQL Injection problem and improves the efficiency.

We will not discuss other details about PreparedStatement and Statement, but only about injection. Whether the reader is an old bird or a cainiao, you need to ask yourself, is the PreparedStatement actually completely protected against injection?

Next, we will study how PreparedStatement prevents injection. This article takes the MySQL database as an example.

To avoid excessive length, I only paste code snippets here, hoping that readers can have a certain foundation.

String SQL = "select * from goods where min_name =? "; // Contains the PreparedStatement st = conn. prepareStatement (SQL); st. setString (1, "child"); // The parameter value is System. out. println (st. toString (); // com. mysql. jdbc. JDBC4PreparedStatement @ d704f0: select * from goods where min_name = 'kid'

This code is common knowledge of JDBC. It is simply based on the parameter query and cannot be found. But what if someone breaks down and wants to inject it?

String SQL = "select * from goods where min_name =? "; // Contains the PreparedStatement st = conn. prepareStatement (SQL); st. setString (1, "children"); // The parameter value is System. out. println (st. toString (); // com. mysql. jdbc. JDBC4PreparedStatement @ d704f0: select * from goods where min_name = 'children \''

Simply add a single quotation mark behind the parameter to quickly determine whether SQL injection can be performed. If there is a vulnerability, an error is reported.

The reason why PreparedStatement can prevent injection is that it escapes single quotes and turns them into \ '. In this way, the SQL statement cannot be truncated, and then the SQL statement cannot be spliced. Basically, there is no way to inject the statement.

Therefore, if you do not need PreparedStatement and want to prevent injection, the simplest and most crude method is to filter single quotes. After filtering, injection cannot be performed from the SQL perspective.

In fact, we just mentioned the injection of the String parameter type. Most of the injection still occurs in the numeric type. Fortunately, PreparedStatement provides us with st. setInt (1,999); this value assignment API avoids injection, because if the input is not a numerical value type, an error is returned during type conversion.

Now, the reader understands that PreparedStatement will escape the parameters. Next, let's look at another example.

String SQL = "select * from goods where min_name =? "; // Contains the PreparedStatement st = conn. prepareStatement (SQL); st. setString (1, "Children %"); // The parameter value is System. out. println (st. toString (); // com. mysql. jdbc. JDBC4PreparedStatement @ 8543aa: select * from goods where min_name = 'children %'

We tried to enter a percent sign and found that the PreparedStatement is not escaped. The percent sign is exactly the wildcard of the like query.

Normally, like queries are written like this:

String SQL = "select * from goods where min_name like? "; // Contains the parameter st = conn. prepareStatement (SQL); st. setString (1, "child" + "%"); // The parameter value is System. out. println (st. toString (); // com. mysql. jdbc. JDBC4PreparedStatement @ 8543aa: select * from goods where min_name like 'children %'

Query all records starting with "children" in the min_name field. The word "children" is the query condition entered by the user. we add the percent number by ourselves. How can we allow the user to enter the percent number! Wait! If the user is very smart, enter the percentage sign?

String SQL = "select * from goods where min_name like? "; // Contains the parameter st = conn. prepareStatement (SQL); st. setString (1, "% Children %" + "%"); // The parameter value is System. out. println (st. toString (); // com. mysql. jdbc. JDBC4PreparedStatement @ 8543aa: select * from goods where min_name like '% Children %'

The clever user directly entered "% Children %", and the meaning of the entire query changed to include query. In fact, you don't have to worry about it. If you do not enter anything or enter only one %, you can change the original intention.

Although such SQL injection is not harmful, such queries consume system resources and evolve into DoS attacks.

How can we prevent it? The solution I can think of is as follows:

Directly concatenate SQL statements and perform all escape operations by yourself. This method is troublesome and may not be as good as PreparedStatement, resulting in other larger vulnerabilities and is not recommended.

Simply and violently filter out %. I think this solution is good. If there are no strict restrictions, users can enter anything as needed. Since there are restrictions, we should simply be strict, so that users are not allowed to search for %, which is recommended.

Currently, as long as they are not very poor companies, they generally have their own search engines (such as the famous java open-source search engine solr), and few like them directly in the database, I don't want to get on top of the like, but remind readers to be good at thinking. I write repeated code every day, but never stop to savor it.

Some readers may ask, why can't we manually escape the % entered by the user, and give the rest to the PreparedStatement escape? Leave this question for consideration and try it to find out why.

Note that JDBC is only a specification defined in java and can be understood as an interface. Each database must have its own implementation. The implementation is generally called a database driver. The PreparedStatement involved in this article, it is implemented by MySQL and is not the default behavior implemented by JDK. That is to say, different databases have different performance and cannot be generalized.