Introducing PL/SQL Unwrapper for SQL Developer

2015-05-17

Tags

I’m using from time to time the free service Unwrap it! or Niels Teusink’s Python script unwrap.py to unwrap PL/SQL code. Recently I’ve been confronted more with wrapped code since a customer is about to migrate to a new banking platform which is using wrapped PL/SQL code extensively. While investigating migration errors we experienced that unwrapping the PL/SQL packages helped us a lot to identify the root cause faster. But since the unwrapping and debugging process is still a bit cumbersome for a series of PL/SQL packages a colleague asked me: “Wouldn’t it be nice if we could unwrap PL/SQL packages directly in SQL Developer?” and I answered “This should be simple. I’ve already written an extension for SQL Developer and the code in unwrap.py does not look too complicated.”

On a rainy weekend, I analyzed Niels Teusink’s public domain Phyton script unwrap.py and used it as a starting point for the development of a PL/SQL Unwrapper for SQL Developer.

Niels Teusink’s Python script unwrap.py (copy)

#!/usr/bin/python
#
# This script unwraps Oracle wrapped plb packages, does not support 9g
# Contact: niels at teusink net / blog.teusink.net
#
# License: Public domain
#
import re
import base64
import zlib
import sys

# simple substitution table
charmap = [0x3d, 0x65, 0x85, 0xb3, 0x18, 0xdb, 0xe2, 0x87, 0xf1, 0x52, 0xab, 0x63, 0x4b, 0xb5, 0xa0, 0x5f, 0x7d, 0x68, 0x7b, 0x9b, 0x24, 0xc2, 0x28, 0x67, 0x8a, 0xde, 0xa4, 0x26, 0x1e, 0x03, 0xeb, 0x17, 0x6f, 0x34, 0x3e, 0x7a, 0x3f, 0xd2, 0xa9, 0x6a, 0x0f, 0xe9, 0x35, 0x56, 0x1f, 0xb1, 0x4d, 0x10, 0x78, 0xd9, 0x75, 0xf6, 0xbc, 0x41, 0x04, 0x81, 0x61, 0x06, 0xf9, 0xad, 0xd6, 0xd5, 0x29, 0x7e, 0x86, 0x9e, 0x79, 0xe5, 0x05, 0xba, 0x84, 0xcc, 0x6e, 0x27, 0x8e, 0xb0, 0x5d, 0xa8, 0xf3, 0x9f, 0xd0, 0xa2, 0x71, 0xb8, 0x58, 0xdd, 0x2c, 0x38, 0x99, 0x4c, 0x48, 0x07, 0x55, 0xe4, 0x53, 0x8c, 0x46, 0xb6, 0x2d, 0xa5, 0xaf, 0x32, 0x22, 0x40, 0xdc, 0x50, 0xc3, 0xa1, 0x25, 0x8b, 0x9c, 0x16, 0x60, 0x5c, 0xcf, 0xfd, 0x0c, 0x98, 0x1c, 0xd4, 0x37, 0x6d, 0x3c, 0x3a, 0x30, 0xe8, 0x6c, 0x31, 0x47, 0xf5, 0x33, 0xda, 0x43, 0xc8, 0xe3, 0x5e, 0x19, 0x94, 0xec, 0xe6, 0xa3, 0x95, 0x14, 0xe0, 0x9d, 0x64, 0xfa, 0x59, 0x15, 0xc5, 0x2f, 0xca, 0xbb, 0x0b, 0xdf, 0xf2, 0x97, 0xbf, 0x0a, 0x76, 0xb4, 0x49, 0x44, 0x5a, 0x1d, 0xf0, 0x00, 0x96, 0x21, 0x80, 0x7f, 0x1a, 0x82, 0x39, 0x4f, 0xc1, 0xa7, 0xd7, 0x0d, 0xd1, 0xd8, 0xff, 0x13, 0x93, 0x70, 0xee, 0x5b, 0xef, 0xbe, 0x09, 0xb9, 0x77, 0x72, 0xe7, 0xb2, 0x54, 0xb7, 0x2a, 0xc7, 0x73, 0x90, 0x66, 0x20, 0x0e, 0x51, 0xed, 0xf8, 0x7c, 0x8f, 0x2e, 0xf4, 0x12, 0xc6, 0x2b, 0x83, 0xcd, 0xac, 0xcb, 0x3b, 0xc4, 0x4e, 0xc0, 0x69, 0x36, 0x62, 0x02, 0xae, 0x88, 0xfc, 0xaa, 0x42, 0x08, 0xa6, 0x45, 0x57, 0xd3, 0x9a, 0xbd, 0xe1, 0x23, 0x8d, 0x92, 0x4a, 0x11, 0x89, 0x74, 0x6b, 0x91, 0xfb, 0xfe, 0xc9, 0x01, 0xea, 0x1b, 0xf7, 0xce]

