31、Building Reliable Solutions to Avoid SQL Injection

Building Reliable Solutions to Avoid SQL Injection

1. Understanding SQL Injection

SQL injection is a prevalent and dangerous hacking technique. It occurs when a developer prompts a user for input, and that input is directly concatenated into a SQL query as a string. Instead of providing the required values, a malicious user can add additional SQL commands or conditions, which are then compiled and executed. This can lead to unauthorized access to sensitive data, modification of the database, or even its destruction.

1.1 Example of SQL Injection

Let’s consider an example related to an employment contract. We have two tables, PERSONAL_DATA and EMPLOYMENT , which are merged into a view V_EMP . The view contains columns such as Employee_id , Name , Surname , Position , Salary , Date_from , and Date_to .

We create a procedure GET_EMPLOYEES to list employees hired on a specified date:

create or replace procedure GET_EMPLOYEES(p_date DATE)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_ns varchar(100);
    v_pid varchar(11);
begin
    v_statement:='select Name || '' '' || Surname as ns,
                  employee_id
                  from V_EMP
                  where Date_from>='''||p_date||'''';
    open v_cursor for v_statement;
    loop
        fetch v_cursor into v_ns, v_pid;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_ns ||': ' || v_pid);
    end loop;
    close v_cursor;
end;
/

The default date format is inherited from the server settings but can be overwritten by the user. For example, we can set the format to YYYY-MM-DD using the following command:

alter session set NLS_DATE_FORMAT='YYYY-MM-DD';

We can then call the procedure with a specific date:

exec GET_EMPLOYEES(TO_DATE('1.1.2015', 'DD.MM.YYYY'));
exec GET_EMPLOYEES(sysdate);

1.2 The Problem with Implicit Conversion

The core of the SQL injection problem lies in implicit conversion. When the input date value is concatenated to the SQL query, it is implicitly converted to a string. The NLS_DATE_FORMAT parameter controls this conversion, but it can be manipulated by the user.

For example, we can change the date format to inject additional SQL commands:

alter session
set nls_date_format = '"'' union select Surname,Salary
                      from V_EMP --"';

When we call the GET_EMPLOYEES procedure again:

exec GET_EMPLOYEES(sysdate);

The query is modified, and instead of the employee ID, we get the surname and salary of each employee:

--> It provides the salary of the employees!!!
--> Cash:  3000
--> Smith: 1200
--> Smith: 1800

This shows that direct value concatenation in SQL queries is extremely dangerous and can lead to data leakage.

2. Solutions to Limit SQL Injection

2.1 Using Bind Variables

Bind variables are a reliable solution to limit SQL injection. Instead of concatenating values directly into the query, we use bind variables. Here is the updated GET_EMPLOYEES procedure using bind variables:

create or replace procedure GET_EMPLOYEES(p_date DATE)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_ns varchar(100);
    v_pid varchar(11);
begin
    v_statement:='select Name || '' '' || Surname as ns,
                  Employee_id
                  from V_EMP
                  where Date_from <= :bind_date';
    DBMS_OUTPUT.PUT_LINE(v_statement);
    open v_cursor for v_statement using p_date;
    loop
        fetch v_cursor into v_ns, v_pid;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_ns ||': ' || v_pid);
    end loop;
    close v_cursor;
end;
/

To test the effectiveness of bind variables, we first change the date format to try and inject additional queries:

alter session
set NLS_DATE_FORMAT = '"''
union select ''hack'',1000 from dual --"';

Then we execute the updated procedure:

exec GET_EMPLOYEES(sysdate);

The result shows that no additional rows are listed based on the session date format specification:

--> Name & surname, EMPLOYEE_ID
--> John Cash: 10000
--> Simone Smith: 20000
--> Simone Smith: 20001

The benefits of using bind variables are twofold:
- Security : They limit SQL injection by preventing malicious input from modifying the query structure.
- Performance : Each statement only needs to be parsed once, regardless of the input values.

2.2 Explicit Date and Time Value Conversion

Another technique to limit SQL injection related to date and time values is explicit conversion. We can use the TO_CHAR function to convert date values to strings and check if the conversion is valid.

Let’s go back to the get_employees function and modify the select statement condition:

select Name || ' ' || Surname as ns, Employee_id
from V_EMP
where TO_CHAR(DATE_FROM, 'DD.MM.YYYY')
    <= TO_CHAR(p_date, 'DD.MM.YYYY');

If the input values are not properly formatted, the TO_CHAR conversion will fail and raise an exception. For example, if we change the session date format to an invalid value:

