Do you need to worry about SQL injection with Java PreparedStatement?

The first feeling under, a long time did not write a blog, one is too busy work, the second is the body is not too to force, but finally to find out the cause, while today idle, eager to communicate with the reader, the final advice: the body is the cost of living!

To get to the point, Java has a knowledge of the students have basically experienced JDBC, basic understanding of preparedstatement,preparedstatement compared to statement basically solve the problem of SQL injection, but also a certain increase in efficiency.

We do not discuss other details about PreparedStatement and statement, but only about the injection problem. Whether the reader is a veteran or rookie, you need to ask yourself, preparedstatement really hundred anti-injection it?

Next we look at how PreparedStatement prevents injection, this article takes the MySQL database as an example.

In order to avoid too long, I only put a code snippet here, I hope the reader can have a certain foundation.

1 String sql = "SELECT * from goods where min_name =?";  // contains parameters 2 PreparedStatement st = conn.preparestatement (sql); 3 // parameter Assignment 4 // [Email protected]: SELECT * from goods where min_name = ' children '

This code belongs to the JDBC Common sense, is simply based on the parameter query, see what clues, but if someone bad, want to inject it?

1 String sql = "SELECT * from goods where min_name =?";  // contains parameters 2 PreparedStatement st = conn.preparestatement (sql); 3 // parameter Assignment 4 // [Email protected]: SELECT * from goods where min_name = ' children \ '

Simple after the argument with a single quotation mark, you can quickly determine whether SQL injection can be, the Hundred test lark, if there is a loophole, the general will error.

The reason why PreparedStatement can prevent injection is because it escapes the single quotation mark, and becomes the \ ', so that the SQL statement cannot be truncated, so that the SQL statement cannot be spliced, there is basically no way to inject.

Therefore, if you do not need to preparedstatement, and want to prevent injection, the simplest way is to filter single quotes, filtered, simply from the SQL point of view, can not be injected.

In fact, what we just mentioned is the injection of the string parameter type, most of the injection, or the numeric type, fortunately PreparedStatement provides us with st.setint (1, 999); This numeric parameter assignment API basically avoids injection, Because if the user input is not a numeric type, the type conversion when the error.

Well, now that the reader has learned that PreparedStatement will escape the parameters, let's look at an example.

1 String sql = "SELECT * from goods where min_name =?";  // contains parameters 2 PreparedStatement st = conn.preparestatement (sql); 3 // parameter Assignment 4 // [Email protected]: SELECT * from goods where min_name = ' Child% '

We tried to enter a percent semicolon and found that the PreparedStatement was not escaped, and that the percent semicolon happened to be a wildcard for the like query.

Under normal circumstances, like queries are written as follows:

1 String sql = "SELECT * from goods where min_name like?";  // contains parameters 2 st = conn.preparestatement (sql); 3 // parameter Assignment 4 // [Email protected]: SELECT * from goods where min_name like ' child% '

Query the Min_name field to start with "children" all records, where the word "children" is the user input query criteria, percent semicolon is our own addition, how can let the user enter a percent sign! Wait a minute! If the user is very smart, do you want to enter the percent sign?

String sql = "SELECT * from goods where min_name like?";  // contains the parameter st = conn.preparestatement (sql); st.setstring (//  parameter Assignment //[email Protected]: SELECT * from goods where min_name like '% child percent '

Smart users directly entered the "% child%", the entire query meaning changed, into the inclusion of the query. In fact, no such trouble, the user does not enter anything, or just enter a%, can change the original intent.

Although this type of SQL injection is less harmful, this query can run out of system resources and evolve into a denial of service attack.

So how to guard against it? The solution I can think of is as follows:

          · Directly splicing the SQL statement, and then implement all the escape operation. This method is more troublesome, and probably not preparedstatement done well, resulting in other larger loopholes, not recommended.

          · Direct simple violent filtering off%. I think this program is good, if there is no strict restrictions, any user how to input, since there are restrictions, simply strict some, simply do not let users search%, recommended.

Currently do a search, as long as not too bad companies, generally have their own search engines (such as the famous Java Open source search engine SOLR), very few in the database directly like, I do not want to look at the same way, but to remind the reader good at thinking, every day is written repeating code, But never stopped to savor.

Some readers may ask, why can't we manually escape the user input%, and then give the other to PreparedStatement escape? This is left as study questions, try it out and know why.

Note that JDBC is only Java-defined specification, can be understood as an interface, each database must have its own implementation, after implementation is generally called the database driver, the preparedstatement involved in this article is implemented by MySQL, is not the default behavior of the JDK implementation, that is, Different databases behave differently and cannot be generalize.

Do you need to worry about SQL injection with Java PreparedStatement?