def decode_base64_package(base64str):
	base64dec = base64.decodestring(base64str)[20:] # we strip the first 20 chars (SHA1 hash, I don't bother checking it at the moment)
	decoded = ''
	for byte in range(0, len(base64dec)):
		decoded += chr(charmap[ord(base64dec[byte])])
	return zlib.decompress(decoded)
	

sys.stderr.write("=== Oracle 10g/11g PL/SQL unwrapper 0.2 - by Niels Teusink - blog.teusink.net ===\n\n" )
if len(sys.argv) < 2:
	sys.stderr.write("Usage: %s infile.plb [outfile]\n" % sys.argv[0])
	sys.exit(1)

infile = open(sys.argv[1])
outfile = None
if len(sys.argv) == 3:
	outfile = open(sys.argv[2], 'w')

lines = infile.readlines()
for i in range(0, len(lines)):
	# this is really naive parsing, but works on every package I've thrown at it
	matches = re.compile(r"^[0-9a-f]+ ([0-9a-f]+)$").match(lines[i])
	if matches:
		base64len = int(matches.groups()[0], 16)
		base64str = ''
		j = 0
		while len(base64str) < base64len:
			j+=1
			base64str += lines[i+j]
		base64str = base64str.replace("\n","")
		if outfile:
			outfile.write(decode_base64_package(base64str) + "\n")
		else:
			print decode_base64_package(base64str)

Even if this code looked straightforward on first sight, it took me a moment or two to understand it. In fact, I googled and found the following information helpful:

David Litchfield’s The Oracle Hacker’s Handbook: Hacking and Defending Oracle (chapter 5)
Pete Finnigan’s How to Unwrap PL/SQL. Pete provides a lot of useful resources on his blog and has shown in this post, that he owns unwrappers for 9i and 10g, which should be capable of handling every wrapped code.
Anton Scheffler’s blog post about Unwrapping 10g wrapped PL/SQL
Automatic Detection of Vulnerabilities in Wrapped Packages in Oracle by Yaron Gur-Arieh, Nikita Zubrilov and Ilya Kolchinsky
Marcel Lambrechts’ blog about Unwrapping Wrapped PL/SQL in Oracle 10gR2 – 12cR1

After flipping through all these pages I had some second thoughts about publishing an unwrapper, especially since David, Pete and Anton were a bit secretive about certain details such as the substitution table. Obviously, I decided to publish it nonetheless. Is this really harmful? There are already a couple of other 10g unwrappers available, such as:

In the end, this is just another PL/SQL Unwrapper. However, I believe it delivers some additional value if Oracle’s SQL Developer is the IDE of your choice. This is what it looks like on Windows:

The wrapped code will be replaced in the editor by the unwrapped code…

…you have to pay attention to not save the unwrapped code by accident.

Grab your copy of Trivadis PL/SQL Unwrapper from the download area. I hope it is useful.

46 Comments

Michal says:

2015-10-26 at 10:52

Thanks a lot, saves a lot of time!

