DBA Hub

📋Steps in this guide1/2

UTL_URL : Escape and unescape strings in URLs

The UTL_URL package was introduced in Oracle 12.2 to help escape and unescape strings in URLs.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Escape

In its simplest form the function escapes all illegal characters the %XX format. In this example we encrypt a URL, converting a whitespace into a "%20". Setting the parameter to TRUE will escape reserved characters, as well as illegal characters. Notice how the ":" and "/" characters are also escaped in this example. In some situations it may be sensible to escape only the parameter values. In this example we use a URL with parameters, and set simple parameter values. Escaping the whole URL works fine. This time we make the second parameter value more complex, including "&", which is a reserved character. This time the output is not correct and the "&" in the second parameter should be escaped. We don't want to escape all reserved characters, so we need to escape the parameter values separately to get the correct result.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
set serveroutput on define off

begin
  dbms_output.put_line(utl_url.escape(url => '
http://oracle-base.com/my page.html
'));
end;
/
http://oracle-base.com/my
%20
page.html


PL/SQL procedure successfully completed.

SQL>

begin
  dbms_output.put_line(utl_url.escape(
                         url                   => 'http://oracle-base.com/my page.html',
escape_reserved_chars => true
));
end;
/
http%3A%2F%2Foracle-base.com%2Fmy%20page.html
PL/SQL procedure successfully completed.

SQL>

declare
  l_param1 varchar2(10) := 'yes';
  l_param2 varchar2(10) := 'no';
  l_url    varchar2(255);
begin
  l_url := 'http://oracle-base.com/my-service?param1=' || l_param1 || '&param2=' || l_param2;
  dbms_output.put_line(utl_url.escape(url => l_url));
end;
/
http://oracle-base.com/my-service?param1=yes&param2=no


PL/SQL procedure successfully completed.

SQL>

declare
  l_param1 varchar2(10) := 'yes';
  l_param2 varchar2(10) := 'yes&no';
  l_url    varchar2(255);
begin
  l_url := 'http://oracle-base.com/my-service?param1=' || l_param1 || '&param2=' || l_param2;
  dbms_output.put_line(utl_url.escape(url => l_url));
end;
/
http://oracle-base.com/my-service?param1=yes&param2=
yes&no
PL/SQL procedure successfully completed.

SQL>

declare
  l_param1 varchar2(10) := 'yes';
  l_param2 varchar2(10) := 'yes&no';
  l_url    varchar2(255);
begin
  l_url := 'http://oracle-base.com/my-service?param1=' || 
           utl_url.escape(l_param1, true) ||
           '&param2=' || utl_url.escape(l_param2, true);
  dbms_output.put_line(l_url);
end;
/
http://oracle-base.com/my-service?param1=yes&param2=
yes%26no
PL/SQL procedure successfully completed.

SQL>
2

Unescape

The function does the reverse of the function. In this example we unescape all the URLs we previously escaped. In a real situation, you would probably have a call into your code containing escaped parameter values, which you subsequently unescape. For more information see: - UTL_URL Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
begin
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my%20page.html'));
  dbms_output.put_line(utl_url.unescape('http%3A%2F%2Foracle-base.com%2Fmy%20page.html'));
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes&param2=no'));
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes&param2=yes&no'));
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes&param2=yes%26no'));
end;
/
http://oracle-base.com/my page.html
http://oracle-base.com/my page.html
http://oracle-base.com/my-service?param1=yes&param2=no
http://oracle-base.com/my-service?param1=yes&param2=yes&no
http://oracle-base.com/my-service?param1=yes&param2=yes&no


PL/SQL procedure successfully completed.

SQL>

create or replace procedure my_proc (
  p_param1 IN VARCHAR2,
  p_param2 IN VARCHAR2
)
as
  l_param1 varchar2(32767);
  l_param2 varchar2(32767);
begin
  l_param1 := utl_url.unescape(p_param1);
  l_param2 := utl_url.unescape(p_param2);

  -- Do something with the parameter values.
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!