Obfuscating an SQL routine or SQL trigger

SQL functions, procedures, and triggers can be obfuscated so that their routine body logic and statements are not visible on a system.

Once testing of an SQL procedure, function, or trigger is complete, you can obfuscate the text so it is not readable to anyone on the system. To do this, use the WRAP SQL function or the CREATE_WRAPPED SQL procedure. Both are system provided routines in schema SYSIBMADM.

The WRAP scalar function returns an obfuscated form of the SQL statement that was provided as an input argument. The CREATE_WRAPPED procedure will obfuscate the SQL statement and then execute it.

When an obfuscated routine is created, the routine body will not be visible in the catalog tables or in the program object. No listing will be generated and any temporary files used during creation of the routine will be deleted. The routine will always be created as not debuggable so no debug view containing the routine content will exist.

The Generate SQL feature of System i® Navigator has an option to make it easy to obfuscate many SQL routines at one time.

Once a routine is obfuscated, the program or service program object for the SQL routine can be saved and restored to other systems. It can be used as an object for the Generate SQL feature of System i Navigator. Db2 for i will understand how to process the statement correctly and it will remain obfuscated.

For example, suppose your procedure is:

CREATE PROCEDURE UPDATE_STAFF (
          IN P_EmpNo CHAR(6),
          IN P_NewJob CHAR(5),
          IN P_Dept INTEGER)
BEGIN
  UPDATE STAFF
      SET JOB = P_NewJob
      WHERE DEPT = P_Dept and ID = P_EmpNo;
END;

After testing the procedure to make sure it works, you can generate an obfuscated form of the statement by using the WRAP function.

VALUES(SYSIBMADM.WRAP(
'CREATE PROCEDURE UPDATE_STAFF (
          IN P_EmpNo CHAR(6),
          IN P_NewJob CHAR(5),
          IN P_Dept INTEGER)
BEGIN
  UPDATE STAFF
      SET JOB = P_NewJob
      WHERE DEPT = P_Dept and ID = P_EmpNo;
END'
));

The result from this statement is a CLOB value that contains a value that looks something like the following statement. Since the timestamp is used during the obfuscation process, your result can be different every time. The value is shown here on several lines for convenience. New line characters are not allowed in the wrapped part of the statement text.

CREATE PROCEDURE UPDATE_STAFF ( IN P_EMPNO CHAR ( 6 ) , IN P_NEWJOB CHAR ( 5 ) 
, IN P_DEPT INTEGER )  WRAPPED QSQ07010 aacxW8plW8FjG8pnG8VzG8FD68Fj68:Hl8:dY_p
B2qpdW8pdW8pdW_praqebaqebaGEMj_vsPBs5bOJUUqnHVayEl_ogAlGWqz2jJCIE1dQEjt33hd5Sps
5cYGViD1urv7vGKeOcC4CwpCibbmeMfsW3XzXWnlplyX9wunOCqqFiDqaBl

This is an executable SQL statement. It can be run just like the original SQL statement. Altering any of the characters that follow the WRAPPED keyword will cause the statement to fail.

To deploy this statement, the obfuscated form can be embedded in a RUNSQLSTM source member or source stream file. You need to be very careful to include exactly the characters in the obfuscated version.

A second way of obfuscating an SQL routine is to use the CREATE_WRAPPED SQL procedure:

CALL SYSIBMADM.CREATE_WRAPPED(
'CREATE PROCEDURE UPDATE_STAFF (
          IN P_EmpNo CHAR(6),
          IN P_NewJob CHAR(5),
          IN P_Dept INTEGER)
BEGIN
  UPDATE STAFF
      SET JOB = P_NewJob
      WHERE DEPT = P_Dept and ID = P_EmpNo;
END'
);

This will create the procedure and the entire SQL routine body will be obfuscated. Looking at the ROUTINE_DEFINITION column in SYSROUTINES will show the obfuscated form of the routine body, starting with the WRAPPED keyword. You must save the original routine source if you might need it for future reference since there is no way to generate the original source from the obfuscated text.