Easy Field Encyption for System i

For our new billing system (AR), we wanted to store account numbers for so that our customers could auto-pay their bills. After some searching and seeing how complicated the APIs are to use, I found these SQL functions: ENCRYPT_RC2(data, password) and DECRYPT_CHAR(data, password) . These looked promising and being that this is an internal-only system, we thought that this should be okay.

Now the challenge, we don’t want to have to use SQL INSERT and UPDATE statements every time we create or update a new record. RPG (Report Program Generator) has built-in commands to do that: WRITE and UPDATE. I found this article that talks about some work-around options. One of which is a trigger. This makes the most sense for our scenario.

By creating the trigger as a “before insert” or “before update” it will take the text passed and encrypt it before actually writing it to the table. Here is a sample:

CREATE TRIGGER MWLIBR.TEST_INSERT

    BEFORE INSERT ON MWLIBR.TESTP

    REFERENCING NEW AS N

    FOR EACH ROW

    MODE DB2ROW

    BEGIN

    DECLARE PASSWD VARCHAR (127) ;

    SET PASSWD = MWLIBR.GETPASS () ;

    SET N.ACCOUNT = ENCRYPT_RC2 (N.ACCOUNT , PASSWD) ;

END;

Notice the GETPASS()? That is the secret to the magic. That is a user created function. I’ll get into that in a minute. What this function does is take the ACCOUNT field and encrypt that. You will need to also create one for UPDATE as well. This will take care of your writes. Now you won’t have to change anything in your RPG program to update or write to this file.

The function is relatively simple. We simply need to return a “password”. I would recommend using a site like goodpassword.com to generate a random password. I used a 80 long string with special characters. Good luck dictionary attacking that hackers! Here is the function:

CREATE FUNCTION MWLIBR.GETPASS ()

           RETURNS CHAR(127)

           LANGUAGE SQL

           SPECIFIC MWLIBR.GETPASS

           NOT DETERMINISTIC

           READS SQL DATA

           CALLED ON NULL INPUT

           DISALLOW PARALLEL

           BEGIN

                      RETURN 'mypassword';

           END;

Now the problem comes in, to keep our model simple, we would like this to be as easy to use as READ to get this data, but we can’t just allow anyone to see that information. So we have to add a layer of difficulty in using it. There are a few options, and I’ll leave it to you to figure out which will work for you.

1. Use SQL to access that data. Just a simple

   SELECT DECRYPT_CHAR(myfield, GETPASS()) FROM myfile WHERE mykey = key;

   will get you the account number.

2. Use an SQL view.

   CREATE VIEW lib/view from SELECT KEY1, KEY2, DECRYPT_CHAR(myfield, GETPASS()) FROM myfile

   The problem with this method is you might as well just not secure it at all unless you lock down this view tight.

3. The final option would be a subprocedure. There can be two approaches to this.
   1. Write a seperate subprocedure for each field you have encrypted.
   2. Write one subprocedure that could work for everything. For instance:

      getEncryptedField('MYLIB' : 'MYFILE' : 'MYFIELD' : 'WHERE STATEMENT FOR THE KEYS')

      This would look like:

      getEncryptedField('MWLIBR' : 'TESTP' : 'ACCOUNT' : 'TESTKEY = 123 AND KEY2 = 567')

So there you have it. It isn’t only complicated and should work for most people. Any problems or comments? Just leave a comment on the thread and if I have any errors, I will immediately correct them.