Enhanced encryption routine added to SQL @ RPGPGM.COM

Included within the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, is an improved encryption algorithm for AES, which uses a 256 bit key. It is as easy to use as the older versions of encryption I wrote about over a year ago. I just wanted to take this opportunity to reinforce the best ways of using it.

It is a scalar function that does the encryption. Its syntax is just:

ENCRYPT_AES256(< string or variable >, < password string >, < hint >)

In its simplest for I can just use it like:

01  VALUES BINARY(ENCRYPT_AES256('This is RPGPGM.COM','Password'))

'This is RPGPGM.COM' is what I am going to encrypt.

'Password' is the password string to use.

I am not going to pass a password hint in this example.

Notice how ENCRYPT_AES256 is contained within a BINARY scalar function. If I just use the ENCRYPT_AES256 on its own the results contain all kinds of special characters, which are not easily supported by HTML. If I translate the result to binary then I can show the binary string returned.

The result is:

00001
---------------------------------
CCF40CFF0025D5B9B96180506FFE4F...

The three dots indicate that the result is larger than what I have decided to show.

If I am going to give good examples then I need somewhere to store my results, let's say in a table. The SQL DDL table is called TABLE1 and it will be created in my library, MYLIB.

01  CREATE OR REPLACE TABLE MYLIB.TABLE1   
02    (UNENCRYPTED VARCHAR(10),              
03     TYPE_AES256 VARCHAR(128) FOR BIT DATA,
04     TYPE_AES VARCHAR(128) FOR BIT DATA,   
05     PRIMARY KEY(UNENCRYPTED))             
06  ON REPLACE DELETE ROWS

Line 2: This column will contain the unencrypted value.

Line 3: This column will contain the encrypted value generated the new AES256 algorithm. Note that the column must be a variable character of a maximum of 128 characters. And it must have the FOR BIT DATA.

Line 4: This column will contain the old version of AES.

Let me insert one row into the file:

01  INSERT INTO TABLE1 VALUES(
02     'FIRST',
03     ENCRYPT_AES256('FIRST','Stockholm','Favorite European city'),
04     ENCRYPT_AES('FIRST','Stockholm','Favorite European city'))

Line 2: Is the value that is added to the unencrypted column.

Line 3: Here I am encrypting the value 'FIRST' using the AES256 algorithm, with the password of 'Stockholm'. I have given the password hint too, 'Favorite European city''.

Line 4: Here I am encrypting using the old AES algorithm, with the same password and hint.

If you want to see how to use the password hint I recommend you read my previous post about encryption.

Having inserted the row I can view it:

01  SELECT UNENCRYPTED,
02         BINARY(TYPE_AES256),
03         BINARY(TYPE_AES)
04    FROM TABLE1

Again I am using the BINARY scalar function to convert the bit data returned from the two encrypted columns.

UNENCRYPTED  AES256           AES
-----------  ---------------  ---------------
FIRST        CCD62A160025...  4CCD33160025...

IMHO the only way to test if the encryption was successful is to have a successful decryption. For that I use the DECRYPT_BIT scalar function, see below:

01  SELECT UNENCRYPTED,
02         DECRYPT_BIT(TYPE_AES256,'Stockholm') AS "AES256",
03         DECRYPT_BIT(TYPE_AES,'Stockholm') AS "AES"
04    FROM TABLE1

Lines 2 and 3: I need the password to unencrypt.

The results of the above statement is:

UNENCRYPTED  AES256   AES
-----------  -------  -----
FIRST        FIRST    FIRST

Rather than have to give the password, and if I want the hint, I use the SET ENCRPYTION PASSWORD statement:

01  SET ENCRYPTION PASSWORD = 'Stockholm' 
02        WITH HINT = 'Favorite European city'

Now when I use the encryption scalar functions I don't have to give the password, they use the one I set above:

01  INSERT INTO TABLE1 VALUES(
02     'SECOND',ENCRYPT_AES256('SECOND'),ENCRYPT_AES('SECOND'))

And the same is true of the when I decrypt:

01  SELECT UNENCRYPTED,
02         DECRYPT_BIT(TYPE_AES256) AS "AES256",
03         DECRYPT_BIT(TYPE_AES) AS "AES"
04    FROM TABLE1

The results for the above statement are:

UNENCRYPTED  AES256   AES
-----------  -------  ------
FIRST        FIRST    FIRST
SECOND       SECOND   SECOND

Performing all of the above in ACS's Run SQL Scripts does not give a real world example. I am going to show you how to use this in a RPG program:

01  **free
02  dcl-s Password char(9) inz('Stockholm') ;
03  dcl-s Hint char(22) inz('Favorite European city') ;

04  dcl-s Unencrypted varchar(10) ;
05  dcl-s AES256 varchar(10) ;
06  dcl-s AES varchar(10) ;

07  exec sql SET ENCRYPTION PASSWORD = :Password
                   WITH HINT = :Hint ;

08  exec sql INSERT INTO TABLE1 VALUES(
                'THIRD',
                ENCRYPT_AES256('THIRD'),
                ENCRYPT_AES('THIRD')) ;

09  exec sql SELECT UNENCRYPTED,
                    DECRYPT_BIT(TYPE_AES256),
                    DECRYPT_BIT(TYPE_AES)
                    INTO :Unencrypted,:AES256,:AES
               FROM TABLE1
              WHERE UNENCRYPTED = 'THIRD' ;
                                                
10  dsply (Unencrypted + ',' + AES256 + ',' + AES) ;

Line 1: This code is modern RPG.

Lines 2 and 3: I have defined this two variables to contain the password and password hint. If this was a production environment I could pass these to the program to ensure that all programs use the save encryption password.

Lines 4 – 6: I will be using these variables to contain the decrypted results.

Line 7: I am setting the encryption password and hint.

Line 8: I am inserting a record into the Table, and encrypting the two columns as I did before.

Line 9: I am using a Select statement to retrieve the row I inserted in line 8. What follows the INTO are the RPG variables that the values from the columns listed in the select columns section are moved to.

Line 10: I use a Display operation code, DSPLY, to display the contents of those RPG variables.

After compiling when I call the program the following is displayed: