IBMi (AS400) fans only * How to store passwords in a secure way in a DB2 SQL Server database

#IBMiSample

This is a wery simple way to store passwords in a secure way in a DB2 SQL Server database.

First of all create a table with only two fileds, user (UTENTE), and password (PWD).

CREATE TABLE A01AWPWD (UTENTE varchar(128), PWD VARCHAR(124) FOR BIT DATA)

Establish an encryption key. My encryption key for this example is skjdjuiu%Tr&$*+[be%_tr=\?

Fill the table with users and passwords:

INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('User01', ENCRYPT('Passw01',
'skjdjuiu%Tr&$*+[be%_tr=\?'))

        

INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('User02', ENCRYPT('Passw02',
'skjdjuiu%Tr&$*+[be%_tr=\?'))

INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('JohnS', ENCRYPT('ert%9ii',
'skjdjuiu%Tr&$*+[be%_tr=\?'))

Now we have a table A01AWPWD with ENCRYPTED passwords.

Try this:

SELECT PWD FROM A01AWPWD WHERE UTENTE = 'JohnS'

As you can see, the password is ENCRYPTED.

To read (DECRYPT) the passwords you need to know the encryption key:

SELECT DECRYPT_CHAR(PWD,'skjdjuiu%Tr&$*+[be%_tr=\?')

                  FROM A01AWPWD
                  WHERE UTENTE = 'JohnS'

Another way with SQL embedded:

      **free

      ************************************************

      * Encrypting/Decrypting data with SQL embedded *

      * Encrypt/decript example with                 *

      * ENCRYPT_AES                                  *

      * ENCRYPT_RC2                                  *

      * ENCRYPT_TDES                                 *

      *                                              *

      * DECRYPT_BIT                                  *

      ************************************************

       ctl-opt

       option(*nodebugio) dftactgrp(*no) actgrp(*new);

       dcl-s encryptionPassword VARCHAR(128);

       dcl-s p0                     CHAR(10);

       dcl-s p1                     CHAR(10);

       dcl-s p2                     CHAR(10);

       dcl-s p3                     CHAR(10);

       // The EXEC SQL is never executed. It is used at compile time.

       exec sql Set Option Commit = *none;

       // Create File

       exec sql

         create or replace table TABLE1

           (F0TEXT VARCHAR(010),

            F1_AES VARCHAR(128) FOR BIT DATA,

            F2_RC2 VARCHAR(128) FOR BIT DATA,

            F3TDES VARCHAR(128) FOR BIT DATA,

            PRIMARY KEY(F0TEXT))

            ON REPLACE DELETE ROWS ;

       encryptionPassword = 'kljhasdflkjhasdf';

       exec sql SET ENCRYPTION PASSWORD = :encryptionPassword ;

       // Insert a row

       EXEC SQL INSERT INTO TABLE1

         VALUES ('aaaa', ENCRYPT_AES( '111111111') ,

                         ENCRYPT_RC2( '222222222') ,

                         ENCRYPT_TDES('333333333')) ;

       // Insert another row

       EXEC SQL INSERT INTO TABLE1

         VALUES ('bbbb', ENCRYPT_AES( 'xxxxxxxxx') ,

                         ENCRYPT_RC2( 'yyyyyyyyy') ,

                         ENCRYPT_TDES('zzzzzzzzz')) ;

       exec sql close C1;

       exec sql open C1;

       // Read recordset without decription

       dow Sqlcode = 0;

         exec sql fetch C1 into :p0, :p1, :p2, :p3;

         if sqlcode = 0;

           dsply p1;

           dsply p2;

           dsply p3;

         endif;

       enddo;

       exec sql close C1;

       exec sql close C2;

       exec sql open C2;

       // Read recordset with decription

       dow Sqlcode = 0;

         exec sql fetch C2 into :p0, :p1, :p2, :p3;

         if sqlcode = 0;

           dsply p1;

           dsply p2;

           dsply p3;

         endif;

       enddo;

         exec sql close C2;

       *inlr = *on;

       // Read recordset without decription

       exec sql declare C1 cursor for

         SELECT F0TEXT,

                F1_AES,

                F2_RC2,

                F3TDES

         FROM TABLE1;

       exec sql declare C2 cursor for

         SELECT F0TEXT,

                DECRYPT_BIT(F1_AES),

                DECRYPT_BIT(F2_RC2),

                DECRYPT_BIT(F3TDES)

         FROM TABLE1; 

That's it

I appreciate all the comments made on this blog.