Encrypting data with SQL

December 14, 2023 Update:
256 byte enhanced version of AES encryption is available.

---

 

• Advanced Encryption Standard, AES
• RC2
• Triple DES, TDES

There are two ways we can do this, set an encryption password within a program and use that, or give the password with every insert or update. My preference is the first scenario.

Before I start encrypting data, I need a DDL table in which to put it all:

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

Lines 1 and 7: In this example table I am using the CREATE OR REPLACE TABLE, this allows me to re-run the statement multiple times and replace the existing table, without having to use the DROP TABLE as I would for earlier releases.

Line 2: I decided to have a column that would contain the unencrypted string. The only reason I added this was it could be used to compared to the result of the decrypting function.

Line 3: This will be the column that contains the AES encrypted data. Do notice the FOR BIT DATA, without that it is not possible to decrypt the data from the column.

Line 4: Column for the RC2 encrypted data.

Line 5: Column for the TDES encrypted data.

Line 6: I add a primary key to the table to ensure that there will never be duplicate keyed rows in this table.

There are three functions I will be using to encrypt the data:

• ENCRYPT_AES
• ENCRYPT_RC2
• ENCRYPT_TDES

These all have the same input parameters:

1. String or column to encrypt – mandatory
2. Password that is used to encrypt and decrypt the data – optional
3. Password hint – optional

In its simplest form I could do:

VALUES ENCRYPT_AES('Something','Password') ;

Something is the string I am going to encrypt. Password is the password for the encrypting. The result is:

4C54ACFF0111D5A2B96180506FFE4F23B96180506FFE4F23C8B3DF83F94263266153D7417324F38D

Rather than place the password in the function, which I think is unsecure, I like to use a SQL Set statement to set my password and hint:

SET ENCRYPTION PASSWORD = 'Stockholm' WITH HINT = 'Favorite European city' ;

If I was going to use this in a RPG program I would use variables in place of strings for the password and hint. These could be passed from another program, retrieved from a file, etc.:

SET ENCRYPTION PASSWORD = :Password WITH HINT = :Hint ;

If I had not set my password to insert values into the table I created I would need to:

01 INSERT INTO MYLIB.TABLE1 02 VALUES('Simon', 03 ENCRYPT_AES('Simon','Stockholm'), 04 ENCRYPT_RC2('Simon','Stockholm'), 05 ENCRYPT_TDES('Simon','Stockholm')) ;

But I used the Set statement, so my Insert looks like:

01 INSERT INTO U4142SH.TABLE1 02 VALUES('Simon', 03 ENCRYPT_AES('Simon'), 04 ENCRYPT_RC2('Simon'), 05 ENCRYPT_TDES('Simon')) ;

The encrypt functions, line 3-5, use the password and hint I set in the Set statement.

When I retrieve my results:

SELECT * FROM MYLIB.TABLE1 ;

I get:

UNENCRYPTED TYPE_AES TYPE_RC2 TYPE_TDES ----------- ------------------ ------------------ ------------------ Simon 4C8B75160111D5A... 0CE818160111D5A... 2CA15F160111D5A...

What happens if I have forgotten which password I use to encrypt with? Fortunately, there is a Get Hint function that will return to me the hint I used when I used the SET ENCRYPTION PASSWORD.

SELECT GETHINT(TYPE_AES) FROM MYLIB.TABLE1 ;

The hint is returned to me:

00001 ---------------------- Favorite European city

The encrypted data is worthless unless I can decrypt it. There are various functions to do this. The one I need to use is: DECRYPT_BIT

The function has three parameters:

1. Encrypted data or column – mandatory
2. Password used when encrypted – optional
3. CCSID for result – optional

I am only going to use the mandatory parameter. The password used is the one I set with the SET ENCRYPTION PASSWORD.

01 SELECT DECRYPT_BIT(TYPE_AES) AS "AES", 02 DECRYPT_BIT(TYPE_RC2) AS "RC2", 03 DECRYPT_BIT(TYPE_TDES) AS "TDES" 04 FROM MYLIB.TABLE1 ;

Which gives me the decrypted values for those columns:

AES RC2 TDES ----- ----- ----- Simon Simon Simon

If you get a SQL code of -171 then you did not define the columns with the FOR BIT DATA.

 

• SET ENCRYPTION PASSWORD SQL statement
• ENCRYPT_AES SQL function
• ENCRYPT_RC2 SQL function
• ENCRYPT_TDES SQL function
• GET HINT SQL function
• DECRYPT SQL functions

 

This article was written for IBM i 7.4, and should work for some earlier releases too.