The Oracle® Hacker's Handbook: Hacking and Defending Oracle

5.4. PL/SQL Injection

PL/SQL injection is a method for attacking procedures, functions, triggers, and types. The idea behind SQL injection is quite simple. User-supplied input to an application is embedded directly into a dynamic SQL query, which is then executed; because the input that is embedded is user controlled, it is possible for that user to manipulate the query in such a way that extra SQL is executed. This additional SQL that is executed can be used to gain unauthorized access to data, allowing an attacker to gain complete control of the server. Let's look at some code snippets for some simple examples:

..
  ..
  STMT:= 'SELECT TITLES FROM BOOKS WHERE AUTHOR = ''' || USERINPUT ||
  '''';
  EXECUTE IMMEDIATE STMT;
  ..
  ..

This code is vulnerable to SQL injection. Assuming the user input in this case is DICKENS, then the actual query that is eventually executed by the application is as follows:

SELECT TITLES FROM BOOKS WHERE AUTHOR = 'DICKENS'

Note that strings in SQL are enclosed in single quotes; thus, if we changed the user input to O'BRIEN, the following SQL will be executed:

SELECT TITLES FROM BOOKS WHERE AUTHOR = 'O'BRIEN'

As you can see, the single quotes are not balanced, which will cause an error: ORA-01756: quoted string not properly terminated. In essence, by inserting a single quote into their input, attackers can "escape" from the original query and then tack on their own SQL. Consider, for example, what would happen if the user input supplied were DICKENS'' UNION SELECT ...