Tuesday, September 24, 2013

Everything is my IP: Obfuscating a "Good Morning"

Want to impress or scare your co-workers? Or show your boss how to hide Intellectual Property (IP)? Try this with DB2:

db2 => create view hello  WRAPPED SQL10050 ablGWmdiWmtmTmdKTmJqTmdKUmteUmduUnZq1mZK2idaWmdaWmdaWndjHBcaGicaGQIwPJVuycUYnzvpYqeM0J9mNKgGspriVQtaGRWxKEiYWqvXK6WNaJSUbhrqa
DB20000I  The SQL command completed successfully.
db2 => select * from hello

Good Morning

  1 record(s) selected.

Even though the functionaliy for it was introduced in DB2 9.7 FP2, probably not many have looked at obfuscation of SQL code, i.e., the body of routines, views, triggers, and packages or modules. Basically, if some business or application logic is inside the database, obfuscation might be an option. This could be either because you want to protect your IP or for security reasons. At the time code obfuscation was released for DB2 in 2010 I wrote that it would be possible to abandon spaghetti code as a way of protection.

It is pretty simple to use obfuscation as everything needed is in the built-in module DBMS_DDL. It has a function WRAP and a procedure CREATE_WRAPPED. When WRAP is called with a valid statement as parameter, it returns a string with the DDL statement to create a wrapped, i.e. obfuscated, version of it. The procedure CREATE_WRAPPED is called with a valid DDL statement, transforms it to the wrapped version and directly executes it. WRAP could be called to produce encoded statements to be embedded in text files. The files would be given to customers. CREATE_WRAPPED could be called directly from an application which creates objects inside a database. Customers would have a hard(er) time to reverse engineer the application logic of functions, procedures, or entire packages created in the database.

So instead of creating the view "hello" as shown above, my application could have issued the following statement:
call dbms_ddl.create_wrapped('create view hello (greet) as select ''Good Morning'' from dual')

And now you know the logic that was hidden inside the strange string of my first SQL statement above ("ablGWmdi..."). Try to memorize that pattern and impress your co-workers or boss...

BTW: If you are a hardcore DB2 user and have never used the DUAL table, read here.