Chapter 12. Safeguarding PL/SQL Code against SQL Injection Attacks

Oracle database is, undoubtedly, the uncrowned monarch of "Information Business" across the globe. Though it has narrowed the gap between the expectations and the potential, the question, "Is my information secure?" still hovers the DBMS philosophies. We often discuss the vectors of language strength, performance, storage, and data security. But code vulnerability and security share equal stake in data security. Nevertheless, the strength of SQL and PL/SQL is unquestionable, but vulnerable code writing might motivate a hacker to smuggle through the code and perform vicious manipulations in the data.

In this chapter, we will expand our bandwidth to understand PL/SQL code security. We will understand how "loose code writing" can encompass the code base injection and hence, the data. We will cover the following topics:

• SQL injection

  • Introduction and understanding

• Immunizing SQL injection attacks

  • Reducing the attack surface

  • Avoiding...

SQL injection—an introduction

SQL injection is a database intrusion that occurs when an unauthorized "malicious" user hacks the PL/SQL code and draws unintended access to the database. Once the code has been cracked, the malicious user can pull out confidential information from the database. There can be many more hazardous consequences of code injection.

In 1998, Rain Forest Puppy (RFP) was the first to identify the "technology vulnerabilities" in his paper "NT Web Technology Vulnerabilities" for "Phrack 54". Later, the injective techniques were studied by many technology experts and evangelists to chalk out the best practices of code writing to dilute such acts. Till date, many application exploitation cases have been registered on account of code injection. For reference, check out http://www.computerworld.com.au/index.php/id;683627551. The applications working with personal information or financial data are more prone to injective attacks.

SQL injection—an overview

In the past, the reason...

Immunizing SQL injection attacks

We will discuss the ways to immunize code against SQL injection in detail. Besides the ways which are listed above, we will discuss some additional tricks too, to reduce SQL injection attacks.

Reducing the attack's surface

Reducing the attack's surface is one of the preventive measures that are proactively used to fight the SQL injection attacks. It aims to minimize the area of operation and visibility of the hackers by controlling the privileges and execution rights of a user on the accessible subprograms. The technique is helpful when a user plays a defined role in an application but is still bestowed with a lot more irrelevant privileges from the admin. The attack perimeter can be reduced by:

• Controlling the user privileges

• Creating the program units with invoker's rights

Controlling user privileges

The DBA must keep a hawk eye on the roles of the users in the application to prevent any malicious motivation. The availability of additional spare privileges might...

Testing the code for SQL injection flaws

Until now, we 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 and testing play a crucial role in taking preventive measures against hackers. Code testing resources must adopt a concrete strategy to discover and hit upon the code 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 effective test strategy must be employed to discover injection flaws. Of course, there is no magic practice to ooze out all flaws in the code.

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

Summary

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

Practice exercise

1. Which method would you employ to immunize the PL/SQL code against SQL Injection attacks?

   1. Replace Dynamic SQLs with Static SQLs.

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

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

   4. Removing string type parameters from the procedure.

2. Use static SQL to avoid SQL injection when all Oracle identifiers are known at the time of code execution.

   1. True

   2. False

3. 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 ORDER data in EMPLOYEES table.

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

4. Pick the correct strategies to fight against of 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's rights.

5. Statistical...