"Serial" Database audit product common defects (v) Parameter audit error

Parameter binding is a common method in database programming, in which the database system can reduce the number of compile times, execute quickly and improve efficiency. But this kind of programming method will challenge the audit of database, and in the case of some database audit products I see, there are many errors. Some are missing the statement, there are some records of the operation of the statement, but the specific implementation of the parameters used in the wrong or missing memory. These flaws are undoubtedly deadly for audit products.

To explain this situation, let's take a look at the basic concepts of parameter binding. In regular graphical or command-line tools, we often write SQL statements directly, such as:

Select * from Person_info where id= ' 12xxxxx6722 ';

Here the query condition is * * * number. According to the * * * number to inquire about personal information, is a common function, is also repeated use of the statement, in order to improve efficiency, programming can write:

String sql1= ' Select * from Person_info where id=?; '

PreparedStatement pstmt = Testconn.getconnection (). preparestatement (SQL);

Pstmt.setint (1, ' 12xxxxx6722 ');

Pstmt.execute ();

The next time you re-use, you will not have to send the statement, you can send directly:

Pstmt.setint (1, ' 22xxxxx5399 ');

Pstmt.execute ();

For a database audit system, simply record ' Select * from Person_info where id=? ' is flawed, because you cannot specify exactly which user's information the operator accesses, and you must specify the specific parameters.

This requires that the set parameters be effectively correlated with the prepare statement to form a visual audit record display:

Select * from Person_info where id= ' 12xxxxx6722 ';

Select * from Person_info where id= ' 22xxxxx5399 ';

This actually requires the audit system to do more work than the simple record statement, one of the important tasks is the handle tracking, essentially the SQL statement execution process tracking is the handle tracking process. In the example shown above

Pstmt.execute (), in the communication process does not send a specific statement, but only to inform the server to execute which statement handle, the server side will be based on the internal record of the handle of the compiled SQL statement corresponding to the execution plan of the completed statement execution. Database audit to complete the corresponding work, the need to perform a similar process, in the internal system also maintain such a mapping relationship, and because most of the database handle is at the session level, the handle is reusable, so in the database audit also effectively maintain the relationship between the handle and the session, and the death of the handle.

In addition to handle maintenance, another challenging task is to restore the parameters. The first thing to do is to define the handle to the parameter, and when calling Pstmt.setint (1, ' 22xxxxx5399 '), the packet sent in the network will indicate which handle the parameter is for, and is for the first parameter. As a database audit product, parameters and statements need to be mapped, more importantly to accurately fill in the location of the parameters, the above example because there is only one parameter, there is no challenge. But the binding of parameters is far more complex than this, we will provide some examples to test the relevant database audit products.

use Case 1: a basic example

String sql = "Select Pid,name from Performance_c t where Pid=:1 and Balance>:2 and Persionid>:3 and Datefield>to_ DATE (: 4, ' yyyy-mm-dd ') ";

PreparedStatement pstmt_2 = Testconn.getconnection (). preparestatement (SQL);

Pstmt_2.setint (1, 84);

Pstmt_2.setint (2, 5555);

Pstmt_2.setstring (3, "120");

Pstmt_2.setstring (4, "1900-1-1");

Pstmt_2.execute ();

The key in this example is to see if the audit product can audit the statement as:

Select Pid,name from Performance_c t where pid=84 and balance>5555 and persionid> ' ' and datefield>to_date (' 190 0-1-1 ', ' yyyy-mm-dd ')

use case 2: This is a slightly challenging example, but the challenge already has a database audit product cannot restore parameters

String sql = "Select Pid,name from Performance_c t where pid=:p ID and balance>:balance and personid>:p Ersonid and Da Tefield>to_date (:d Atefield, ' yyyy-mm-dd ') ";

PreparedStatement pstmt_2 = Testconn.getconnection (). preparestatement (SQL);

Pstmt_2.setint (1, 84);

Pstmt_2.setint (2, 5555);

Pstmt_2.setstring (3, "120");

Pstmt_2.setstring (4, "1900-1-1");

Pstmt_2.execute ();

The challenge here is that the parameters do not use the conventional '? ' or ': 1 ', in this way, but in the form of a string, the answer to the verification remains:

Select Pid,name from Performance_c t where pid=84 and balance>5555 and personid> ' ' and Datefield>to_date (' 1900 -1-1 ', ' yyyy-mm-dd ')

use Case 3: This challenge also has a database audit product error

String sql = "Select Pid,name from Performance_c t where Personid>:1 and Pid=:2 and Balance>:3 and personid< 32 + : 1;

PreparedStatement pstmt_2 = Testconn.getconnection (). preparestatement (SQL);

Pstmt_2.setint (1, "120");

Pstmt_2.setint (2, 84);

Pstmt_2.setint (3, 5555);

Pstmt_2.setint (4,84);

Pstmt_2.execute ();

The challenge here is that parameter 1 and parameter 4 use the same parameter number, and the answer is:
Select Pid,name from Performance_c t where personid>120 and pid=84 and balance>5555 and PersonID < 32 + 120;

use case 4: If you simply use ': ' to split the parameter restore will definitely error

String sql = "Select Pid,name from Performance_c t where Pid>:1 and PersonID =:2 and the content like ' name:3% ' and balance >:3 ";

PreparedStatement pstmt_2 = Testconn.getconnection (). preparestatement (SQL);

Pstmt_2.setint (1, 84);

Pstmt_2.setstring (2, "120");

Pstmt_2.setstring (3, 5555);

Pstmt_2.execute ();

The answer to the verification is:

