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 🙂
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
DECLARE PASSWD VARCHAR (127) ;
SET PASSWD = MWLIBR.GETPASS () ;
SET N.ACCOUNT = ENCRYPT_RC2 (N.ACCOUNT , PASSWD) ;
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 ()
READS SQL DATA
CALLED ON NULL INPUT
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.
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.
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.
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
Using IBM i FIELDPROC encrypt sensitive data
What is IBM i Email and SPF?
Updating Numeric DTAARA in RPGLE
How to capture IBM-i job info for submitted jobs
Register license key in SOFTLANDING SOFTMENU
Going the (Levenshtein) Distance in RPG Free
Don’t hardcode library names in your TURNOVER SQL source #youbigsilly
How to Install IBM Access Client Solutions (ACS)