|
|
| Line 1: |
Line 1: |
| − | 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.
| + | __NOTOC__ |
| | + | <div style="width:100%;height:160px;border:0,margin:0;overflow: hidden;">[[File:Cheatsheets-header.jpg|link=]]</div> |
| | | | |
| − | 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 <code>htp</code> (hypertext procedures) and <code>htf</code> (hypertext functions) packages contain the primary functions for generating output in PL/SQL web applications as well as output escaping functions. See [https://docs.oracle.com/cd/B14099_19/web.1012/b15896/pshtp.htm Oracle: The htp and htf Packages]
| + | The Cheat Sheet Series project has been moved to [https://github.com/OWASP/CheatSheetSeries GitHub]! |
| | | | |
| − | ==Escaping output data to prevent Cross-Site Scripting==
| + | An [https://github.com/OWASP/CheatSheetSeries/issues/13 open discussion] is pending about to exclude or not this cheat sheet of the V2 of the project. |
| − | Applications running on newer Oracle versions where APEX packages are available should use <code>apex_escape</code> for contextual escaping of output data in a manner similar to [[ESAPI]] validators. See [https://docs.oracle.com/database/121/AEAPI/apex_escape.htm 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 <code>htp.prints</code> to output text blocks rather than <code>htp.print</code> as the former escapes potentially dangerous characters (<code><>"'</code>). Note that the <code>htp.prints</code> cannot be used as a simple drop-in replacement for <code>htp.print</code> because it will also escape legitimate HTML but by <code>htp</code> usage model raw HTML shouldn't be generally entered in strings but rather generated with appropriate HTML functions (e.g. <code>htp.header(1, 'Hello');</code> will output <code><H1>Hello</H1></code>).
| |
| − | | |
| − | 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 <code>htf.escape_sc</code> for output in HTML context can be used and the <code>utl_url.escape</code> function is available to escape URL characters (<code>&"<>%</code>). URL escaping functionality is also provided by legacy <code>htf.escape_url</code> function. These functions are generally less robust than their <code>apex_escape</code> equivalents and not context-aware.
| |
| − | | |
| − | ==Input validation and sanitization==
| |
| − | ===Regular expression functions===
| |
| − | IF REGEXP_LIKE('untrusted input', '^[0-9a-zA-z]{2,6}$') THEN /* Match */ ELSE /* No match */ END IF;
| |
| − | select REGEXP_REPLACE('subject<<>>', '[<>]') from dual; -- returns: "subject"
| |
| − | | |
| − | | |
| − | ===DBMS_ASSERT===
| |
| − | * ENQUOTE_LITERAL — Enquotes a string literal
| |
| − | * ENQUOTE_NAME — Encloses a name in double quotes
| |
| − | * NOOP — Returns the unmodified value
| |
| − | * QUALIFIED_SQL_NAME — Verifies that the input string is a qualified SQL name
| |
| − | * SCHEMA_NAME — Verifies that the input string is an existing schema name
| |
| − | * SIMPLE_SQL_NAME — Verifies that the input string is a simple SQL name
| |
| − | * SQL_OBJECT_NAME — Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object
| |
| − | | |
| − | Example:
| |
| − | | |
| − | SELECT SYS.DBMS_ASSERT.SIMPLE_SQL_NAME ('Data with <invalid> characters') FROM dual;
| |
| − | ORA-44003: invalid SQL name
| |
| − | | |
| − | See [https://docs.oracle.com/database/121/ARPLS/d_assert.htm#ARPLS231 Oracle: DBMS_ASSERT]
| |
| − | | |
| − | ==References==
| |
| − | * [http://www.oracle.com/technetwork/database/features/plsql/overview/how-to-write-injection-proof-plsql-1-129572.pdf Oracle "How to write SQL injection proof PL/SQL"]
| |
| − | * [http://www.oracle.com/technetwork/developer-tools/adf/adfowasptop10-final-2348304.pdf Security in Oracle ADF: Addressing the OWASP Top 10 Security Vulnerabilities]
| |
| − | | |
| − | ==Authors==
| |
| − | * Pawel Krawczyk
| |
| − | | |
| − | == Other Cheatsheets ==
| |
| − | | |
| − | {{Cheatsheet_Navigation_Body}}
| |
| − | | |
| − | [[Category:Cheatsheets]]
| |
