Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Joel Kalllman Day

I saw a few tweets yesterday about Joel Kallman and liked a few and shared one (maybe two) and then I saw Tim Halls post that talked about Joel Kallman day. I decided to do a quick blog now to support this event that Tim has been running even though it ended yesterday; Sorry, I could not do it yesterday. ##

I didn't know Joel well and we had met and spoken at least 3 times over the years but possibly more, I just cannot recall exactly; two occasions I do remember. The first time we spoke in person was at DOAG a few years ago. I was there with a reseller of our scanner PFCLScan and was doing demos on the Loopback stand there. Joel came up to me and introduced himself and shook my hang vigorously and said "you are a Rockstar; but don't tell anyone in Oracle I said that" and he had a broad smile. We chatted for a while and I was immediately struck by his absolute friendliness and his great attitude to everyone around and the "subject" of Oracle in general; he was a great person.

I then spoke to Joel again after that at a UKOUG event where he came to find me because he had heard through the grape vine that I was going to include an exploit in Apex in my talk of that day. The grape vine wasn't strictly true. Yes I was going to use Apex as an example of a problem but it was not a bug in the traditional sense in Apex. The issue was that the full design implementation of Apex includes a lot of rights for the APEX_% schema and I was going to show a issue of how a privilege can be stolen. Joel was very gracious and courteous even before I explained my talks example. He was a great person, very friendly. We then talked about all sorts of things (all related to Oracle of course) and even about the key design decision in the core of Apex to use DBMS_SYS_SQL.

I definitely remember speaking to Joel somewhere else but I cannot remember now where or the details.

Great guy, sadly missed and a fantastic ambassador for Oracle.

#JoelKallmanDay #oracle #security #oracleace @oracleace @groundbreakers @OracleDatabase @OracleAPEX #orclapex @OracleSQLDev

Designing Good Audit Trails for an Oracle Database

I have been asked to speak at the UKOUG Autumn Tech event. This is an online conference event and the agenda grid is live and I will speak at 15:00 to 15:45 BUT the link to the details of my talk is incorrect as it points to a Graham Spicers talk. I have asked UKOUG to fix this but it doesn't matter for now as I can discuss the contents of the talk here.

The talk is all about building a good audit trail for your Oracle database. What I mean by good is a well designed audit trail and not one simply based on a set of technical settings BUT a well designed and thought out audit trail. First, who are you trying to satisfy? external regulations or even internal ones; do you want to be reactive - i.e. simply collect and store, do you want to be pro-active - i.e. use the audit trail in real time or semi real time to react to an incident and block or stop it. If you have to comply with regulations to gather certain audit trail evidence then there is no reason that you should simply comply with the regulations - often they are not good enough to use the audit to investigate a breach or to detect a breach. This is because they are designed by committee not designed by you and for your business.

One area I will focus on, then, of course is the good design. The audit trail needs to be designed first in terms of events; the events that you want to capture and we discuss these first as well as some sample events that I feel should be included. These are at a business level in a table in an MS Word document; these are not audit settings in the database. Once we have the list of events we can then decide what the technical solution is going to be (standard audit, unified audit, FGA, third party... ) and as part of this we decide what raw audit to be collected and how, then how to mine that raw audit to see if the event has occurred and we also bring in reporting and escalation and alerts. This is a designed audit not a list of random settings recommended by someone else.

I will also show the results of some hacking and what is captured with the base standard audit settings from Oracle and then implement a good set of policies in my database and show that the hacking is now captured in the audit trail.

Come along and learn about audit trails and good designs for capturing activity in the core database engine.

Happy 17th Birthday to this Oracle Security Blog

It is almost 17 years since I started this blog on the 20th of September 2004. I had actually already been sort of blogging without blog software before that since 10th February 2004 with my ramblings section of my website. The ramblings section was a list of posts/articles so was the forerunner to my blog when I installed but the site in general was a site to promote Oracle Security articles and papers.

The first post on the ramblings section was how to disable iSQLPlus which is not relevant anymore.

