Saturday, September 02, 2006

Protecting PL/SQL Code

There are two broad categories of reasons why PL/SQL programmers wouldn't want others to read their code:

Reason #1 Protection
The programmers wish to protect their code from theft, misuse or alteration (among other things).

Reason #2 Malicious Reasons
The code is meant to do harm, or may have some potentially dangerous errors they wish to cover up.

I don't believe in hidden code, because open code is easier to debug, reuse, and it is obviously easier to detect (deliberately or accidentally) harmful code.

But regardless of whether code you have received from a vendor or contractor is protected or not, you should be testing it thoroughly before using it anyway. Plus, these methods are already thoroughly documented in a number of places, not the least of which by expert Steven Feuerstein and also in Oracle's documentation.

In fact, the example I'm using is from Oracle's documentation.

Normally, after creating a stored procedure, the source code can be access through a SOURCE table, like so:

SQL> SELECT text FROM USER_SOURCE
SQL> WHERE name = 'WRAPTEST' order by line;

TEXT
-----------------------------------------------------------

PROCEDURE wraptest IS
TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
all_emps emp_tab;
BEGIN
SELECT * BULK COLLECT INTO all_emps FROM emp;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).empno);
END LOOP;
END;

How can we avoid this?

My first thought is simply not to load the procedure into the database at all. Leave the procedure as an SQL file on the server, and execute it remotely. About a year ago, I engaged some Oracle programmers in such an approach, check that earlier article for a brief discussion on the advantages and disadvantages of keeping your code out of the database:
PL/SQL Code Storage: Files vs In-DB Packages

Anyone with access to the server will can still read your code, using an editor of some kind. But perhaps your server is capable of restricting access to your satisfaction.

You'll note that those Oracle programmers largely agreed that if your code is in the database, it should be wrapped and/or part of a package. First let's look at wrapping.

Wrapping, otherwise known as obfuscation, is a method of changing your human-readable PL/SQL code into text that is readable only by Oracle. In that sense, it can be compiled and executed like any other code, but it is protected in the sense that it won't make sense to anyone except Oracle.

You can learn how to use the wrap utility from either Dan Morgan, or Oracle's PL/SQL User's Guide and Reference (Appendix C), but I'll show you what I mean right now.

1. Put your PL/SQL code in an SQL file.
2. From the command line, issue the wrap command. The syntax is wrap iname=input_file [oname=output_file]

wrap iname=wraptest.sql oname=wraptest.plb

3. From SQL*Plus, execute the resulting PLB file to load the stored procedure, and even execute it for verification (omitted here).

SQL> @wraptest.plb

Procedure created.

4. Try to read the code, either by opening the PLB file, or querying a SOURCE table. PLB is a standard Oracle extension, sometimes referred to as "PL/SQL Binary." But it isn't binary, it's text: you can go ahead and read it.

SQL> SELECT text FROM USER_SOURCE WHERE name = 'WRAPTEST' order by line;

(garbage removed)

Now is wrapping a perfect solution? Well no. First of all, Oracle doesn't claim that it is undecipherable. Edited: In fact, leading Oracle security expert Pete Finnigan has made a presentation available that summarizes how to read wrapped code.

It also has a few annoying limitations, none of which I'll prove here, but they include the fact that you can't wrap triggers (just call a wrapped proc from your trigger), can't be copied to an earlier release, and all Oracle comments (--) are deleted.

I hear there may be a superior solution in Oracle 10, as part of the DBMS_DDL package. That solution doesn't even require a command-line step, it can be done directly in the database. If you're ahead of me, and using Oracle 10, give it a try using Dan Morgan's reference for the syntax.

Speaking of packages, the more important lesson to learn from those Oracle programmers was to put your code into packages. You can make the specification public, so everyone can understand them, and then restrict access (and wrap) the implementation details.

Using packages and wrapping your code is the standard method of protection. As a closing note, there are plenty of other compelling reasons to use Oracle packages too. I wrote about that once before, and also included some links where you can get the finer details on how to use them:
Oracle Packages - And why/how you should use them.

Comments:
Hi Robert,

You should take a look at my presentation from the BlackHat 2006 Las Vegas conference this year. See my post How to Unwrap PL/SQL BlackHat las vegas 2006 presentation slides are available which shows how to unwrap PL/SQL!

cheers

Pete
 
Thanks Pete! I loved your presentations, so I did you one better and edited my post to include your link at the appropriate place.

Your presentation begs the question: why would someone want to unwrap PL/SQL code anyway? Because you can verify it by testing it. Is theft the only reason to unwrap code?
 
Hi Robert,

Thanks for the update. I am not aware first hand of anyone using an unwrapper maliciously but there are plenty of uses for one. I know that unwrappers are used to find bugs in built in packages for instance by some security researchers and I also know that some security product companies are using unwrappers to allow their tools to detect CPU updates and to establish how to check for fixed vulnerabilities.

There are lots of uses for a PL/SQL unwrapper, malicious and not but the underlying reason in all cases is the same. This is to be able to read someone elses source code.

The message I am trying to get across in my paper is that the wrap mechanism is not secure, if you really want to protect source code then dont write directly in PL/SQL use a compiled language but these are not secure either as tools like strings can be used to extract SQL for instance.

cheers

pete
 
Why would people want to unwrap pl/sql?

In my case, it would be because I am trying to figure out why some calls through an incredibly poorly documented Oracle Warehouse Builder API are failing, and it would sure as hell be easier to debug what I need to change in my scripts or what it is that I have misconfigured if I could simply see what Oracle is trying to do with the values I am passing in!

In other words - not for malicious purposes but rather because beating my head against a wall using "Trial and Error" as the only available programming technique really sucks when I could step through the code and solve my problems simply if only it weren't wrapped.

Which explains how I found your journal after searching for an Oracle unwrap utility.....

Cheers,
Mike
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?