Select Pid,name from Performance_c t where personid>120 and pid=84 and content like ' name:3% ' and balance>5555;

use case 5: the challenge is to verify that the executed statements and corresponding parameters are audited accurately under a multi-prepared statement, where we will provide two statements for interactive execution:

Public void Testcase_prepareselect_3 ()

{

String sql_1 = "Select Pid,balance,account from F10_user t where pid=:p ID";

String sql_2 = "Select Name,pid,balance from Performance_c t where PID=:1 and

Balance>:2 and Persionid>:3 and Datefield>to_date (: 4, ' yyyy-mm-dd ') ";

String sql_2 = "Select Name,pid,balance from F10_user t where Pid=:1 Andbalance>:3 and Persionid>:2 and Datefield> ; To_date (: 4, ' yyyy-mm-dd ') ";

Try {

pstmt = Testconn.getconnection (). Preparestatement (Sql_1);

Pstmt_2 = Testconn.getconnection (). Preparestatement (sql_2);

First round of execution

Pstmt_2.setint (1, 94);

Pstmt_2.setint (2, 5555);

Pstmt_2.setstring (3, "120");

Pstmt_2.setstring (4, "1900-1-1");

Pstmt_2.execute ();

Testrest_2 = Pstmt_2.getresultset ();

while (Testrest_2.next ())

{

PID = Testrest_2.getint ("pid");

System. out. println ("pid=" +pid);

}

Pstmt.setint (1, 84);

Pstmt.execute ();

Testrest = Pstmt.getresultset ();

while (Testrest.next ())

{

PID = Testrest.getint ("pid");

System. out. println ("pid=" +pid);

}

2nd Round of execution

Pstmt.setint (1, 85);

Pstmt.execute ();

Testrest = Pstmt.getresultset ();

while (Testrest.next ())

{

PID = Testrest.getint ("pid");

System. out. println ("pid=" +pid);

}

Pstmt_2.setint (1, 95);

Pstmt_2.setint (2, 1555);

Pstmt_2.setstring (3, "1305");

Pstmt_2.setstring (4, "1920-1-1");

Pstmt_2.execute ();

Testrest_2 = Pstmt_2.getresultset ();

while (Testrest_2.next ())

{

PID = Testrest_2.getint ("pid");

System. out. println ("pid=" +pid);

}

3rd Round of execution

Pstmt_2.setint (1, 96);

Pstmt_2.setint (2, 5123);

Pstmt_2.setstring (3, "1201");

Pstmt_2.setstring (4, "1930-1-1");

Pstmt_2.execute ();

Testrest_2 = Pstmt_2.getresultset ();

while (Testrest_2.next ())

{

PID = Testrest_2.getint ("pid");

System. out. println ("pid=" +pid);

}

Pstmt.setint (1, 86);

Pstmt.execute ();

Testrest = Pstmt.getresultset ();

while (Testrest.next ())

{

PID = Testrest.getint ("pid");

System. out. println ("pid=" +pid);

}

Testrest.close ();

Pstmt.close ();

} catch (SQLException e) {

E.printstacktrace ();

}

}

It should be noted that this is an uncomplicated multi-statement execution example, but a well-known manufacturer of database audit products, will be sql_1 audited to 4 times, Sql_2 was audited to 1 times. This is just a simple multi-statement implementation, and our real-world applications are more complex.

You can access this link, which will provide a more complete example of parameter binding, we can test the database audit products used by the accurate audit: http://www.dbsec.cn/a/shujukuanquan/ Shujukufanghushouduan/shujukushenj/index.html

We will no longer discuss these simple scenarios, and we'll discuss a more complex parameter binding scenario, which is bulk parameter binding.

The batch parameter binding is the DBMS provider in order to provide more efficient data operation speed, and provide a batch of parameters sent, the statement executes multiple times, the method through less network communication times, less SQL layer of the context switch, etc. greatly improve programming efficiency. This type of execution is seldom used in general programming, but in an environment similar to that of a carrier's billing system, where performance requirements are high and often require nearly tens of thousands of inserts or updates per second, it is a more common way to use it, so the instantaneous trading volume is very large for operators, securities finance, the Internet, The application environment that requires the most efficient data processing needs to be compatible with this parameter binding approach.

The following is an example of a bulk INSERT, just to verify that the audit product supports this feature:
PreparedStatement statement = connection.preparestatement ("INSERT into Tbl1 VALUES (?,?)");
Record 1
Statement.setint (1, 1);
Statement.setstring (2, "Cujo");
Statement.addbatch ();

Record 2
Statement.setint (1, 2);
Statement.setstring (2, "Fred");
Statement.addbatch ();

Record 3
Statement.setint (1, 3);
Statement.setstring (2, "Mark");
Statement.addbatch ();

Executes the above 3 statements in bulk.
int [] counts = Statement.executebatch ();

Now we need to examine the database audit products used, can accurately audit the next 3 INSERT statements and each statement can accurately match the corresponding parameters, or audit 1 INSERT statements, 3 sets of parameters.

This is only a slight challenge in bulk insertion, and more complex is when the larger case of the batch, Oracle and SQL Server more granular products, the data will be compressed processing, such as the expression of duplicate values, the expression of null values, etc. It will be a challenge for the database audit product to effectively restore the value of each batch member.

At present, most of the database audit products we see in the case of batch parameter binding execution statement, can not accurately audit the following batch query executed statements and corresponding parameters.

This article is from the Database security blog, so be sure to keep this source http://schina.blog.51cto.com/9734953/1692787

"Serial" Database audit product common defects (v) Parameter audit error