The first post to the blog in September 2004 was a simple introduction and why I wanted to blog and what i would include in this blog. The post was titled A new Oracle Security based weblog.

I was late to the party in blogging as the first blog is reputedly said to be by Justin Hall who recorded his life on his personal website but it was known as a blog and the term weblog was not used until 1997; first by Jorn Barger. WordPress did not launch until 2003, one year before my Oracle security blog but I used Greymatter blog software which was one of the first proper blog softwares to be released in 2000. The Wikipedia page even mentions me twice as I created some of the mods and also hosted the software for download as well as helped develop it for a while with Coldstone. I chose Greymatter because it is static and does not use a database unlike other popular blog packages of the time such as WordPress and Typepad and Movable Type. I liked Greymatter because it was configurable and because it was static and therefore not SQL Injectable.

I have posted some 1484 posts (including this post) during the 17 years; not as many in recent years as I did at the start. That is 87 posts a year on average, not bad! For quite some time at the start I managed to do a post a day but that dropped off in recent years and now I am trying to build that back up a bit again - but not to one post a day; I will never do that again as I am too busy with other work.

There are not many people who blog about Oracle Security and very few who did/do blog about Oracle security specifically that's why I keep covering this subject that is still very interesting to me especially in the times of data breaches and identity theft and more.

I also post to our social media profiles so also please feel free to visit these as well and also share, like and follow us there on these profiles:

Register for a Free Webinar with PFCLForensics for Breached Oracle Databases

I will be giving a free webinar hosted with our reseller/distributer in Slovenia and the Balkans region - Palsit. The free webinar is at 09:00 UK time or 10:00 CET time on the 22nd September 2021. In this webinar I am going to do a live walk through and do a live demonstration of our new product PFCLForensics. This will show the three core areas of the product:

  • Manage the Oracle database incident: The incident team can use the easy to use checklist to manage the response to any potential breach of the Oracle database

  • Live Response on an Oracle Database: Extract evidence and artefacts from the targeted Oracle database using our built in tools so that the most volatile data can be extracted first so making less impact on the database and future evidence. All extracted data is checksummed to allow a consistent view of the data

  • Forensic Analysis of an Oracle Database: Analyse all of the extracted evidence and create a timeline of events and artefacts. Also create a supporting timeline to allow surrounding data to be extracted. Use the time sync feature to ensure that all evidence is aligned with wall time. Use the reporting features to create a report of the attack



PFCLForensics is suitable for internal security teams, external investigators who are responsible to provide support and analysis during a breach response. Consulting companies can also use this toolset to provide support during a breach of an Oracle database for their clients.

PFCLForensics is also useful for internal and external teams to test and practice your response to a potential breach of your Oracle databases

To join this free webinar please visit the registration page. The webinar is in English.

PFCLForensics is released a tool for forensic analysis of a breached database

We have had a very busy year despite the Covid pandemic. I personally managed to catch covid last January and was very unwell for weeks with coviid and then many many weeks recovering after that. Then I managed to get problems with my kidneys that resulted in some surgery a couple of months ago. I am very well now. Despite these set backs we have written a new product to do live response and forensic analysis of a breached Oracle database and we made some major updates to one of our other products PFCLObfuscate to add new features as well as we added updates and fixes to other products as well

PFCLForensics showing a simple open project


Today we have released a new product - PFCLForensics. This is a product aimed at helping customers do three things in the event of a breach or potential breach of their Oracle database:

  • Manage the breach: with a built in check list of actions for the responder to follow. The product is project based so you can work on multiple breaches and tasks at the same time

  • Perform live response: with built in policies that allow the most transient data to be gathered first from an Oracle database or a Unix/Linux server. There are also built in policies to get less transient data from the breached systems. The product also allows files to be loaded that could be part of the breach and allow sthe user to define rules for their own file types and load additional data as necessary. The tool also takes checksums of each piece of data and validates those every time the project is loaded or on demand by the user to ensure that the raw data has not changed

  • Forensic analysis: with lots of built in tools. The user can filter and sort the input data gathered and look for issues related to the breach. Potential evidence can be added to the timeline for the attack and even comments added to each artefact. The timeline can be viewed as a drillable graph of evidence over time or a complete end to end graph of the even distinctly showing how the attack occurred. Supporting evidence can be added to a separate time line. The tool also includes a built in word processor and sample report template so that you can easily write your report and have direct access to evidence and artefacts as well as timelines. The timelines can also be sync'd to wall time easily so that all disparate evidence is correlated on the same time; each piece of evidence as it is added is also correlated with all previous evidence


This is a great tool to help follow a process if a breach occurs and also to collect and analyse and report on any breach that may have occurred. The product details start here and you can follow the links there to learn more. There is also a 2 page flyer that can be downloaded.

Our engagement licenses are the best value for 30 days and just £145 per product. Contact us to purchase a license or to ask to see a demo before buying.

Should We Security Patch Oracle Databases?

Spoiler: Of course!

Security patching of Oracle databases can be a touchy and complex subject for some companies. It is perceived to be complex; companies don’t want the downtime; business is worried that a security patch can break the applications; the application vendor maybe says his application is only certified with a base patch set version and so on.

There is also this perceived lack of customer knowledge of what Oracle has actually fixed in a security patch. I remember detailed and heated discussions at conferences and online at the start of the CPU regime many years ago where people refused to patch (seemingly) because they didn’t know what the patch fixed. In one sense I can understand this; if a customer finds a functional bug in Oracle and raised an SR and a patch wings its way to them; they know for sure if it fixed the functional issue or not. They can test it.

There seemed to be this logic to security patches at the time and after that because a customer didn’t know what a security patch fixed in any detail, they could not test it or be sure it would not affect their application. Oracle or indeed other vendors are not going to give out a list of security vulnerabilities and hacks. This, of course would then be useful to hackers of databases that have not been patched. Oracle do credit researchers in their advisories for security patches but usually not enough details to show how the issue can occur. I have been credited a number of times on CPU advisories and I know what the issue was as I found it each time BUT the customers didn’t and I didn’t know what other fixes were for. Of course, Oracle do not credit their own staff who find and fix security bugs released in the same patches.

We as customers must just get over this fact and accept we will never know exactly what issues were fixed in every security patch release.
Researchers and hackers can reverse engineer each released patch. This is not trivial with C language binaries or DLL/SO but it is not impossible for skilled researchers. I don’t do this so please don’t bother to tell me the legalities in relation to the license agreement; I am just stating people can do it and have done it but not me. Where the patch involves PL/SQL packages then its even easier to reverse engineer the patched PL/SQL as there are unwrappers on the internet.

Why mention reverse engineering?

Well, we will come back to that in a minute.

I have been conducting detailed security audits of Oracle databases for more than 20 years. When I started there was little out there on securing Oracle or indeed hacking Oracle in terms of books or papers or products or tools. People and companies told me that they only had a budget for firewalls or desktop security and whilst when speaking to companies and people at these companies they agreed it’s good to focus on data security and Oracle security they didn’t have time or a budget for it.

At that same time 20 years ago, Oracle had not started releasing any security patches yet; first came alerts " random security patches that got bigger and bigger up until the regimented quarterly security patches were started to be released.

Since the release of quarterly patches, I have asked groups of people at conference talks and also at my trainings whether they apply security patches. The results of these polls are usually between 10% and 30% of people apply patches. Of that group who have applied a patch I also ask who applies patches regularly; once a quarter or 6 months or even once a year. Always there is a larger subset of those who do patch but only once.

In other words, a smaller percentage of customers apply security patches regularly. This is not a good statistic but its not scientific and I didn’t talk to everyone who runs Oracle. Certainly, my experience of asking “do you patch or don’t you?” question is consistent across the years and it’s not changing.

What about databases I have seen; I have seen a lot! They sadly tend to follow a similar pattern as asking people these questions. A lot of customers don’t patch at all and some only patch once after the initial build or after a major upgrade but nothing in-between and some strangely patch the day before I come on site to do a security audit. A small number patch regularly but not enough do.

What should we do to secure data in an Oracle database; well at a high level we should focus on these elements:

  • Patch the Oracle database software

  • Harden the database

  • Implement access controls to the database; this could be network level or database level such as valid node checking or logon triggers

  • Implement user security, least rights

  • Implement data access controls

  • Implement context based security to allow fine grained access to resources and data



The first three are about platform security where Oracle is the platform and the last three are data security design aimed at securing the actual data.
In terms of security patches what can you do at a high level:

  • Patch regularly

  • Patch randomly

  • Don’t patch at all or maybe once the database was built or after a major update of an application or database

  • Try and work around not patching

  • Buy software that tries to replace the patch with a blocking or logging action



My experience is that most people don’t patch regularly or if they do it’s not regular. I do see some sites that do patch and have some semblance of regularity or at least not completely random. Some sites apply patches once a quarter or six monthly.

Not patching (or patching regularly) but also not even trying to work around the fact that there is no security patch is also not good enough. There are often no work arounds for security bugs fixed in quarterly security patches and some issues fixed are where an attacker can exploit the database without credentials or authenticating. These types of issues are dangerous and the only workaround in this case would be to prevent direct TNS access to the database; but what about the genuine database users who may need direct TNS access; my experience is that a large number of sites use open routing and blocking access would be difficult anyway. One step that can be done is limiting access to a list of known IP Addresses using tools such as valid node checking BUT this is a potential partial fix for one problem. What if a patch fixes issues in PL/SQL packages? Then valid node checking would not work; what could we do in this case; limit access to the package is possible but only if it’s not used genuinely in your application.

And the list goes on. Yes, we can do really good database security design and hardening and lock down access to everything and this may reduce the risk of exploitation of something fixed by a security patch but it’s never going to be perfect.

Another option that started to appear around 15 years ago is the idea of virtual patching. This is the idea that you cannot or don’t want to patch with Oracle’s security patch so you deploy network software that is a special version of an application firewall or intrusion detection / prevention system. The way this works is that network packets are sniffed (or shared memory attached and parsed) and attacks that could exploit issues fixed in Oracle’s security patch are detected. This is complex and prone to error or hacker bypass and requires the vendors of the virtual patch to reverse engineer (or guess!) what Oracle has fixed; then work out how that fix could be exploited and then how an exploit that could hack the database software can be detected.

As I stated above Oracle do not release details of what is fixed so this virtual patching is not perfect and involves a lot of work. Yes, I can see that a product such as this maybe be a vert short term barrier to when a patch of a particular system cannot be applied quickly but its not a perfect fix.

So, patch and apply Oracle’s security patches if you can; it makes sense; it builds belt and braces solutions and an underpinning of security of the Oracle software that hardening and data security design can be built on.

If your major concern is that an Oracle security patch may cause a performance issue or break something functionally if you apply it then one method is this; Wait until patch Tuesday when Oracle release the next security patch and then download the previous quarters patch and apply it; application testing as necessary. The logic of this is to allow everyone else to test Oracle’s patch for you over the previous quarter.

For me, it is important that security patches are applied. Security patches form part of the underpinning of data security (patch and harden) on which we build data security design (user controls, data access controls, context based security). I can appreciate the time needed if you must perform regression tests of applications but a suitable method must be created that allows regular security patching; this can be:

  • Apply within the quarter every quarter

  • Apply every quarter but apply the last quarter patch to allow for others testing it

  • Apply regularly on a longer timescale such as every six months



Workarounds can reduce the risk but cannot replace patches and virtual patches may be fine for very short periods but cannot replace actual patches.
The most important message is patch and patch within a regular regime BUT don’t forget all of the other security tasks for your data; hardening, user security, access controls, data security and context based security. There is no point patching once and then never again or patching before a security audit and never again.

Unwrapping PL/SQL Source Code and Proving the Code is Recovered

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 http://www.petefinnigan.com

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.