Reply
Luis A says:

2015-11-05 at 16:47

Awesome. I don’t need to go other places to unwrap the packages. I have all that I need in my SQL Developer tool.
Thanks a lot for this post.

Reply
Ashly says:

2016-02-09 at 23:53

Hi Phillip – Thanks a ton for this unwrap extension to SQL Developer , saved me so much time and effort! Kudos to your efforts!

Thanks,

Ashly.

Reply
Oleg says:

2016-02-18 at 22:00

Phillip, I’ve downloaded .jar file. What I need to do further in order to get the final result: “Unwrap” submenu in “right click”-menu of SQLDeveloper’s editor. Sorry, I’m not a Java developer. Thanks in advance. Oleg

Reply
- Philipp Salvisberg says:
  
  2016-02-18 at 22:41
  
  Hello Oleg,
  
  You have to start SQL Developer and install the downloaded Unwrapper_for_SQLDev_0.1.1.zip file. Please follow the instructions under “Installation” at the bottom of https://www.salvis.com/blog/downloads/plsql-unwrapper-for-sql-developer/. See also the FAQ section, specifically https://www.salvis.com/blog/faq/i-cannot-install-any-extension-on-windows/ if you are using Windows.
  
  Best Regards,
  Philipp
  
  Reply
  - William Robertson says:
    
    2021-04-17 at 10:09
    
    There don’t seem to be any installation instructions on the linked page.
    
    Reply
    - Philipp Salvisberg says:
      
      2021-04-17 at 11:55
      
      In the download area is a link. See https://github.com/Trivadis/plsql-unwrapper-sqldev#configure-update-center
    - William Robertson says:
      
      2021-04-17 at 17:59
      
      Oh, got it. You have to add it to the “Check for Updates…” menu. Not obvious if you’re not that familiar with SQL Developer. Thanks.
    - Philipp Salvisberg says:
      
      2021-04-17 at 18:17
      
      Update center is the preferred way. The other option is to download the zip and use the Install From Local File option. It’s also mentioned there.
Nick says:

2016-05-23 at 12:45

Great tool, thank you.

Do you plan to develop Oracle 9i unwrapper?

Reply
- Philipp Salvisberg says:
  
  2016-05-23 at 13:02
  
  You’re welcome. No, I do not plan to provide a pre 10g unwrapper in the near future.
  
  Reply
PL/SQL Bulk Unwrap | Philipp Salvisberg's Blog says:

2016-06-26 at 23:44

[…] days ago I’ve released PL/SQL Unwrapper for SQL Developer version 0.1.1 and blogged about it. With this extension you can unwrap the content of a SQL Developer window. Time for an update. With […]

Reply
Alvaro Ramos says:

2016-07-18 at 19:26

thank you very much

Reply
Lucas says:

2016-07-19 at 16:57

Great tool! Thank you!

Reply
Banu says:

2016-07-19 at 16:59

HI, I have downloaded the jar file, but unable to configure/install the unwrap utility into sqldeveloper. I am unable to find the procedure to do that as you specified in Installation section. Can you please help me on this?

Regards,

Banu

Reply
- Philipp Salvisberg says:
  
  2016-07-19 at 17:07
  
  Hello Banu,
  
  You should get a zip file and not a jar after download. Please configure your browser to suppress unzipping files after download. Regarding the installation instructions, see section “Configure Update Center” in PL/SQL Unwrapper for SQL Developer.
  
  Regards Philipp
  
  Reply
  - Kalpataru Rath says:
    
    2017-03-26 at 15:16
    
    Hi Philipp,
    
    I am not able to unwrap a package in SQL Developer after adding your extension.
    
    I am using SQL Developer 4.1.1.
    
    When i am select the package body and clicking in the unwrap it is showing
    
    “Could unwrap this code . Most probably it was not wrapped with Oracle 10g , 11g and 12c wrap Utility.
    
    Reply
    - Philipp Salvisberg says:
      
      2017-03-26 at 20:30
      
      I assume that the error message is accurate. Unwrapping is not supported by this extension when the code was wrapped with an Oracle 9i wrap utility.
