Enhanced encryption routine added to SQL

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:

DSPLY THIRD,THIRD,THIRD

Showing that the encrypted columns were decrypted successfully.

I will be converting the programs I have used the ENCRYPT_AES within to use the new more secure ENCRYPT_AES256 function.

 

You can learn more about the changes to SQL's ENCRYPT_AES256 scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.