Securing an Application - Oracle Application Express 4 Recipes - page 241

5. You've successfully protected your report against the SQL injection attack.

How It Works
The SQL injection attack is one of the most common forms of attack against web applications. It usually
involves a malicious user gaining unauthorized access to data by manipulating dynamic SQL statements
generated by your application. This is done so by massaging input data so that becomes part of the SQL
statement itself. For instance, take a look at your original SQL statement.

SELECT * FROM CUSTOMERS WHERE NAME = '<INPUT DATA FROM SEARCH FIELD>'
If the malicious user keys in ' OR 1=1-- in the search field, this is concatenated with your SQL code, and
it becomes

SELECT * FROM CUSTOMERS WHERE NAME = '' OR 1=1--'

 Tip  The -- symbol is the comment indicator symbol in PL/SQL, and it comments out the last single quote
character, effectively turning your active SQL statement into

SELECT * FROM CUSTOMERS WHERE NAME='' OR 1=1

This allows the end user to retrieve the entire list of customers from your database! A SQL injection
attack can be used in many different ways. For instance, it can be used against an unprotected login
page where an end user can gain unauthorized access to your application simply by manipulating the
data entered in the username or password field.
Earlier in this recipe, you saw that the following notation was used: &PSEARCH_BYNAME. The
ampersand indicates that this is a substitution variable; substitution variables are used to retrieve data
from form fields on a page. This data is then (as its name implies) substituted as-is into the target string.
Substitution variables are the root causes of most SQL injection attacks. Since data is simply
substituted into the SQL statement, this allows apostrophes keyed in by the end user to end up in the
final SQL string, causing the previously mentioned scenario.
Traditionally, in most web applications, SQL injection attacks are prevented by escaping single
quote characters in the input data. “Escaping” the single quote characters simply meant placing an
escape character at the front of each single quote in the input data to render them harmless.
Duplicating the single quote character is a way to escape the single quote in PL/SQL. For instance,
the final SQL generated if the input data was escaped would look like this:

SELECT * FROM CUSTOMERS WHERE NAME=' '' OR 1=1-- '

 Note The ' OR 1=1-- input phrase in the previous SQL (after escaping its single quotes) is correctly treated as
a string instead of as PL/SQL code.