Encrypt IBM i File (Table) Data with no RPGLE changes using SQL

IBM i

Mar 12

I found this wonderful article over at Midrange Blog

It does exactly what I am trying to do: Lets me encrypt a certain field (column) in a table so that its encrypted as its added and updated. It also lets me automatically decrypt it when I read the record back (the only caveat is that this will be automatic with SQL but will need a small procedure to do it manually with native IO – CHAIN, READ, etc)

So, I’m republishing it here for reference. Not changing a word 🙂

Encrypt Data as its read, updated, added

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”.

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 simpleSELECT 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 myfileThe 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.

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.