alter session set NLS_DATE_FORMAT='"hack"';

The original statement:

select Name || ' ' || Surname as ns, Employee_id
from V_EMP
where TO_CHAR(date_from, 'DD.MM.YYYY')
    <= TO_CHAR(TO_DATE('15.10.2022'), 'DD.MM.YYYY');

will be transformed to:

select Name || ' ' || Surname as ns, Employee_id
from V_EMP
where TO_CHAR(date_from, 'DD.MM.YYYY')
    <= TO_CHAR("hack", 'DD.MM.YYYY');

This will raise an ORA-01861 exception:

--> ORA-01861: "literal does not match format string"
--> *Cause: Literals in the input must be the same length ---> 
as literals in the format string (with the exception of --> 
leading whitespace).

2.3 Sanitizing the Input with the DBMS_ASSERT Package

The DBMS_ASSERT package provides several methods to validate input values and prevent SQL injection. Here are some of the methods:
| Method | Description |
| ---- | ---- |
| ENQUOTE_LITERAL | Encloses the input value in single quote marks and checks for proper quote pairing. |
| SCHEMA_NAME | Checks if the input value is an existing schema name. |
| SIMPLE_SQL_NAME | Verifies a simple SQL name. |
| QUALIFIED_SQL_NAME | Checks the value against the qualified SQL name list. |
| SQL_OBJECT_NAME | Evaluates if the input value is a qualified SQL identifier of an existing SQL object. |
| NOOP | Returns the original value without any checking. |

2.3.1 Implementing the ENQUOTE_LITERAL Function

The ENQUOTE_LITERAL function is particularly useful for preventing SQL injection. It checks the input value for potential injection by evaluating the quotation marks.

select DBMS_ASSERT.ENQUOTE_LITERAL(''' or ''1=1')
from dual;

If the processing fails, it raises an ORA-06502 exception, indicating a possible SQL injection attack.

Here are some examples of using the ENQUOTE_LITERAL function:

select DBMS_ASSERT.ENQUOTE_LITERAL('Michal Kvet')
from dual;
--> 'Michal Kvet'

select DBMS_ASSERT.ENQUOTE_LITERAL(1)
from dual;
--> '1'

select DBMS_ASSERT.ENQUOTE_LITERAL(NULL)
from dual;
--> ''

select DBMS_ASSERT.ENQUOTE_LITERAL('NULL')
from dual;
--> 'NULL'

The following mermaid flowchart shows the process of using bind variables and the DBMS_ASSERT package to prevent SQL injection:

graph TD;
    A[Receive User Input] --> B{Is it a simple query?};
    B -- Yes --> C[Use Bind Variables];
    B -- No --> D[Build SQL Dynamically];
    D --> E[Use DBMS_ASSERT Package];
    C --> F[Execute Query];
    E --> F;
    F --> G[Check for Exceptions];
    G -- Exception --> H[Possible SQL Injection Detected];
    G -- No Exception --> I[Query Executed Successfully];

In summary, to prevent SQL injection, we should always use bind variables, perform explicit conversions when dealing with date and time values, and use the DBMS_ASSERT package to sanitize input values. By following these practices, we can build more reliable and secure SQL solutions.

3. Practical Considerations and Additional Tips

3.1 Handling Complex Queries

In real - world scenarios, queries can be much more complex than the simple examples we’ve seen so far. When dealing with complex queries that involve multiple conditions, joins, and sub - queries, the risk of SQL injection increases.

For example, consider a query that retrieves employees based on multiple criteria such as department, salary range, and hire date:

-- A complex query vulnerable to SQL injection
create or replace procedure GET_COMPLEX_EMPLOYEES(p_dept varchar2, p_min_salary number, p_date DATE)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_ns varchar(100);
    v_pid varchar(11);
begin
    v_statement:='select Name || '' '' || Surname as ns,
                  Employee_id
                  from V_EMP
                  where Department = '''||p_dept||'''
                  and Salary >= '||p_min_salary||'
                  and Date_from >= '''||p_date||'''';
    open v_cursor for v_statement;
    loop
        fetch v_cursor into v_ns, v_pid;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_ns ||': ' || v_pid);
    end loop;
    close v_cursor;
end;
/

To protect this complex query, we should use bind variables:

create or replace procedure GET_COMPLEX_EMPLOYEES(p_dept varchar2, p_min_salary number, p_date DATE)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_ns varchar(100);
    v_pid varchar(11);
begin
    v_statement:='select Name || '' '' || Surname as ns,
                  Employee_id
                  from V_EMP
                  where Department = :bind_dept
                  and Salary >= :bind_min_salary
                  and Date_from >= :bind_date';
    open v_cursor for v_statement using p_dept, p_min_salary, p_date;
    loop
        fetch v_cursor into v_ns, v_pid;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_ns ||': ' || v_pid);
    end loop;
    close v_cursor;
end;
/

3.2 Testing for SQL Injection

It’s crucial to test your applications for SQL injection vulnerabilities. Here are some steps you can follow:
1. Manual Testing :
- Try to enter malicious input in all input fields of your application. For example, in a login form, try entering ' OR '1'='1 in the username or password field.
- Check if the application behaves as expected and doesn’t return unauthorized data.
2. Automated Testing Tools :
- Tools like OWASP ZAP (Zed Attack Proxy) can be used to scan your application for SQL injection vulnerabilities.
- These tools send a variety of malicious inputs to your application and analyze the responses to detect potential vulnerabilities.

3.3 Training and Awareness

All developers and users involved in the application should be aware of SQL injection risks. Here are some ways to increase awareness:
- Developer Training :
- Provide training on secure coding practices, including the proper use of bind variables, explicit conversions, and input sanitization.
- Encourage developers to follow security best practices in all their code.
- User Awareness :
- Educate users about the importance of not sharing their login credentials and not entering malicious input in application forms.

4. Case Studies

4.1 A Real - World SQL Injection Incident

Let’s consider a fictional case study of a small e - commerce website. The website had a search functionality that allowed users to search for products by name. The search query was constructed by concatenating the user input directly into the SQL query:

-- Vulnerable search query
create or replace procedure SEARCH_PRODUCTS(p_search_term varchar2)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_product_name varchar(100);
    v_product_id varchar(11);
begin
    v_statement:='select Product_name, Product_id
                  from PRODUCTS
                  where Product_name like ''%'||p_search_term||'%''';
    open v_cursor for v_statement;
    loop
        fetch v_cursor into v_product_name, v_product_id;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_product_name ||': ' || v_product_id);
    end loop;
    close v_cursor;
