News
Abstract
SQL Procedure and Function obfuscation
Content
Obfuscation provides the capability of treating the SQL statements and logic within SQL procedures or functions as an intellectual asset.
Obfuscation is something that is chosen or used when a procedure or function is created. When obfuscation is used, the routine definition is scrambled wherever it is stored. Db2 for i knows how to unscramble it when needed and external users are prevented from seeing the routine definition details.
Software vendors and others can use this support to prevent their customers from seeing or changing SQL routines delivered as part of their solution.
To discover more information on this topic:
- Refer to the "Obfuscating an SQL routine" section within the SQL Programming book.
- Refer to the "CREATE_WRAPPED procedure" and the "WRAP function" sections within the DB2 for i SQL Reference.
Example 1 - Using WRAP() to build an obfuscated CREATE PROCEDURE statement
CREATE PROCEDURE UPDATE_STAFF ( IN P_EmpNo CHAR(6), IN P_NewJob CHAR(5), IN P_Dept INTEGER) LANGUAGE SQL TR : BEGIN UPDATE STAFF SET JOB = P_NewJob WHERE DEPT = P_Dept and ID = P_EmpNo; END TR
One way of shipping this procedure is to have code that runs at application install time that executes this SQL statement. However, after this SQL statement is executed the logic for the procedure is visible in the system catalogs.
SELECT ROUTINE_DEFINITION FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'UPDATE_STAFF';
To protect this proprietary logic, you can use use the SYSIBMADM.WRAP function to obfuscate the procedure definition.
values(sysibmadm.wrap('CREATE PROCEDURE UPDATE_STAFF ( IN P_EmpNo CHAR(6), IN P_NewJob CHAR(5), IN P_Dept INTEGER)
LANGUAGE SQL
TR : BEGIN UPDATE STAFF SET JOB = P_NewJob WHERE DEPT = P_Dept and ID = P_EmpNo;
END TR'))
CREATE PROCEDURE UPDATE_STAFF ( IN P_EMPNO CHAR ( 6 ) , IN P_NEWJOB CHAR ( 5 ) , IN P_DEPT INTEGER )
WRAPPED QSQ07010 aacxW8plW8FjG8prG8VbG8Fb68Fj68pnl9:p28VN5qpdW8pdW8pdX8pvaqebaqebaMzyFeUQ_XRUxb5OQY5enQQoE32oUfeBiyCeePraIiJZ4_t684g:TFNX3EuesCYX403tppXuqA:J2k4H8ZA0Y5jZLndrKS_g4SGf8uj6LWipyyd9vyT1kqHu:kwZcgvkQ7OKKI75ljLMsYzz:
Example 2 - Using ACS Generate SQL to create an obfuscated install script
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
All Versions
Operating system(s):
IBM i
Document number:
1167448
Modified date:
27 March 2025
UID
ibm11167448