Hayden Hudson
Intro
In my experience advising clients, there are some expedient tactics that can quickly be applied to addressing 'Inappropriate Use of Substitution Syntax' errors spotted by APEX Advisor. The below decision tree, hopefully, addresses 90% of situations. The main take-away from the below is that 90% of your SQL Injection vulnerabilities will take care of themselves if you simply lean in to APEX's handy declarative report-building / link-building utilities.
Objectives
Your situation :
  • 1. APEX Advisor is flagging your application for ‘Inappropriate Use of Substitution Syntax’ (IUSS)
  • 2. You want to have Session State Protection (SSP) enabled
  • 3. You want to eliminate Cross-Site Scripting (XSS) vulnerabilities

Goals: Beyond simply (1) addressing the IUSS issues, let’s up our game and simultaneously (2) prepare our application for implementing SSP and (3) reduce our exposure to XSS.

Decision tree
Starting point: APEX Advisor has flagged an 'Inapproprite Use of Substitution Syntax'
Is it a link?
BIND Is it in a report region?
Can you turn it into an APEX button? Can we make the column a type of 'link'?
BIND
PREPARE URL
HTML WHITELIST 		BIND
PREPARE URL
HTML WHITELIST
Some scenarios not covered by decision tree
ProblemResolution
You are building a URL in the database (say, a package) and you need to pass in some APEX substitution values, like the session id. You can reference APEX substitution values with the syntax : v(‘APP_SESSION’) for e.g.
You are using a ‘select list’ in APEX to set a value and redirecting to another page. You will have to make the item you are setting unrestricted on the destination page.
You are using Ajax calls in APEX to set values to page/app items. The items on the destination page must be set as ‘unrestricted’.
Final words on SQL Injection
Avoidance Strategies:
Strategy Description
Use bind arguments Parameterize queries using bind arguments. Not only do bind arguments eliminate the possibility of SQL injection, they also enhance performance.
Avoid Dynamic SQL with concatenated input E.g.: EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || loc;
Filter and sanitize input The Oracle-supplied DBMS_ASSERT package contains a number of functions that can be used to sanitize user input and to guard against SQL injection in applications that use dynamic SQL built with Concatenated values.
Reduce Attack Surface Ensure that all excess db privileges are revoked and that only routines intended for end-user access are exposed. Move your SQL and PLSQL to the database. Break your logic into restricted schemas. For further reading: https://blogs.oracle.com/plsql-and-ebr/noplsql-versus-thickdb
Final Words on XSS

Cross-site scripting attacks are relatively simple to defend against. Related the Strategy #3 on the previous slide, it’s all about filtering and sanitizing your data:

  • 1. Put intelligent field-character limits and data-types on input fields.
  • 2. Don’t disable the ‘escape special characters’ settings in APEX fields.
  • 3. If you must ‘escape special characters’, use the APEX_ESCAPE.HTML_WHITELIST() function.
Date
Date : Dec. 23rd, 2017