Oracle Database Security, SQL injection of PL/SQL

The following articles mainly talk about Oracle Database Security: PL/SQL injection, I found a good reputation for Oracle database security on a website: PL/SQL Injection information for your sharing. We hope you will have some gains.

SQL injection is an old security issue. If there is SQL, there will be SQL injection. 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 understand 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 Oracle databases.

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 PL/SQL, 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 the 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 Oracle 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;  
  
  
/  
 
 

The above content is the description of Oracle Database Security: PL/SQL injection, hoping to help you in this regard.