Building Reliable Solutions to Avoid SQL Injection

Improper value management and assignment can lead to security issues related to SQL injection. Although it is not directly evident, it can cause significant problems if bind variables or other sanitization techniques are not used. Unfortunately, most people do not realize this situation and live with the incorrect assumption that the date and time values cannot be the root of the SQL injection. They put the character string sequence of individual elements representing date and time values into the command definition. However, then, the provided value is converted into a DATE or TIMESTAMP value automatically and evaluated. But there is a risk: automated conversion.

Often, date and time values are not considered security holes. They consist of individual elements, and the format is precisely specified, so where is the problem? Which parameters affect the format and representation? The session DATE and TIMESTAMP formats impact the...

Understanding SQL injection

Are you self-employed or part of the company? Who can access your data in the company? Well, you suppose, only your manager can access data about colleagues from the same department and not about anyone from the whole company, right? It is, therefore, important to ensure that the system inputs cannot change the query without proper authorization and thus obtain completely different values and outputs. You probably wouldn’t want anyone to have access to your salary, available funds in your accounts, or other sensitive data. Consequently, it is necessary to secure and sanitize the inputs so that the user cannot influence the structure of the select statements or modify them to get principally different results.

The rule is simple: never rely on the user data, and always check it to ensure that suspicious code is not detected there.

SQL injection risk is a hazardous consequence of a developer prompting a user to get the value from the input. That...

Solutions to limit SQL injection

In the previous section, you learned about SQL injection creation. We showed what it can cause and what the consequences are. This section will drive you through the available solutions by introducing bind variables and explicit conversions. We will also discuss the DBMS_ASSERT package, which allows you to sanitize input values.

Using bind variables

Bind variables provide the relevant solution to limit SQL injection. Instead of concatenating the character strings forming the statement, the input value is applied using a bind variable, as shown in the following code block, which defines a GET_EMPLOYEES procedure. The condition in the where clause is related to the Date_from attribute and procedure parameter (p_date). This is not concatenated, but rather treated as a bind variable:

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...

Summary

SQL injection is a technique that involves changing the original SQL statement code by extending it from the user input. Instead of getting the required value, the user provides the SQL code, which is executed. This results in access to commonly unavailable (hidden) data, the possibility of unauthorized changes to values, or dropping individual objects. The security, robustness, and correctness of the systems would be significantly impaired.

In this chapter, we focused on SQL injection problems related to Date and Time processing. We have shown that the problem can be precisely done because of the implicit character string conversion to the Date or Timestamp value. The solution is associated with the bind variables, which cannot cover additional conditions or queries.

Additionally, we covered explicit Date and Time management and looked at the DBMS_ASSERT package, which can identify identifiers and SQL names and can also limit SQL injection by using the ENQUOTE_LITERAL...

Questions

Answer the following questions to test your knowledge of this chapter:

1. Try to figure out the SQL injection risk in your already-developed applications. Have you focused on the implicit Date and Time conversions? How can your solution be updated to eliminate it?
2. Use the data model shown in Figure 14.1. Assume that the following query is used to produce the name and surname values of the employee. Each employee is uniquely identified by the employee_id attribute value. The required value of the employee_id value is user specified:

   select name, surname
    from personal_data
         join employment using (personal_id)
      where employee_id=INPUT_VAL;

Which input value provides SQL injection and produces all the names and surnames, irrespective of the provided employee ID?

1. NULL
2. IS NULL
3. 1=1
4. 1 or 1=1

Further reading

To learn more about the topics that were covered in this chapter, take a look at the following resources:

• SQL Injection Strategies: Practical techniques to secure old vulnerabilities against modern attacks, by Ettore Galluccio, Edoardo Caselli, and Gabriele Lombari. This book serves as a theoretical as well as a practical guide to take you through the important aspects of SQL injection in web, mobile, and IoT applications. It reflects both defense and attack perspectives. You will learn how to defend systems against SQL injection attacks and about various concepts and scenarios, and the SQL injection manual attack techniques will be discovered.
• Real World SQL and PL/SQL: Advice from the Experts, by Arup Nanda, Brendan Tierney, Heli Helskyaho, Martin Widlake, and Alex Nuijten. Chapter 15 of this book discusses the threats to data security, focusing on SQL injection in a complex manner pointing to program security. It references the authorization, definer...