end;
/

A malicious user discovered this vulnerability and entered the following input in the search field: ' OR 1=1 -- . This modified the query to return all products in the database, including sensitive information such as product costs and inventory levels.

To fix this vulnerability, the developers updated the procedure to use bind variables:

create or replace procedure SEARCH_PRODUCTS(p_search_term varchar2)
is
    v_statement varchar2(10000);
    v_cursor sys_refcursor;
    v_product_name varchar(100);
    v_product_id varchar(11);
begin
    v_statement:='select Product_name, Product_id
                  from PRODUCTS
                  where Product_name like ''%'' || :bind_search_term || ''%''';
    open v_cursor for v_statement using p_search_term;
    loop
        fetch v_cursor into v_product_name, v_product_id;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_product_name ||': ' || v_product_id);
    end loop;
    close v_cursor;
end;
/

4.2 Lessons Learned

• Security is Paramount : SQL injection can lead to significant data breaches and financial losses. Therefore, it should be a top priority in application development.
• Proactive Measures : Instead of waiting for an attack to occur, developers should implement preventive measures such as using bind variables and input sanitization from the start.

5. Conclusion

5.1 Recap of Key Points

• SQL injection is a serious security threat that can lead to unauthorized access to sensitive data, data modification, and database destruction.
• To prevent SQL injection, we should use bind variables, perform explicit conversions for date and time values, and use the DBMS_ASSERT package to sanitize input values.
• Handling complex queries requires extra care, and we should always test our applications for SQL injection vulnerabilities.

5.2 Future Outlook

As technology evolves, new types of SQL injection attacks may emerge. Therefore, developers need to stay updated on the latest security threats and best practices. Additionally, the use of artificial intelligence and machine learning in security can help in detecting and preventing SQL injection attacks more effectively in the future.

The following table summarizes the key techniques and their benefits for preventing SQL injection:
| Technique | Benefits |
| ---- | ---- |
| Bind Variables | Limit SQL injection, improve performance by reducing statement parsing |
| Explicit Date and Time Conversion | Detect and prevent SQL injection related to date and time values |
| DBMS_ASSERT Package | Sanitize input values and validate their properties |

In conclusion, by implementing the techniques and best practices discussed in this article, we can build reliable and secure SQL solutions that are resistant to SQL injection attacks.