Banu says:

2016-07-19 at 17:05

Thank You. I could able to install using the last section for “Check for Updates”

Reply
Banu says:

2016-07-19 at 17:23

I have installed the packages and updated them from “Check for updates’ option. But how can i get options for “Check ” and “Beautify”

Reply
- Philipp Salvisberg says:
  
  2016-07-19 at 17:26
  
  For “Check” you have to install PL/SQL Cop for SQL Developer. “Beautify” is another SQL Developer plugin (a formatter) which is not yet released.
  
  Reply
Aashish Suneja says:

2016-07-25 at 15:02

Thank you so Much for your Efforts. It really helped.

Just one question: How did you take that snapshot of screen? I mean Cutting Edges on the Bottom (0r any side of the image) ;-) What tool/utility you used? Please tell me the way. I googled it but could not able to find out.

Reply
- Philipp Salvisberg says:
  
  2016-07-25 at 15:06
  
  You’re welcome. I’m using Snagit from TechSmith.
  
  Reply
  - Aashish Suneja says:
    
    2016-07-25 at 15:27
    
    Thank You Philipp. Appreciate your efforts :)
    
    Reply
Chad says:

2016-10-05 at 23:22

Hi Philipp,

I tried to install your extension into my Oracle SQL Developer v. 3.2.20.10 by referencing the URL http://update.salvis.com/ as noted in your instructions. The extension downloaded, but the extension functionality doesn’t show up.

I noticed an error displayed in the Extensions Log that states:

Error: ESDK version 2.0 is too high. Maximum supported version is 1.0.
Error: Exception processing manifest: java.lang.NullPointerException:null

Double-clicking on the first error displays a file named “extension.xml” which contains:
```
<extension id="com.trivadis.unwrapper.sqldev" version="1.0.0.1878"
    esdk-version="2.0" rsbundle-class="com.trivadis.unwrapper.sqldev.resources"
    xmlns="http://jcp.org/jsr/198/extension-manifest">
```
I’m not sure what to try next in order to resolve this issue. I would appreciate any suggestions you might have.

Thanks!

Reply
- Philipp Salvisberg says:
  
  2016-10-06 at 07:31
  
  Hi Chad,
  
  SQL Developer 1.x, 2.x, 3.x are based on the Extension Software Development Kit (ESDK) version 1. SQL Developer 4.x requires the use of ESDK version 2. Unfortunately these versions are not compatible. So I decided to support the newest SQL Developer version only. I suggest to update to the latest SQL Developer version (currently 4.1.5) since this is a prerequisite for this extension.
  
  Thank you.
  Philipp
  
  Reply
  - Chad says:
    
    2016-10-10 at 18:33
    
    Thank you for your assistance, Philipp!
    
    Reply
Naresh Kumawat says:

2016-12-04 at 07:54

How can i come to know about logic behind of unwrap

Reply
- Philipp Salvisberg says:
  
  2016-12-04 at 09:13
  
  Hi Naresh,
  
  Look at the Nils Teusink’s Python script unwrap.py and the links mentioned above. The work from David Litchfield, Pete Finnigan and Anton Scheffler are very helpful.
  
  HTH
  Philipp
  
  Reply
Eugene says:

2016-12-28 at 12:41

Hi Philipp,
I,ve installed the Unwrapper from local file Unwrapper_for_SQLDev_1.0.0.zip onto SQL Developer 4.1.5.21 Build MAIN-21.78,
but don’t see any effect after restarting neither messages in extensions log.
Thank you in advance for help.

