Analysis of Oracle SQL injection and PL Injection

Oracle SQL injection is an old security issue. Generally, enterprise applications only focus on Java-level writing specifications, such as preparedStatement, or directly filter out dangerous characters. In fact, when compiling PL/SQL functions or procedure, there are also injection problems. Let's discuss it briefly.

For example, a procedure function is used to disable the constraint of a table:

SQL code

Create or replace procedure Disable_Constraint (p_constraint_name VARCHAR2, p_table VARCHAR2)

AUTHID CURRENT_USER

AS

P_schema VARCHAR2 (32): = USER;

SQL _stmt VARCHAR2 (2000): = 'alter table'

| P_schema

| '.'

| P_table

| 'Disable constraint'

| P_constraint_name;

BEGIN

Execute immediate SQL _stmt;

END;

/

If you know about Oracle SQL injection, you can see that procedure has several dangerous variables:
1. p_table
2. p_constraint_name
3. p_schema

The first two are easy to find, but why is there p_schema? Because the current USER name may also be a dangerous string constructed by the USER. Well, according to the general rule, we should follow the order below:
1. Static SQL. Do not use variables.
2. Bind the variable. Similar to the PreparedStatement in Java, data is stored in variables instead of directly concatenated in SQL and directly used by the database.
3. Check the value of the variable.

Obviously, the first two methods are not applicable to this procedure. We can only use the worst 3rd methods. Fortunately, for Oracle SQL injection and PL injection, we do not need to write complicated character judgments by ourselves. Oracle has a SYS. DBMS_ASSERT package that provides some preset functions, as shown below:

NOOP No Operation. Returns string unchanged

SIMPLE_ SQL _NAME Verify that the input string is a simple SQL name.

QUALIFIED_ SQL _NAME Verify that input string is a qualified SQL name.

SCHEMA_NAME This function verifies that the input string is an existing schema name.

SQL _OBJECT_NAME This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.

ENQUOTE_NAME This function encloses a name in double quotes.

ENQUOTE_LITERAL Add leading and trailing single quotes to a string literal.

When executing these functions, if the input variables do not meet the specified format or conditions, an exception will be thrown to protect our procedure from SQL injection.

We determine whether these methods are available:

1. SIMPLE_ SQL _NAME, QUALIFIED_ SQL _NAME

These methods require that the input and output parameters are a valid SQL name. For example, if a table is named "Table One", the input value must contain double quotation marks. There is a problem with using these methods. The table name directly read from data-dictionary does not contain double quotation marks. If you directly read the table name from data-dictionary and pass in procedure directly, an exception is thrown because it does not meet the requirements of simple SQL name, but the table name should be correct. Therefore, you cannot directly use these functions.

2. SCHEMA_NAME, SQL _OBJECT_NAME

These methods require that the input parameter value be the name of an existing object in the database. If there is a table named "Table One" in the database, it is considered correct if the user passes in Table One. Using these methods avoids the data-dictionary problem of the first method, and can also avoid issues similar to table. However, there is a so-called secondary attack. If you create a table that contains dangerous characters in advance and then call our procedure, it will still cause SQL injection.

3. ENQUOTE_NAME, ENQUOTE_LITERAL

These methods enclose the parameter values in double quotation marks or single quotation marks. If the enclosed values are dangerous, an exception is thrown. For procedure, we only need to use ENQUOTE_NAME. ENQUOTE_NAME requires two parameters: one is the variable that requires enquote, and the other is whether to convert to uppercase. For procedure, ENQUOTE_NAME (p_table, FALSE) should be used to ensure that Table One is not converted to "table one ".

This is our final solution. However, since ENQUOTE_NAME is used, table and constraint names are case sensitive for procedure. If the name is table_1, TABLE_1 must be input; otherwise, an error is returned.

The modified code is as follows:

SQL code

Create or replace procedure Disable_Constraint (p_constraint_name VARCHAR2, p_table VARCHAR2)

AUTHID CURRENT_USER

AS

P_schema VARCHAR2 (32): = SYS. DBMS_ASSERT.ENQUOTE_NAME (USER, FALSE );

SQL _stmt VARCHAR2 (2000 );

Safe_table VARCHAR2 (32 );

Safe_constraint VARCHAR2 (32 );

BEGIN

Safe_table: = SYS. DBMS_ASSERT.ENQUOTE_NAME (p_table, FALSE );

Safe_constraint: = SYS. DBMS_ASSERT.ENQUOTE_NAME (p_constraint_name, FALSE );

SQL _stmt: = 'alter table'

| P_schema

| '.'

| Safe_table

| 'Disable constraint'

| Safe_constraint;

Execute immediate SQL _stmt;

END;

/

1. What is unknown about Oracle performance diagnosis?
2. How to remotely connect to the mongoel 9i Database Using dtu
3. Install Oracle9i and 10g on Windows2000 Server
4. Code example of Oracle multi-Table query optimization
5. Analyze the default port number modified by Oracle