Oracle SQL injection(SQL注入)

Oracle SQL注入是一种网络安全漏洞，它允许攻击者在Oracle数据库驱动的Web应用程序中插入或“注入”恶意的SQL代码。这种攻击通常发生在应用程序未能正确验证或清理用户输入的数据时，从而允许攻击者操纵数据库查询，进而获取、修改或删除敏感信息。以下是对Oracle SQL注入的详细分析：

参考官方文档地址如下：

https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/dynamic-sql.html#GUID-4503110E-DF12-487E-B613-6890CC55B6CD

一、Oracle SQL注入的原理

SQL注入的原理在于攻击者通过向应用程序的输入点（如表单、URL参数等）提交恶意的SQL代码片段，这些代码片段会被应用程序的数据库查询语句所接纳并执行，从而改变了原始查询的逻辑，实现了攻击者的目的。
SQL injection maliciously exploits applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database to view or manipulate restricted data.

This section describes SQL injection vulnerabilities in PL/SQL and explains how to guard against them.

Topics

SQL Injection Techniques
Guards Against SQL Injection

二、Oracle SQL注入的攻击方式

2.1、Setup for SQL Injection Examples

sql">DROP TABLE secret_records;
CREATE TABLE secret_records (user_name    VARCHAR2(9),service_type VARCHAR2(12),value        VARCHAR2(30),date_created DATE
);INSERT INTO secret_records (user_name, service_type, value, date_created)
VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE);INSERT INTO secret_records (user_name, service_type, value, date_created)
VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);

Oracle SQL注入的攻击方式多种多样，但主要可以分为以下几类：

All SQL injection techniques exploit a single vulnerability: String input is not correctly validated and is concatenated into a dynamic SQL statement.

Topics

Statement Modification
Statement Injection
Data Type Conversion

2.2、Statement modification

Statement modification means deliberately altering a dynamic SQL statement so that it runs in a way unintended by the application developer.

Typically, the user retrieves unauthorized data by changing the WHERE clause of a SELECT statement or by inserting a UNION ALL clause. The classic example of this technique is bypassing password authentication by making a WHERE clause always TRUE.

Example-1 Procedure Vulnerable to Statement Modification

This example creates a procedure that is vulnerable to statement modification and then invokes that procedure with and without statement modification. With statement modification, the procedure returns a supposedly secret record.

sql">CREATE OR REPLACE PROCEDURE get_record (user_name    IN  VARCHAR2,service_type IN  VARCHAR2,rec          OUT VARCHAR2
) AUTHID DEFINER
ISquery VARCHAR2(4000);
BEGIN-- Following SELECT statement is vulnerable to modification-- because it uses concatenation to build WHERE clause.query := 'SELECT value FROM secret_records WHERE user_name='''|| user_name || ''' AND service_type=''' || service_type || '''';DBMS_OUTPUT.PUT_LINE('Query: ' || query);EXECUTE IMMEDIATE query INTO rec ;DBMS_OUTPUT.PUT_LINE('Rec: ' || rec );
END;
/

Demonstrate procedure without SQL injection

sql">SET SERVEROUTPUT ON;DECLARE record_value VARCHAR2(4000);
BEGINget_record('Andy', 'Waiter', record_value);
END;
/
-- Run Result
Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter'
Rec: Serve dinner at Cafe Pete

Example of statement modification:

sql">DECLARE record_value VARCHAR2(4000);
BEGINget_record('Anybody '' OR service_type=''Merger''--','Anything',record_value);
END;
/
-- Run Result
Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR service_type='Merger'--' AND service_type='Anything'
Rec: Buy company XYZPL/SQL procedure successfully completed.

2.3、Statement Injection

Statement injection means that a user appends one or more SQL statements to a dynamic SQL statement.

Anonymous PL/SQL blocks are vulnerable to this technique.

Example-2 Procedure Vulnerable to Statement Injection
这个示例创建了一个易受语句注入攻击的过程，然后调用该过程，无论是否使用语句注入。
使用语句注入，过程删除中公开的假定的秘密记录

sql">CREATE OR REPLACE PROCEDURE sp_statement_injection (user_name    IN  VARCHAR2,service_type IN  VARCHAR2
) AUTHID DEFINER
ISblock1 VARCHAR2(4000);
BEGIN-- Following block is vulnerable to statement injection-- because it is built by concatenation.block1 :='BEGINDBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');'|| 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || ''');END;';DBMS_OUTPUT.PUT_LINE('Block1: ' || block1);EXECUTE IMMEDIATE block1;
END;
/
-- Demonstrate procedure without SQL injection
beginsp_statement_injection('Andy', 'Waiter');
end;
/
-- Run Result
Block1: BEGINDBMS_OUTPUT.PUT_LINE('user_name:Andy');DBMS_OUTPUT.PUT_LINE('service_type: Waiter');END;
user_name: Andy
service_type: WaiterPL/SQL procedure successfully completed.

