Encode & Decode URLs Safely Using UTL_URL in PolarDB - PolarDB

The UTL_URL package provides two functions for encoding and decoding URLs in PL/SQL: ESCAPE converts special characters into percent-encoded sequences, and UNESCAPE reverses that conversion.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL (Compatible with Oracle) cluster running version 2.0 (revision 2.0.14.27.0 or later)

To check your revision version, run:

SHOW polar_version;

Functions

Function	Return type	Description
`ESCAPE(url, escape_reserved_chars, url_charset)`	VARCHAR2	Encodes a URL by replacing special characters with percent-encoded sequences.
`UNESCAPE(url, url_charset)`	VARCHAR2	Decodes a percent-encoded URL back to its original form.

Background

A URL can contain two categories of characters:

Non-reserved characters — safe to use as-is in any part of a URL:

A–Z a–z 0–9 - _ . ! ~ * ' ( )

Reserved characters — serve as delimiters in URL syntax. To use them as literal values rather than delimiters, they must be encoded:

Character	Escape sequence
`;`	`%3B`
`/`	`%2F`
`?`	`%3F`
`:`	`%3A`
`@`	`%40`
`&`	`%26`
`=`	`%3D`
`+`	`%2B`
`$`	`%24`
`,`	`%2C`
`[`	`%5B`
`]`	`%5D`

Any character outside these two categories — including multibyte characters and binary octets — is treated as illegal and is always encoded by ESCAPE.

ESCAPE function

Encodes a URL by converting each illegal character (and optionally each reserved character) into a %XX sequence, where XX is the character's ASCII value in hexadecimal.

Syntax

FUNCTION escape(
    url                   IN VARCHAR2,
    escape_reserved_chars IN BOOLEAN DEFAULT FALSE,
    url_charset           IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;

Parameters

Parameter	Description
`url`	The URL to encode.
`escape_reserved_chars`	Whether to also encode reserved characters. Set to `TRUE` when encoding a value that will be embedded in a URL query string. Default: `FALSE`.
`url_charset`	The character set used to interpret multibyte characters before encoding. Default: null.

Examples

Encode non-ASCII characters (reserved characters left intact)

When escape_reserved_chars is FALSE (the default), ESCAPE encodes only illegal characters such as multibyte characters, leaving reserved characters like / and : in place. Use this when encoding a complete URL.

DECLARE
    res VARCHAR2(400);
BEGIN
    res := utl_url.escape(
               url                   => 'https://www.aliyun.com/数据库',
               escape_reserved_chars => false,
               url_charset           => 'utf8');
    DBMS_OUTPUT.PUT_LINE(res);
END;

Output:

https://www.aliyun.com/%E6%95%B0%E6%8D%AE%E5%BA%93

Encode a query string value (reserved characters included)

When escape_reserved_chars is TRUE, ESCAPE also encodes reserved characters. Use this when encoding a value that contains reserved characters and will be concatenated into a URL query string.

DECLARE
    res VARCHAR2(400);
BEGIN
    res := utl_url.escape(
               url                   => 'https://www.aliyun.com/数据库',
               escape_reserved_chars => true,
               url_charset           => 'utf8');
    DBMS_OUTPUT.PUT_LINE(res);
END;

Output:

https%3A%2F%2Fwww.aliyun.com%2F%E6%95%B0%E6%8D%AE%E5%BA%93

UNESCAPE function

Decodes a percent-encoded URL back to its original form.

Syntax

FUNCTION unescape(
    url         IN VARCHAR2,
    url_charset IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;

Parameters

Parameter	Description
`url`	The percent-encoded URL to decode.
`url_charset`	The character set used to interpret decoded multibyte characters. Default: null.

Example

Encode a URL and then decode it back to its original form:

DECLARE
    res VARCHAR2(400);
BEGIN
    res := utl_url.escape(
               url                   => 'https://www.aliyun.com/数据库',
               escape_reserved_chars => false,
               url_charset           => 'utf8');
    DBMS_OUTPUT.PUT_LINE('URL after escape:' || res);

    res := utl_url.unescape(
               url         => res,
               url_charset => 'utf8');
    DBMS_OUTPUT.PUT_LINE('URL after unescape:' || res);
END;

Output:

URL after escape:https://www.aliyun.com/%E6%95%B0%E6%8D%AE%E5%BA%93
URL after unescape:https://www.aliyun.com/数据库