We get asked by people if we can recover customers PL/SQL quite a few times a year. This is because they no longer have access to the original clear text PL/SQL. We can of course get this code back for customers successfully. Most are asking about wrapped code from wrap.exe 9.2.0.8 or earlier.
Some ask us can we prove that the original code is really recovered properly? In the case of 10g and above we don't as such need to prove this because the 10g and above mechanism is not encryption as such (ok very weak) and it just puts the original code back. The only real advantage of 10g and 11g wrapping over 9i and lower is that the symbol table is no longer visible but the algorithm is much weaker.
The 9.2.0.8 and earlier wrap.exe is more complex as the process / the file created is the intermediate stage of the PL/SQL compiler written out to disk. I talked about this at
Black Hat in Las Vegas many years ago in some details. Oracle also provided a sample unwrapper for years in the PSTUB function shipped with the database and also because it’s actually just ADA and the DIANA nodes used in PL/SQL are mostly the same as ADA for a lot of syntax. ADA unwrappers have existed for many years.
So for 9.2.0.8 how can we prove without access to the original source code that we have properly unwrapped and retrieved the missing code?
One way is to start with a wrapped PL/SQL file; then unwrap this file and get the clear text code back. Then wrap this new clear text file with the same version of wrap.exe used to create the original file. Then finally compare the two wrapped files; if they are the same we have correctly recovered the original source code. Complete recovery of source is possible and it is provable and indeed my clients are impressed and hire us because we can prove their code is recovered. I have created a sample test case here to show this process.
First create a simple procedure to use for this test case. The code is just made up for this experiment:
SQL> get sample1.sql
1 create or replace procedure test_proc (pv_num in number,
2 pv_var in varchar2, pv_var3 in out integer) is
3 l_num number:=3;
4 l_var number;
5 j number:=1;
6 procedure nested (pv_len in out number)
7 is
8 x number;
9 begin
10 x:=pv_len*5;
11 end;
12 begin
13 case l_num
14 when 1 then
15 --
16 l_var:=3;
17 dbms_output.put_line('This is a header');
18 dbms_output.put_line('The number is '||l_var);
19 dbms_output.put_line('The case var is '||l_num);
20 when 2 then
21 --
22 l_var:=4;
23 dbms_output.put_line('This is a header');
24 dbms_output.put_line('The number is '||l_var);
25 dbms_output.put_line('The case var is '||l_num);
26 when 3 then
27 --
28 l_var:=6;
29 dbms_output.put_line('This is a header');
30 dbms_output.put_line('The number is '||l_var);
31 dbms_output.put_line('The case var is '||l_num);
32 else
33 dbms_output.put_line('wrong choice');
34 end case;
35 if ((j=1) and (j=3)) then
36 dbms_output.put_line('here is IF');
37 elsif ((j=2) or (j!=3)) then
38 dbms_output.put_line('The elsif clause');
39 else
40 dbms_output.put_line('else clause');
41 end if;
42 j:=4;
43 nested(j);
44 dbms_output.put_line('nested=:'||j);
45 for j in reverse 1..pv_num
46 loop
47 if mod(j,2) = 0 then
48 dbms_output.put_line('for loop with reverse');
49 end if;
50 end loop;
51* end;
SQL>
I can then wrap this with the 9i wrap utility:
C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>wrap iname=sample1.sql oname=sample1.plb
PL/SQL Wrapper: Release 9.2.0.1.0- Production on Mon Jun 01 14:02:34 2021
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing sample1.sql to sample1.plb
C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>head sample1.plb
Then I can show it is indeed wrapped by viewing the contents (Note the above commands are in a DOS box, the head command is on the same machine but from cygwin as the head command is available:
$ head -20 sample1.plb
create or replace procedure test_proc wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
9200000
Now load the wrapped file into an Oracle database and check its stored wrapped:
SQL> @sample1.plb
Procedure created.
SQL> select substr(text,1,60)
2 from dba_source
3 where name='TEST_PROC'
4 and rownum=1;
SUBSTR(TEXT,1,60)
------------------------------------------------------------
procedure test_proc wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
SQL>
Now we can simply unwrap it using my PL/SQL based unwrapper:
SQL> @unwrap_c
unwrap_c: Release 1.4.0.0.0 - Production on Mon Jun 01 14:07:13 2021
Copyright (c) 2008, 2021 PeteFinnigan.com Limited. All rights reserved.
NAME OF OBJECT TO CHECK [P1]: TEST_PROC
OWNER OF OBJECT TO CHECK [SYS]: SYS
TYPE OF THE OBJECT [PROCEDURE]: PROCEDURE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
create or replace procedure TEST_PROC( PV_NUM in NUMBER,
PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) is
L_NUM NUMBER:=3;
L_VAR NUMBER;
J NUMBER:=1;
procedure NESTED( PV_LEN in out NUMBER) is
X NUMBER;
begin
X:= PV_LEN * 5;
end;
begin
case L_NUM
when 1 then
L_VAR:=3;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 2 then
L_VAR:=4;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
when 3 then
L_VAR:=6;
DBMS_OUTPUT. PUT_LINE('This is a header');
DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
else
DBMS_OUTPUT. PUT_LINE('wrong choice');
end case;
if ( ( J = 1) and ( J = 3)) then
DBMS_OUTPUT. PUT_LINE('here is IF');
elsif ( ( J = 2) or ( J != 3)) then
DBMS_OUTPUT. PUT_LINE('The elsif clause');
else
DBMS_OUTPUT. PUT_LINE('else clause');
end if;
J:=4;
NESTED( J);
DBMS_OUTPUT. PUT_LINE('nested=:' || J);
for J in reverse 1.. PV_NUM loop
if MOD( J,2) = 0 then
DBMS_OUTPUT. PUT_LINE('for loop with reverse');
end if;
end loop;
end;
/
INFO: Elapsed time = [.1 Seconds]
PL/SQL procedure successfully completed.
For more information please visit
SQL>
Then the code can be taken and wrapped again with the same 9i wrap utility, first cut and paste the code and then save as a file sample2.sql:
SQL> get sample2.sql
1 create or replace procedure TEST_PROC( PV_NUM in NUMBER,
2 PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) is
3 L_NUM NUMBER:=3;
4 L_VAR NUMBER;
5 J NUMBER:=1;
6 procedure NESTED( PV_LEN in out NUMBER) is
7 X NUMBER;
8 begin
9 X:= PV_LEN * 5;
10 end;
11 begin
12 case L_NUM
13 when 1 then
14 L_VAR:=3;
15 DBMS_OUTPUT. PUT_LINE('This is a header');
16 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
17 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
18 when 2 then
19 L_VAR:=4;
20 DBMS_OUTPUT. PUT_LINE('This is a header');
21 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
22 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
23 when 3 then
24 L_VAR:=6;
25 DBMS_OUTPUT. PUT_LINE('This is a header');
26 DBMS_OUTPUT. PUT_LINE('The number is ' || L_VAR);
27 DBMS_OUTPUT. PUT_LINE('The case var is ' || L_NUM);
28 else
29 DBMS_OUTPUT. PUT_LINE('wrong choice');
30 end case;
31 if ( ( J = 1) and ( J = 3)) then
32 DBMS_OUTPUT. PUT_LINE('here is IF');
33 elsif ( ( J = 2) or ( J != 3)) then
34 DBMS_OUTPUT. PUT_LINE('The elsif clause');
35 else
36 DBMS_OUTPUT. PUT_LINE('else clause');
37 end if;
38 J:=4;
39 NESTED( J);
40 DBMS_OUTPUT. PUT_LINE('nested=:' || J);
41 for J in reverse 1.. PV_NUM loop
42 if MOD( J,2) = 0 then
43 DBMS_OUTPUT. PUT_LINE('for loop with reverse');
44 end if;
45 end loop;
46* end;
SQL>
Now the 9i wrap utility can be used to wrap the file again; this time to sample2.plb:
C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>wrap iname=sample2.sql oname=sample2.plb
PL/SQL Wrapper: Release 9.2.0.1.0- Production on Mon Jun 01 14:10:12 2021
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing sample2.sql to sample2.plb
C:\pete_finnigan_com_ltd\scanner-MASTER\unwrapper>
We now have two wrapped files. The one created originally from the PL/SQL source code and now the second created from the unwrapped code. Now use the diff command again under cygwin on the same machine to compare the files:
$ diff -i -w sample1.plb sample2.plb
146c146
< 16 21 25 :2 16 15 :2 1 2 :2 8
---
> 16 21 25 :2 16 14 :2 1 2 :2 8
148,168c148,168
< 2 c 14 1e 22 :2 14 13 :2 2
< 3 :3 5 :2 3 6 c d :2 6 3
< :6 2 7 8 4 b :2 4 :2 10 19
< :3 4 :2 10 19 29 2b :2 19 :3 4 :2 10
< 19 2b 2d :2 19 :2 4 a :2 3 8
< 4 b :2 4 :2 10 19 :3 4 :2 10 19
< 29 2b :2 19 :3 4 :2 10 19 2b 2d
< :2 19 :2 4 a :2 3 8 4 b :2 4
< :2 10 19 :3 4 :2 10 19 29 2b :2 19
< :3 4 :2 10 19 2b 2d :2 19 :2 4 a
< :2 3 4 :2 10 19 :2 4 3 :3 2 7
< 8 9 :2 8 6 11 12 13 :2 12
< 10 :2 6 5 3 :2 f 18 :2 3 2
< 17 a b c :2 b 9 13 14
< 16 :2 14 12 :2 9 8 3 :2 f 18
< :2 3 1a 17 3 :2 f 18 :2 3 :5 2
< 5 :2 2 9 :3 2 :2 e 17 21 23
< :2 17 :2 2 6 b 13 16 2 13
< 2 6 a c :3 6 f 11 :2 f
< 4 :2 10 19 :2 4 13 :2 3 2 6
< 2 :9 1
---
> 2 b 13 1d 21 :2 13 11 :2 1
> 2 :3 4 :2 2 6 d f :2 6 2
> :6 1 6 7 2 9 :2 2 :2 f 18
> :3 2 :2 f 18 29 2d :2 18 :3 2 :2 f
> 18 2b 2f :2 18 :2 2 9 :2 2 7
> 2 9 :2 2 :2 f 18 :3 2 :2 f 18
> 29 2d :2 18 :3 2 :2 f 18 2b 2f
> :2 18 :2 2 9 :2 2 7 2 9 :2 2
> :2 f 18 :3 2 :2 f 18 29 2d :2 18
> :3 2 :2 f 18 2b 2f :2 18 :2 2 9
> :3 2 :2 f 18 :2 2 :4 1 8 a c
> :2 a 6 15 17 19 :2 17 13 :2 6
> 4 2 :2 f 18 :2 2 1 1d b
> d f :2 d 9 17 19 1c :2 19
> 15 :2 9 7 2 :2 f 18 :2 2 20
> 1d 2 :2 f 18 :2 2 :4 1 2 5
> :2 2 a :3 2 :2 f 18 23 27 :2 18
> :2 2 5 a 12 16 1d 12 1
> 4 9 b :3 4 e 10 :2 e 2
> :2 f 18 :2 2 12 :2 1 1d 5 :a 1
>
173,185c173,185
< :5 8 :7 a :2 9 :4 6
< d e :3 10 :6 11
< :a 12 :a 13 :3 e 14
< :3 16 :6 17 :a 18 :a 19
< :3 14 1a :3 1c :6 1d
< :a 1e :a 1f :3 1a :6 21
< 20 :3 d :f 23 :6 24
< 25 23 :f 25 :6 26
< 25 23 :6 28 27
< :3 23 :3 2a :4 2b :a 2c
< :4 2d 2e :2 2d :a 2f
< :6 30 :3 2f 2e 32
< 2d :2 c :7 1
---
> :5 7 :7 9 :2 8 :4 6
> c d :3 e :6 f
> :a 10 :a 11 :3 d 12
> :3 13 :6 14 :a 15 :a 16
> :3 12 17 :3 18 :6 19
> :a 1a :a 1b :3 17 :6 1d
> 1c :3 c :f 1f :6 20
> 21 1f :f 21 :6 22
> 21 1f :6 24 23
> :3 1f :3 26 :4 27 :a 28
> :7 29 :a 2a :6 2b :3 2a
> 29 2d 29 :2 b
> :7 1
The above dump shows the output of the differences.
It seems on the face of it that there are a lot of differences and therefore the code was not recovered properly but these differences are not a problem and are in fact just the “rows” and “columns” held in the IDL for the original source code. One of the attributes of every DIANA node is that it stores the original source code position of the PL/SQL language element it has translated to DIANA. Because the wrapper only does “its best” to reconstruct the actual row/columns positions for all keywords and variables and in fact we don’t really care about whether the keyword “FOR” or “IF” is in the same place as the original as this does not affect the functionality of the code itself.
There are differences shown above but the functional part of the wrapped code are all generated exactly the same as the original. Therefore the code was completely recovered. A look at the sample1.sql and the sample2.sql files shows the differences in the PL/SQL level in terms of line and column positions.
In a real situation of course we don’t start with PL/SQL source code as in this simple example but start with just a wrapped file of lost source code; this can be anything from 7.2.3 to 9.2.0.8. This just removes the first steps from the process shown above; so that we start with a wrapped file, unwrap it, wrap the results and then compare the two wrap files.
To completely recover PL/SQL from wrapped PL/SQL it is necessary to compare the before and after wrapped files to test that the code is the same except for row/col positions. The process can still have issues if the original wrap binary used (say for a very early version of Oracle) is not available to wrap the code a second time. This can cause slight discrepancies if a later wrap is used for testing % complete source recovery.
Our service to recover PL/SQL source code is
here.