查询表数据

sql">TESTUSER@FREEPDB1> set linesize 200
TESTUSER@FREEPDB1> COLUMN date_created FORMAT A30;
TESTUSER@FREEPDB1> select * from secret_records;USER_NAME                   SERVICE_TYPE                         VALUE                                    DATE_CREATED
--------------------------- ------------------------------------ ---------------------------------------- ------------------------------
Andy                        Waiter                               Serve dinner at Cafe Pete                2024-09-18 19:34:29
Chuck                       Merger                               Buy company XYZ                          2024-09-18 19:34:39

语句修改示例

sql">BEGINsp_statement_injection('Anybody', 'Anything'');DELETE FROM secret_records WHERE service_type=INITCAP(''Merger');
END;
/
-- Run Result
Block1: BEGINDBMS_OUTPUT.PUT_LINE('user_name: Anybody');DBMS_OUTPUT.PUT_LINE('service_type: Anything');DELETE FROM secret_records WHERE service_type=INITCAP('Merger');END;
user_name: Anybody
service_type: AnythingPL/SQL procedure successfully completed.-- query
TESTUSER@FREEPDB1> SELECT * FROM secret_records;USER_NAME                   SERVICE_TYPE                         VALUE                                    DATE_CREATED
--------------------------- ------------------------------------ ---------------------------------------- ------------------------------
Andy                        Waiter                               Serve dinner at Cafe Pete                2024-09-18 19:34:29

2.4、Data Type Conversion

A less known SQL injection technique uses NLS session parameters to modify or inject SQL statements.

A datetime or numeric value that is concatenated into the text of a dynamic SQL statement must be converted to the VARCHAR2 data type. The conversion can be either implicit (when the value is an operand of the concatenation operator) or explicit (when the value is the argument of the TO_CHAR function). This data type conversion depends on the NLS settings of the database session that runs the dynamic SQL statement. The conversion of datetime values uses format models specified in the parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT, depending on the particular datetime data type. The conversion of numeric values applies decimal and group separators specified in the parameter NLS_NUMERIC_CHARACTERS.

One datetime format model is “text”. The text is copied into the conversion result. For example, if the value of NLS_DATE_FORMAT is ‘“Month:” Month’, then in June, TO_CHAR(SYSDATE) returns ‘Month: June’. The datetime format model can be abused as shown in Example-3.

Example-3 Procedure Vulnerable to SQL Injection Through Data Type Conversion

