PL/SQL Security Cheat Sheet - Revision history

Dominique RIGHETTO: Migration to GitHub of the project

2019-02-16T10:15:47Z

Migration to GitHub of the project

Pawel Krawczyk: REGEXP_LIKE + DBMS_ASSERT

2017-04-27T13:45:24Z

REGEXP_LIKE + DBMS_ASSERT

Pawel Krawczyk at 13:03, 27 April 2017

2017-04-27T13:03:02Z

Pawel Krawczyk at 10:48, 27 April 2017

2017-04-27T10:48:45Z

Pawel Krawczyk at 10:48, 27 April 2017

2017-04-27T10:48:26Z

Pawel Krawczyk at 10:46, 27 April 2017

2017-04-27T10:46:11Z

Pawel Krawczyk: more samples

2017-04-27T10:43:18Z

more samples

Pawel Krawczyk at 10:38, 27 April 2017

2017-04-27T10:38:22Z

Pawel Krawczyk: code sample

2017-04-27T10:37:51Z

code sample

Pawel Krawczyk: initial version

2017-04-27T09:53:32Z

initial version

New page

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. [https://docs.oracle.com/cd/B14099_19/web.1012/b15896/pshtp.htm 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. [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 running on older versions of Oracle may use '''htp.prints''' to output text blocks rather than '''htp.print''' as the former escapes potentially dangerous characters (`<>"'`).

For escaping individual data fields `htf.escape_sc` for output in HTML context should be used. The `utl_url.escape` function is available to escape URL characters (`&"<>%`). URL escaping functionality is also provided by legacy `htf.escape_url` function but it's less robust.

==Authors==
* Pawel Krawczyk

== Other Cheatsheets ==

{{Cheatsheet_Navigation_Body}}

[[Category:Cheatsheets]]

@@ 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__
-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.

← Older revision		Revision as of 13:45, 27 April 2017
Line 27:		Line 27:

	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.		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==		==Authors==

@@ Line 3: / Line 3: @@
 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]
-==Escaping==
+==Escaping output data to prevent Cross-Site Scripting==
 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]

@@ Line 19: / Line 19: @@
 Sample usage in typical PL/SQL code:
-     htp.header(1, 'Details for user ' || apex_escape.html(username)); -- outputs &lt;H1&gt;...&lt;/H1&gt;<
+     htp.header(1, 'Details for user ' || apex_escape.html(username)); -- outputs &lt;H1&gt;...&lt;/H1&gt;
      htp.print('Username: '); -- just a string literal, no need to escape
      htp.italic(apex_escape.html(username), 'class=' || apex_escape.html_attribute(userclass) );

@@ 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.
-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. [https://docs.oracle.com/cd/B14099_19/web.1012/b15896/pshtp.htm Oracle: The htp and htf Packages]
+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]
 ==Escaping==
-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. [https://docs.oracle.com/database/121/AEAPI/apex_escape.htm Oracle: apex_escape]
+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
@@ Line 15: / Line 15: @@
 * APEX_ESCAPE.NOOP
-Applications running on older versions of Oracle may 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>&lt;H1&gt;Hello&lt;/H1&gt;</code>).
+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>&lt;H1&gt;Hello&lt;/H1&gt;</code>).
 Sample usage in typical PL/SQL code:
-     htp.header(1, 'Details for user ' || apex_escape.html(username));
+     htp.header(1, 'Details for user ' || apex_escape.html(username)); -- outputs &lt;H1&gt;...&lt;/H1&gt;<
      htp.print('Username: '); -- just a string literal, no need to escape
      htp.italic(apex_escape.html(username), 'class=' || apex_escape.html_attribute(userclass) );

@@ Line 20: / Line 20: @@
      htp.header(1, 'Details for user ' || apex_escape.html(username));
-     htp.print('<li>Username: ' || htp.italic(apex_escape.html(username), 'class=' || apex_escape.html_attribute(userclass) );
+     htp.print('Username: '); -- just a string literal, no need to escape
-     htp.prints(address);
+    htp.italic(apex_escape.html(username), 'class=' || apex_escape.html_attribute(userclass) );
      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.
+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.
 ==Authors==