Reply
- Philipp Salvisberg says:
  
  2016-12-28 at 12:50
  
  Hi Eugene,
  The extension should work on 4.1.5. In fact I’m successful using it on Windows and MacOS. Which OS are you using? How have you installed the extension? Have you followed the instructions at the bottom of Configure Update Center? If you are on Windows, please have a look at the FAQ I cannot install any extension on Windows.
  HTH
  Philipp
  
  Reply
  - Eugene says:
    
    2016-12-28 at 14:32
    
    Hi Philipp,
    Windows Server 2012 R2. Check For Updates -> Install From Local File. Then New Installs/Other/Trivadis PL/SQL Unwrapper for SQL Developer, Finish, Restart and… nothing.
    SQL Developer seats on C:\sqldeveloper, I’m administrator myself and tried to “Run As Administrator”.
    Thanks,
    Eugene.
    
    Reply
    - Philipp Salvisberg says:
      
      2016-12-28 at 15:38
      
      Hi Eugene,
      
      C:\sqldeveloper is ok, no need to run as administrator. Installation procedure looks good as well. Do you see the extension in the About or features dialog as shown below?
      
      If not, do you see some related logging output under “Views->Log” after the installation/restart?
      
      In “C:\sqldeveloper\configuration” you should find the files cfu_bundles.info and cfu_bundles.extras. These are CSV files, containing a line for each non-standard extension. They do not exist on a fresh SQLDev installation. Here’s the content required for version 1.0.0:
      
      com.trivadis.unwrapper.sqldev,1.0.0,../sqldeveloper/extensions/com.trivadis.unwrapper.sqldev.jar,4,false
      
      com.trivadis.unwrapper.sqldev,EXTENSION,,1.0.0.1878,Trivadis PL/SQL Unwrapper for SQL Developer,,Other,Trivadis PL/SQL Unwrapper for SQL Developer is an extension to unwrap an editor content.,Trivadis AG,http://www.trivadis.com,Trivadis PL/SQL Unwrapper for SQL Developer
      
      In “C:\sqldeveloper\sqldeveloper\extensions” you should find the file “com.trivadis.unwrapper.sqldev.jar”. It should have been extracted from the ZIP file during installation. That’s all what is required for a successful installation.
      
      However, there are cases when SQL Developer’s System cache is corrupt. You may either delete your settings completely (folder C:\Users\YOURNAME\AppData\Roaming\SQL Developer\system4.1.5.21.78) or the cache (folder C:\Users\YOURNAME\AppData\Roaming\SQL Developer\system4.1.5.21.78\system_cache). Please export connections and other settings before you do so.
      
      Cheers,
      Philipp
    - Eugene says:
      
      2017-01-09 at 16:15
      
      Hi Philipp, thanks a lot.
      
      I don’t see Unwrapper on both screens above :(
      Checked configuration, cfu_bundles.info was the same as cfu_bundles.extras, fixed it.
      Jar file on its place.
      Deleted whole \AppData\Roaming\SQL Developer\system4.1.5.21.78, restarted, reinstalled, restarted again. Nothing.
      One suggestion: my Oracle box isn’t connected to the net, even have no net card. Can it affect?
      
      Best regards,
      Eugene.
    - Philipp Salvisberg says:
      
      2017-01-09 at 16:28
      
      Hi Eugene, strange. I’ve contacted you via E-Mail. Cheers, Philipp
Rupesh Kumar sah says:

2017-06-25 at 20:15

this tool gives me an error message that ‘NOT A VALID CODE OR INCOMPLETE’,but it runs on my system.

please anyone unwrapp this procedure and send it my mail(sahrupesh12@gmail.com)….
please…………………..

CREATE OR REPLACE PROCEDURE FTP_UPLOADFILE wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
8003000
1
4
0
30
e FTP_UPLOADFILE:
(removed trailing code)

Reply
Philipp Salvisberg says:

2017-06-25 at 23:30

The full error message is with version 1.0.0 is:

Could not unwrap this code. Most probably it was not wrapped with the Oracle 10g, 11g or 12c wrap utility.

The error message is correct. This coded has been wrapped with a pre Oracle 10g wrap utility. See also FAQ Is unwrapping code processed by the PL/SQL Wrapper of Oracle7, Oracle8, Oracle8i or Oracle9i supported?

Reply
Eduardo Candido says:

2018-08-23 at 21:15

Perfect, congrats!

Perfeito, parabéns!

Reply
Nandhini says:

2020-07-28 at 09:37

Hi Philipp,

I am using SQL Developer in my Linux Virtual Box and I am not able to see the option Unwrap on right clicking, Also I couldn’t use ur zip file as its for windows
Could you please help me in this regard !

Thanks,
Nandhini.

Reply
- Philipp Salvisberg says:
  
  2020-07-28 at 10:28
  
  The ZIP file is platform independent.
  
  I suggest to install the extension via the update center. See https://github.com/Trivadis/plsql-unwrapper-sqldev#configure-update-center . If it does not work, please have a look at the the FAQs. If the FAQs do not help then please open a GitHub issue and provide the version of SQL Developer, the OS (of the VM) and error messages you’ve got during the installation process. Thanks.
  
  Reply
Rajadeepan says:

2021-09-08 at 14:28

Hi,

I am using SqlDev Version 19.2.1.247 now. I have tried and installed the Unwrapper utility under Check for Updates using Install from Local file option
(Path: C:\Users\Unwrapper_for_SQLDev_1.0.0.zip). Unfortunately, there is no unwrap option listed while right click on SQL window after restarted.
Please help.

Thanks

Reply
- Philipp Salvisberg says:
  
  2021-09-08 at 15:20
  
  A possible reason for the issue you are describing is that you have installed SQL Developer in a protected folder, e.g. under C:\Program Files. Installations have to be done as Administrator. See also https://github.com/Trivadis/plsql-unwrapper-sqldev/blob/main/FAQ.md#i-cannot-install-any-extension-on-windows for more information.
  
  If this does not solve your issue then please open a GitHub issue https://github.com/trivadis/plsql-unwrapper-sqldev/issues/new with further details such as path where SQLDev is installed and steps to reproduce the issue.
  
  Thank you.
  
  Reply
Adrian Boangiu says:

2022-01-27 at 13:53

Hello Philipp,

Very good and useful tool.
I read your initial post and the articles you have mentioned. I am searching for a feature that is not covered by any of the tools I have seen. I want to be able to unwrap the code in the database and not in a file. Would it be possible to have some pl/sql code that allows to wrap/unwrap the code in the internal structures of Oracle?

Thank you for your thoughts

Reply
- Philipp Salvisberg says:
  
  2022-01-29 at 13:13
  
  You can use this SQL Developer extension to read the wrapped code from the database and unwrap it. It’s even possible to do that for a complete schema as shown in this blog post.
  
  If you are looking for an Unwrapper that runs in the Oracle Database, then have a look at plsqlunwrapper. I’ve mentioned in the blog post.
  
  Reply
Kelly says:

2023-04-25 at 20:52

Thanks for your tool, Phil!
What do you suggest for this error? I was given a Package Spec & Body together in one file. Even if I took the spec off & tried unwrapping the body by itself, same error (slightly different character counts). Did the source file get corrupted somehow during the extraction?

Unwrap failed
Wrapped code seems to be truncated. Expected length of 4857 characters but got only 4028.

SQL Developer 23.1.0.097 (64-bit)
Unwrapper For SQL Developer 1.0.0
Windows 10

Reply
Kelly says:

2023-04-25 at 21:03

indeed, it appears to be a corrupted file. Other Database object unwrapped ok using your tool, but for this package, even pasting it online at https://www.codecrete.net/UnwrapIt/ declared:

The PL/SQL code cannot be unwrapped. The data seems to be corrupted.

So, there’s nothing you can do… unless! You build an uncorrupter! jus’ kidding. Your tool is great as is :)
⭐⭐⭐⭐⭐

Reply

46 Comments

Leave a Reply Cancel reply