sql">TESTUSER@FREEPDB1> SELECT * FROM secret_records;USER_NAME                   SERVICE_TYPE                         VALUE                                    DATE_CREATED
--------------------------- ------------------------------------ ---------------------------------------- ------------------------------
Andy                        Waiter                               Serve dinner at Cafe Pete                2024-09-18 19:34:29
Chuck                       Merger                               Buy company XYZ                          2024-09-18 19:52:56-- Return records not older than a monthCREATE OR REPLACE PROCEDURE get_recent_record (user_name    IN  VARCHAR2,service_type IN  VARCHAR2,rec          OUT VARCHAR2
) AUTHID DEFINER
ISquery VARCHAR2(4000);
BEGIN/* Following SELECT statement is vulnerable to modificationbecause it uses concatenation to build WHERE clauseand because SYSDATE depends on the value of NLS_DATE_FORMAT. */query := 'SELECT value FROM secret_records WHERE user_name='''|| user_name|| ''' AND service_type='''|| service_type|| ''' AND date_created>'''|| (SYSDATE - 30)|| '''';DBMS_OUTPUT.PUT_LINE('Query: ' || query);EXECUTE IMMEDIATE query INTO rec;DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/

Demonstrate procedure without SQL injection:

sql">DECLARE record_value VARCHAR2(4000);
BEGINget_recent_record('Andy', 'Waiter', record_value);
END;
/
-- Result
Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' AND date_created>'2024-08-19 19:54:39'
Rec: Serve dinner at Cafe PetePL/SQL procedure successfully completed.

Example of statement modification

sql">ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';DECLARErecord_value VARCHAR2(4000);
BEGINget_recent_record('Anybody', 'Anything', record_value);
END;
/
-- run result
Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND service_type='Anything' AND date_created>'' OR service_type='Merger'
Rec: Buy company XYZPL/SQL procedure successfully completed.

三、Oracle SQL注入的防护策略

为了防止Oracle SQL注入攻击，可以采取以下策略：

Guards Against SQL Injection
If you use dynamic SQL in your PL/SQL applications, you must check the input text to ensure that it is exactly what you expected.

You can use the following techniques:

Bind Variables
Validation Checks
Explicit Format Models

3.1、Bind Variables

The most effective way to make your PL/SQL code invulnerable to SQL injection attacks is to use bind variables.

The database uses the values of bind variables exclusively and does not interpret their contents in any way. (Bind variables also improve performance.)

Example-4 Bind Variables Guarding Against SQL Injection

The procedure in this example is invulnerable to SQL injection because it builds the dynamic SQL statement with bind variables (not by concatenation as in the vulnerable procedure in Example-1). The same binding technique fixes the vulnerable procedure shown in Example-2

sql">CREATE OR REPLACE PROCEDURE get_record_2 (user_name    IN  VARCHAR2,service_type IN  VARCHAR2,rec          OUT VARCHAR2
) AUTHID DEFINER
ISquery VARCHAR2(4000);
BEGINquery := 'SELECT value FROM secret_recordsWHERE user_name=:aAND service_type=:b';DBMS_OUTPUT.PUT_LINE('Query: ' || query);EXECUTE IMMEDIATE query INTO rec USING user_name, service_type;DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/

Demonstrate procedure without SQL injection:

sql">SET SERVEROUTPUT ON;
DECLARE record_value VARCHAR2(4000);
BEGINget_record_2('Andy', 'Waiter', record_value);
END;
/
-- run result
Query: SELECT value FROM secret_recordsWHERE user_name=:aAND service_type=:b
Rec: Serve dinner at Cafe PetePL/SQL procedure successfully completed.

Try statement modification:

sql">DECLARE record_value VARCHAR2(4000);
BEGINget_record_2('Anybody '' OR service_type=''Merger''--','Anything',record_value);
END;
/
-- run result
Query: SELECT value FROM secret_recordsWHERE user_name=:aAND service_type=:b
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TESTUSER.GET_RECORD_2", line 15
ORA-06512: at line 4

3.2、Validation Checks

Always have your program validate user input to ensure that it is what is intended.

For example, if the user is passing a department number for a DELETE statement, check the validity of this department number by selecting from the departments table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by selecting from the static data dictionary view ALL_TABLES.

In validation-checking code, the subprograms in the DBMS_ASSERT package are often useful. For example, you can use the DBMS_ASSERT.ENQUOTE_LITERAL function to enclose a string literal in quotation marks, as Example-5 does. This prevents a malicious user from injecting text between an opening quotation mark and its corresponding closing quotation mark.

Example-5 Validation Checks Guarding Against SQL Injection

In this example, the procedure raise_emp_salary checks the validity of the column name that was passed to it before it updates the employees table, and then the anonymous block invokes the procedure from both a dynamic PL/SQL block and a dynamic SQL statement.

sql">CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value  NUMBER,emp_column    VARCHAR2,amount NUMBER ) AUTHID DEFINER
ISv_column  VARCHAR2(30);sql_stmt  VARCHAR2(200);
BEGIN-- Check validity of column name that was given as input:SELECT column_name INTO v_columnFROM USER_TAB_COLSWHERE TABLE_NAME = 'EMPLOYEES'AND COLUMN_NAME = emp_column;sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '|| DBMS_ASSERT.ENQUOTE_NAME(v_column,FALSE) || ' = :2';EXECUTE IMMEDIATE sql_stmt USING amount, column_value;-- If column name is valid:IF SQL%ROWCOUNT > 0 THENDBMS_OUTPUT.PUT_LINE('Salaries were updated for: '|| emp_column || ' = ' || column_value);END IF;-- If column name is not valid:EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/DECLARE plsql_block  VARCHAR2(500);
BEGIN-- Invoke raise_emp_salary from a dynamic PL/SQL block:plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;-- Invoke raise_emp_salary from a dynamic SQL statement:EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;' USING 112, 'EMPLOYEE_ID', 10;
END;
/
-- run result 
Salaries were updated for: DEPARTMENT_ID = 110
Salaries were updated for: EMPLOYEE_ID = 112PL/SQL procedure successfully completed.

3.3、Explicit Format Models

Using explicit locale-independent format models to construct SQL is recommended not only from a security perspective, but also to ensure that the dynamic SQL statement runs correctly in any globalization environment.

If you use datetime and numeric values that are concatenated into the text of a SQL or PL/SQL statement, and you cannot pass them as bind variables, convert them to text using explicit format models that are independent from the values of the NLS parameters of the running session. Ensure that the converted values have the format of SQL datetime or numeric literals.

Example-6 Explicit Format Models Guarding Against SQL Injection

This procedure is invulnerable to SQL injection because it converts the datetime parameter value, SYSDATE - 30, to a VARCHAR2 value explicitly, using the TO_CHAR function and a locale-independent format model (not implicitly, as in the vulnerable procedure in Example-3).

sql">-- Return records not older than a monthCREATE OR REPLACE PROCEDURE get_recent_record (user_name     IN  VARCHAR2,service_type  IN  VARCHAR2,rec           OUT VARCHAR2
) AUTHID DEFINER
ISquery VARCHAR2(4000);
BEGIN/* Following SELECT statement is vulnerable to modificationbecause it uses concatenation to build WHERE clause. */query := 'SELECT value FROM secret_records WHERE user_name='''|| user_name || ''' AND service_type=''' || service_type || ''' AND date_created> DATE ''' || TO_CHAR(SYSDATE - 30,'YYYY-MM-DD') || '''';DBMS_OUTPUT.PUT_LINE('Query: ' || query);EXECUTE IMMEDIATE query INTO rec;DBMS_OUTPUT.PUT_LINE('Rec: ' || rec);
END;
/

Try statement modification:

sql">ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"'; DECLARE record_value VARCHAR2(4000);
BEGINget_recent_record('Anybody', 'Anything', record_value);
END;
/
-- run result
Query: SELECT value FROM secret_records WHERE user_name='Anybody' AND 
service_type='Anything' AND date_created> DATE '2024-08-19' 
DECLARE 
* 
ERROR at line 1: 
ORA-01403: no data found 
ORA-06512: at "SYS.GET_RECENT_RECORD", line 21 
ORA-06512: at line 4 -- 可以看到，执行报错，

四、总结

Oracle SQL注入是一种严重的网络安全威胁，它允许攻击者利用应用程序的漏洞来操纵数据库查询，进而获取敏感信息。为了防止这种攻击，需要采取一系列的防护措施，包括使用预编译语句、输入验证、使用存储过程、最小权限原则、日志记录和监控、使用WAF以及定期更新和打补丁等。通过这些措施，可以大大降低Oracle SQL注入的风险，保护数据库和应用程序的安全。