Difference between revisions of "PL/SQL Security Cheat Sheet"

Revision as of 10:48, 27 April 2017

PL/SQL is a powerful procedural language built on top of Oracle SQL syntax. Extensive library of business-related and data-processing functions it incorporates makes it an attractive environment for building business-critical applications operating fully within the Oracle database. Introduction of PL/SQL Web Toolkit enabled Oracle developers to generate HTML straight from the PL/SQL code and build web applications fully residing from within the Oracle database.

Just as any other web stack, PL/SQL web applications require careful input validation and other standard safeguards to prevent exploitable OWASP Top 10 vulnerabilities. Oracle htp (hypertext procedures) and htf (hypertext functions) packages contain the primary functions for generating output in PL/SQL web applications as well as output escaping functions. See Oracle: The htp and htf Packages

Escaping

Applications running on newer Oracle versions where APEX packages are available should use apex_escape for contextual escaping of output data in a manner similar to ESAPI validators. See Oracle: apex_escape

• APEX_ESCAPE.HTML
• APEX_ESCAPE.HTML_ATTRIBUTE
• APEX_ESCAPE.HTML_TRUNC
• APEX_ESCAPE.HTML_WHITELIST
• APEX_ESCAPE.JS_LITERAL
• APEX_ESCAPE.LDAP_DN
• APEX_ESCAPE.LDAP_SEARCH_FILTER
• APEX_ESCAPE.NOOP

Applications should use htp.prints to output text blocks rather than htp.print as the former escapes potentially dangerous characters (<>"'). Note that the htp.prints cannot be used as a simple drop-in replacement for htp.print because it will also escape legitimate HTML but by htp usage model raw HTML shouldn't be generally entered in strings but rather generated with appropriate HTML functions (e.g. htp.header(1, 'Hello'); will output <H1>Hello</H1>).

Sample usage in typical PL/SQL code:

   htp.header(1, 'Details for user ' || apex_escape.html(username)); -- outputs <H1>...</H1><
   htp.print('Username: '); -- just a string literal, no need to escape
   htp.italic(apex_escape.html(username), 'class=' || apex_escape.html_attribute(userclass) );
   htp.para();
   htp.prints(address); -- escapes dangerous chars in address string
   htp.script ('var username="' || apex_escape.js_literal(username) || '";');

On older Oracle platforms htf.escape_sc for output in HTML context can be used and the utl_url.escape function is available to escape URL characters (&"<>%). URL escaping functionality is also provided by legacy htf.escape_url function. These functions are generally less robust than their apex_escape equivalents and not context-aware.

Authors

• Pawel Krawczyk

Other Cheatsheets