Questions About AES Encryption In RPG, DB2 for i, And The Web

Hey, FHG:

We have never used encryption. Our new manager wants us to start using it on our IBM i 7.1 system. (For instance, we store passwords for our website members). I use the ENCRYPT_AES and DECRYPT_CHAR SQL functions in my RPG programs. However, with respect to AES encryption, our PHP web programmer is asking about how to generate an initialization vector and then save it later for decryption. Can you point us in the right direction? Finally, our manager doesn’t like the fact that the encryption key is in plain text in RPG source code. What are your thoughts?

–Anonymous

Dear Reader,

It’s good to hear you’re taking encryption seriously and using the convenient functions provided in DB2 for i to encrypt and decrypt data using AES. Further, while your PHP colleague is asking about initialization vector generation, you will see that this is just a terminology difference that I will explain in a bit.

But first, for those who may not know much about AES (Advanced Encryption Standard, a.k.a. “Rijndael”), let me refer you to the Wikipedia page on the topic.

To use AES encryption in DB2 for i, you don’t need advanced knowledge, simply some text or binary data to encrypt (a website password, social security number, credit card number, etc.) and a password to use for the encryption. It’s as easy as this:

ENCRYPT_AES(string to encrypt, encryption password)

The function returns binary data. In this example, user “Joe Cool’s” password (UserPassword) is encrypted using AES encryption with the encryption password value: EncryptionPassword

INSERT INTO WEB_USER (USER_ID, PASSWORD)
VALUES('Joe Cool',ENCRYPT_AES('UserPassword','EncryptionPassword'));

The gobbledygook returned by the ENCRYPT_AES function will be either VARBINARY, VARCHAR FOR BIT DATA, or BLOB; it just depends on the data type of the encrypted data. The length of the returned data depends on the length of the data being encrypted and the password length.

The data can be decrypted back to its original character form using the DECRYPT_CHAR function (so long as the encryption password is known). In this example the DECRYPT_CHAR function decrypts the encrypted website password using the same encryption password that was used to originally encrypt the data:

SELECT CASE WHEN DECRYPT_CHAR(PASSWORD,'EncryptionPassword')='UserPassword'
            THEN 'Y' ELSE 'N' END AS PASSWORD_MATCH
  FROM WEB_LOGIN
 WHERE USER_ID='Joe Cool';

Of course you would normally have variables instead of literals for user names and passwords. Also, I generally don’t decrypt encrypted data unless absolutely necessary. When writing code like this, rather than decrypt a stored password to compare it with what a user typed, I would encrypt what the user typed and compare it to the stored encrypted value.

The password is a vital part of the encryption process as it protects the data. If a password was not required, all a hacker would need to do to decrypt the data is to figure out what encryption algorithm encrypted the data. However, with the password used as a starting point in the encryption process, two different systems can encrypt the same data using the same algorithm (such as AES) but the use of different encryption passwords will yield different encrypted results.

Also, if you’re encrypting binary rather than character data, the DECRYPT_BINARY function would be used rather than DECRYPT_CHAR.

For the record, unless there is a compelling reason to store passwords, it is often recommended to store a hash of the password rather than an encrypted password. The reason for this is if the data is stolen, a hacker may decrypt the stolen passwords.

Now what about the initialization vector (IV) question? The IV is an analogous term for the DB2 encryption password. The IV is the seed that gets the process started when the data is encrypted and is also required for decryption.

Generating IVs is akin to generating encryption passwords. Whether or not you want to do this is up to you. If all of the encrypted data is contained in DB2 and every app has to access the decrypted data via a DB2 function, then one password can be used. However, sometimes you may want to use multiple passwords for encrypted data. For example, if you want to store encrypted data for many customers, you may want each customer to have its own uniquely generated encryption password. The problem with generating multiple IVs (i.e. multiple passwords) is that you have to maintain a cross reference between the IV and the data it was used to protect.

If you want to see an example of an RPG program that uses AES encryption with generated IVs, IBM published a pure RPG example that uses the qc3crtax API to perform AES encryption. Each encrypted customer is given its own initialization vector (see variable QC3IV in the sample code. The IV is generated with API Qc3GenPRNs). The sample code is found here.

As you can see from the example, the DB2 for i function is far simpler to use!

It is fine for your PHP colleague to handle all encryption through the use of an RPG program that uses the DB2 for i functions. However, if it is acceptable to access the encryption password in the PHP application, a developer can use a PHP library to perform AES encryption. The PHP encryption will be compatible with the DB2 for i encryption so long as the inputs to the ENCRYPT_AES function are supplied as UTF-8 (CCSID 1208) and the output is UTF-8. Otherwise the RPG program will be encrypting EBCDIC data using an EBCDIC password, which will be incompatible with PHP. Fortunately, DB2 can handle the EBCDIC/UTF8 conversion automatically by tagging the character parameters as CCSID 1208.

The benefit of letting the PHP developer code for the encryption locally will save the application from making calls to the database server and will also prevent unencrypted traffic from going across the network. The drawback is that the PHP developer (or the application itself) has to gain access to the password(s) for the encrypted data.

As for the plain text password within the source code issue, you should probably do a little more to protect yourself. In the past I’ve used a data area or table to store the passwords. While this may only require a hacker to go an extra step to get what he needs, the point is, it’s more difficult than just looking at source code. Hopefully the extra stumbling block of IBM i/DB2 object security will prevent him from getting the password.

If you opt to store the password in such a manner, you can store the password as a binary field with some sort of encoding like a simple exclusive-or (XOR) over all the bits just so it’s not as easy to get the password as reading the text or extracting a binary value from a database. Every annoyance you can throw in the way is a deterrent to someone who wants your data.

Finally, there are many nuances to using the DB2 encryption functions. Take time to read about the encryption functions and statements in the SQL Reference guide. Developers have the ability to specify password hints, to set a default password with the SET ENCRYPTION PASSWORD statement, etc.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.