Chapter 11. Safeguarding PL/SQL Code against SQL injection

An incidence of a security breach involves: a hacker and a vulnerable system. A hacker can be an insider or an outsider, who attacks the system to expose and access confidential information, which may lead to fatal consequences. A system could be vulnerable to attacks because of low coding standards and a half-baked understanding of technologies. The steep growth of web application users and sharp rise in social media interactions has widened the attack surface area. The systems that are a hacker's paradise are those which contain personal identifiable information, financial information, government data, and business transactions. The hazardous consequences of a security breach have pushed many organizations to look seriously after data security. As a first layer of protection, organizations must bolt the network penetration through the adoption of products like Audit Vault and Database Firewall (AVDF) and protect data access through...

What is SQL injection?

A database application on the server side contains the programmable logic embedded within the PL/SQL packages and subprograms. These PL / SQL program units may contain SQL statements, which are intended to perform specific operations. The SQL statements, whose query text is built at runtime (dynamically derived) and based on client-supplied inputs, open ways for SQL injection. A malicious user can supply a manipulated input that can break through the PL/SQL program logic by replacing the SQL syntax and perform arbitrary execution.

The reason it is known as Injection is because the manipulated text, which replaces or appends to the original SQL text in a PL/SQL program unit, is parsed along with the original SQL statement. The undetected attacker's code is legally executed by the SQL engine, along with the original programmed SQL.

For example, a string type malicious input from the client is executed as legal code by the SQL engine; thus, exploiting a server-side SQL...

Preventing SQL injection attacks

SQL injection is not a design bug, but an intentional malicious practice. Database developers must adopt best practices while writing PL/SQL code. If code modification is not possible, the application interface layer may also work to reduce the attack surface area.

Let's take a brief look at the precautionary measures to minimize injection attacks:

• Check your dynamic SQL: Dynamic SQL query text, which is constructed at runtime and directly uses the user-supplied inputs, creates a pregnable hitch point in the application. You can protect them against injection attacks through either of these techniques:

  • Reduce the direct exposure of client inputs to dynamic SQL: You can sanitize and validate the client-supplied inputs before they are used in dynamic SQL. Oracle provides the DBMS_ASSERT package to verify the inputs.

  • Use bind arguments in dynamic SQL: Database developers are encouraged to make use of bind arguments for multiple reasons and one of them is security...

Testing the code for SQL injection flaws

So far, we have discussed the symptoms and remedies of SQL injection. We demonstrated the programming recommendations to mitigate the effects of code injections and smuggles. Assuring code quality during the testing cycle plays a crucial role towards preventing code attacks. The code testing resources must adopt a concrete strategy to discover and hit upon the code's vulnerabilities before it invites an attacker to exploit the database. Now, we will discuss some of the testing considerations to test the code for SQL injection flaws.

Test strategy

A logical and efficient test strategy must be employed to discover injection flaws. Of course, there is no magic bullet to filter out all the vulnerabilities of a piece of code.

Usual code reviews are a part of static testing while testing programs with sample data and inputs come under dynamic testing. These days, static testing has been absorbed into the development stage, where developers and their peers...

Summary

In this chapter, we learned about a malicious hacking concept—SQL injection. We discussed the causes of a code attack and its impact on the database. We covered the techniques to safeguard an application against the injection attacks through demonstrations and illustrations. At the end of the chapter, we discussed some of the testing considerations to expose the vulnerable areas in the code.

Practice exercise

• Which method would you employ to protect the PL/SQL code against SQL injection attacks?

  1. Replace Dynamic SQLs with Static SQLs.

  2. Replace concatenated inputs in Dynamic SQL with bind arguments.

  3. Declare the PL/SQL program to be executed by its invoker's rights.

  4. Remove string type parameters from the procedure.

• You should use static SQL to avoid SQL injection when all Oracle identifiers are known at the time of code execution.

  1. True.

  2. False.

• Choose the impact of SQL injection attacks:

  1. Malicious string inputs can extract confidential information.

  2. Unauthorized access can drop a database.

  3. It can insert the ORDER data in to the EMPLOYEES table.

  4. A procedure executed by owners, (SYS) rights can change the password of a user.

• Pick the correct strategies to fight against SQL injection:

  1. Sanitize the malicious inputs from the application layer with DBMS_ASSERT.

  2. Remove string concatenated inputs from the Oracle subprogram.

  3. Dynamic SQL should be removed from the stage.

  4. Execute a PL/SQL program with its creator...