You have an existing database table containing the list of all users in the organization, together with their passwords. This database table is a custom table proprietary to your organization. You try to convince your bosses to migrate the list of user accounts from the custom table into APEX, but they insist that your application authenticate against this table instead in real time.

And so you embark on this task. You want to create a custom authentication scheme to authenticate your APEX application against this external database table.

Your first task is to create the database objects used in this recipe. To create the Custom Logins table (and sample records), run the following SQL:

CREATE TABLE "CUSTOMLOGINS"
  (     "USERID" VARCHAR2(50),
        "USERNAME" VARCHAR2(255),
        "PASSWORD" VARCHAR2(255),
         CONSTRAINT "CUSTOMLOGINS_PK" PRIMARY KEY ("USERID") ENABLE
  )

INSERT INTO CUSTOMLOGINS(USERID,USERNAME,PASSWORD) VALUES('01','greg','1234')
/
INSERT INTO CUSTOMLOGINS(USERID,USERNAME,PASSWORD) VALUES('02','zehoo','7890')
/

Your next task is to define the actual authentication function itself. You will create a very simple authentication function that simply checks if the specified username and password exists in the table. If they do, access is granted. To create this function, open the SQL workshop, and run the PL/SQL shown in Listing 8-1.

CREATE OR REPLACE FUNCTION MyCustomAuthenticator (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
  RETURN BOOLEAN
IS
  l_count NUMBER;
BEGIN
   SELECT COUNT(*) into l_count from CUSTOMLOGINS WHERE Username=p_username AND
        Password=p_password;
   IF l_count > 0 THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

Your next task will be to define a new authentication scheme. To do so, follow these steps:

APEX is flexible enough to allow you to modify even the underlying authentication mechanisms for your applications. The method shown in this recipe uses a very simple authentication function (that hardcodes the username and password in PL/SQL); the objective of this recipe, however, is to give you an idea of how to go about switching the authentication mechanism from APEX's default to a custom one.

Let's take a further look at how the PL/SQL authentication function works. The skeleton for this function is described in Listing 8-2.

CREATE OR REPLACE FUNCTION AuthenticationFunction (

p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
  RETURN BOOLEAN
AS
BEGIN
    /* Do your thing here and return TRUE if access should be granted and
       FALSE if access should be denied. */
END;

You can modify this authentication function to do whatever you need. For instance, you could have the function run an SELECT query against an external table, and if the user account exists, return TRUE from the function.

In fact, there is nothing stopping you from authenticating in real-time against usernames or passwords stored in text files (although it would be a rather bad idea to do so). This, however, gives you an idea of the extremes that you can go to with APEX's flexible authentication schemes framework.

You have an interactive report showing a list of job vacancy records. You want to allow John to create new job vacancy record, but you don't want to give Barry this same privilege. In other words, you need to configure access rights for this report.

First you need to create the sample objects used in this recipe. To do so, please follow these steps:

Now that you've successfully created the sample objects successfully, let's move on to creating an authorization scheme. Please follow these instructions to do so:

Now you will need to apply the authorization scheme to the report.

Figure 8.12. Create button missing for user named system

An authorization scheme lets you define a condition (logic) that evaluates to either a true or false. The authorization scheme can then be applied to any element in your application, be it a report column, a button on a report, or even an entire form itself. When the scheme (your logic) returns true, the current user is granted access to the element. If it returns false, the current user is denied access.

Let's take a closer look at the configuration of the authorization scheme that you created earlier. Note that you chose the Exists SQL Query scheme type; it means that if the PL/SQL you wrote returned a record, then the scheme evaluates to true (access granted). If an empty result set was returned, the scheme would evaluate to false (access denied).

Now, let's take a look at the PL/SQL from earlier in this recipe:

select 1 from jobs where LOWER(v('APP_USER')) = 'greg'

You've practically defined that if the current user had the username greg, then it would return something (here you simply return 1 but it could also be abc if you desire, as long as a single record was returned). Hence the authorization scheme you created in this recipe can be interpreted as: if the current user has the username greg, he should be granted access to the element.

Based on this simple concept, you can apply very complex access rights control to your application. For instance, you can create an Employee report that shows all columns to a manager, but hides the Current Salary column when a normal clerk views it.

As a side note, the concept of authorization schemes also promotes reusability and ease of maintenance. It is reusable because you can reuse the same logic for multiple elements in your application without rewriting the same logic many times. More importantly, this makes it easier for you to maintain your application. For instance, if the logic changes one day, such that you need to include an additional check in your PL/SQL, you can just change it at one location, and it will instantly be applied to all elements that use the said authorization scheme.

You have a dynamic report showing the list of customers in the system. By default, your application requires the end user to specify the customer name before it retrieves the matching customer from the database. An APEX hacker has managed to retrieve the full list of all customers in the database via an SQL injection attack. You want to protect your application against similar attacks in the future.

First, you need to set up the sample tables and forms needed to duplicate the scenario for the attack. To do this, please follow these steps:

To perform the SQL injection attack, follow these steps:

To prevent a SQL injection attack from being used against your application, follow these steps:

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

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

In Oracle APEX, there is a better way to prevent SQL injection attacks: through the use of bind variables. Bind variables work in much the same way as passing data to a stored procedure. Bind variables automatically treat all input data as "flat" data and never mistake it for SQL code.

The syntax to declare a field item as a bind variable is through the use of the colon character (:). Simply prefix the colon to your field item name, like this:

:PSEARCH_BYNAME

The use of bind variables is often encouraged in APEX. Besides the prevention of SQL injection attacks, there are other performance-related benefits to its use (please see Chapter 7 for more information).

Your boss opens an APEX report, but instead of seeing the daily financial data, he is automatically redirected to an adult web site containing inappropriate photos. Your boss is obviously not amused. Upon closer inspection of the report, you find that someone has entered malicious JavaScript in one of the fields in the database. Upon displaying the report, the output data (containing the JavaScript) executed, causing the malicious script to run. You recognize this as the cross-site scripting attack. You want to prevent future attacks by rendering any JavaScript code found in report data harmless when it is retrieved for display.

First, try to set up the same environment that made the cross site scripting attack possible. Please follow these steps:

You are now ready to initiate the attack. Let's irritate your end users by having the Customers report automatically redirect to the Google site every time they try to view the report.

To prevent the cross-site scripting attack, you have to do the following:

The cross site scripting (XSS) attack attempts to get client-side JavaScript code to run by injecting it into data entry fields. Such attacks may simply harbor mischievous intent (as outlined in this recipe's example scenario) or can be downright dangerous; imagine an attack that redirects you to a replica of the original site requesting you to enter your password or personal details.

The XSS attack is a rather simple type of attack that works due to the following weaknesses:

To explain how the attack works, consider a form that lets you enter the name of a customer via a textbox. Ideally, the form would take in the data and save it in the database. When it needs to be displayed, the data is retrieved as-is and displayed via HTML (for instance, placed inside a table row of a table). The output result might look like Listing 8-11.

<table>
        <tr>
                <td width='100%'>ACME CORP</td>
        </tr>
</table>

If you didn't place any checks in your application and if a malicious user keyed in some JavaScript instead of ACME CORP, you would get the following (shown in Listing 8-12) as the output result:

<table>
        <tr>
                <td width='100%'>
                        <script>window.location='http://www.google.com';</script>
                </td>
        </tr>
</table>

The browser misinterprets the data as client-script code and executes the JavaScript.

One way to prevent this from happening is by validating input data during data entry (for instance, rejecting data with tags such as <script> in its content). One downside of this approach is that if you really needed to key in some data containing such a tag, it would not be possible.

The preferred way is to escape special characters, not during data entry but during data display. APEX provides an easy way to do this, which is to change the Display Type property of the report column, as you saw earlier in this recipe.