Go Discussion on SQL injection vulnerability and binding variables

1, a problem caused by thinking

We discussed a problem in the group, wrote a composition in the life of Wen Yu, and read:

String user_web = "User_web"
String sql = "Update user set user_web=" +user_web+ "where userid=2343";

Let's see if there's a problem with this SQL, what will the User_web field be updated to?

The problem is that the resulting record is the same as before execution (the SQL statement at the time of execution is

Update user set User_web=user_web where userid=2343,

The User_web field value is update to its original value), which is difficult to detect because of the author's intention to violate it. The original statement omitted a pair of single quotation marks, the correct wording should be:

String sql = "Update user set user_web= '" +user_web+ "' where userid=2343";

In this way, the value of the variable is passed to the SQL statement, which is intended to be achieved, but not a good one, for the following reasons:

1. Poor readability. Single quote double quotation mark (imagine a case with multiple variables, and then think about what would happen if a little bit of double quotation marks in the front of the single quotation mark straight between them)? ）

2. Potential performance issues and SQL injection vulnerabilities (two points may not be required for test code, but it is important to develop good coding practices)

The following is a non-professional perspective of the "+ Variable +" ' (not in the bound variable mode) this way to organize SQL why can cause potential performance problems and SQL injection Vulnerability issues

2, performance issues

The way that SQL code does not adopt bound variables can cause performance problems in the following two ways:

1. Causes the same test plan to be executed repeatedly

The execution of SQL statements takes several steps: grammar checking, parsing, execution, and returning results. When a SQL is checked by syntax, it looks for the same statement in the shared pool, executes the SQL statement with an existing execution plan, and generates an execution plan if it is not found, then executes the SQL statement. As you can see, the latter has additional steps, consumes additional CPUs, and causes SQL overall execution time to be extended, and the key here is "is there an identical SQL statement in the shared pool"?

String username= "Test_xx";
String sql = "Select Id,nick from user WHERE username= '" +username+ "";

The SQL that is passed to the database in this way is

SELECT id,nick from user WHERE username= ' test_xx '

Assuming that the statement is executed for the first time, an execution plan is generated. When a variable changes (username= "Test_yy"), the database receives such a statement

SELECT id,nick from user WHERE username= ' test_yy '

Oracle does not consider the above two statements to be the same, and therefore generates an execution plan that is the same as the execution plan (doing repetitive work)

2. Causes too many SQL statements in the shared pool to accelerate SQL aging, resulting in frequent maintenance of the internal structure of the shared pool.
If a segment of a program is not in the way of binding variables and is heavily called, it will result in a larger number of different SQL statements in the shared pool, and very low reusability, resulting in a drop in the share pool. As the number of SQL is too large, some of the statements gradually age and are eventually cleaned out of the shared pool. Maintaining the internal structure of the shared pool consumes a lot of CPU and memory resources.

　　3. SQL Injection Vulnerability

The way that binding variables are not used can cause SQL injection vulnerabilities, and this article simply illustrates why SQL injection is a vulnerability that does not unfold, such as attack patterns, attack types, and so on. Take one user authentication as an example.

String sql = "Select Id,nick from user WHERE username= '" +username+ "' and password= '" +password+ "'";

The above code receives the username and password variables from the client and queries the database for validation. Suppose an attacker username an arbitrary value (such as test) from the client to a password variable
1′or ' 1′= ' 1
At this point, the SQL after the replacement variable becomes

SELECT id,nick from user WHERE username= ' test ' and password= ' 1 ' or ' 1 ' = ' 1 '

The result is all the data in the user table.

4. Using Bound variables

The solution to both of these problems is to use a binding variable, that is, in the SQL statement does not directly write the variable, but with a placeholder, at execution time and then replace the placeholder with the specific value of the variable. The code snippet is as follows

String sql = "Select Id,nick from User WHERE username=?" and password=? ";
Preparedstatement.setstring (1,username);
Preparedstatement.setstring (2,password);

Some commonly used JDBC tools encapsulate this well, making the code more concise. Like spring's simplejdbctemplate.

String sql = "Select Id,nick from User WHERE username=?" and password=? ";
Jdbctemplate.queryforlist (Sql,username,password);

Above? The form of placeholder is called the sequential placeholder, which must be taken into account in order to pass the parameter value, and there is a name placeholder. Also take simplejdbctemplate as an example to illustrate.

String sql = "Select Id,nick from User WHERE username=:name and password=:p";
Map.put ("Pass", password);
Map.put ("name", username);
Jdbctemplate.queryforlist (SQL,MAP);

The example above: Name and: Pass is the name placeholder, and the variable is then bound when SQL is executed.
There are two placeholders in Ibatis, #name # and $name$ two ways to be aware that the former binds variables when executing SQL, which is replaced directly with variable values, so the latter still has a SQL injection vulnerability issue.

5. No topic

Interface testing do you want to check for SQL injection vulnerability issues? This question is debatable, and I think it might not be feasible to check for SQL injection vulnerabilities through regular use case design (too much work is not necessarily good), and if you want to do this, you can use (or develop) some tools to scan static code and then manually troubleshoot. In addition, if the work is too detailed, I am afraid that the work of the security test overlap too much, of course, if the test process found that the development of the Code of SQL Injection Vulnerability (which is often related to the developer coding habits) problem, do not let go, troubleshooting or is necessary.

Transferred from: http://kb.cnblogs.com/page/55287/

Go Discussion on SQL injection vulnerability and binding variables