db2-hash-routines(8)    DB2 UDFs and Stored Procedures    db2-hash-routines(8)




NAME

       db2-hash-routines - DB2 UDFs and SPs to generate and validate hashes


SYNOPSIS

       User Defined Functions:

       >>-BCRYPT--(--expression--)------------------------------------><

       >>-PHP_MD5--(--expression--)-----------------------------------><

       >>-APR_MD5--(--expression--)-----------------------------------><

       >>-APR_CRYPT--(--expression--)---------------------------------><

       >>-APR_SHA1--(--expression--)----------------------------------><

       >>-APR_SHA256--(--expression--)--------------------------------><

       >>-SHA256_HEX--(--expression--)--------------------------------><

       >>-SHA1_HEX--(--expression--)----------------------------------><

       >>-SHA256--(--expression--+---------+--)-----------------------><
                                 '-,--salt-'

       >>-SHA512--(--expression--+---------+--)-----------------------><
                                 '-,--salt-'

       >>-VALIDATE_PW--(--password--,--hash--)------------------------><

       Stored Procedures:

       >>-BCRYPT--(--expression--,--hash--)---------------------------><

       >>-PHP_MD5--(--expression--,--hash--)--------------------------><

       >>-APR_MD5--(--expression--,--hash--)--------------------------><

       >>-APR_CRYPT--(--expression--,--hash--)------------------------><

       >>-APR_SHA1--(--expression--,--hash--)-------------------------><

       >>-APR_SHA256--(--expression--,--hash--)-----------------------><

       >>-SHA256_HEX--(--expression--,--hash--)-----------------------><

       >>-SHA1_HEX--(--expression--,--hash--)-------------------------><

       >>-SHA256--(--expression--+---------+--,--hash--)--------------><
                                 '-,--salt-'

       >>-SHA512--(--expression--+---------+--,--hash--)--------------><
                                 '-,--salt-'

       >>-VALIDATE_PW--(--password--,--hash--,--is_valid--)-----------><


DESCRIPTION

       These functions and procedures can be used to generate hashes in DB2.

       bcrypt( 'cleartext' )
       bcrypt( 'cleartext', :hash )
              bcrypt  algorithm. The bcrypt routine is compatible to the func-
              tion used in Apache's htpasswd utility.

              The argument can be a character string that is either a CHAR  or
              VARCHAR not exceeding 4096 bytes.

              The  result  of the routine is CHAR(60). The result can be null;
              if the argument is null, the result is the null value.

       php_md5( 'cleartext' )
       php_md5( 'cleartext', :hash )
              MD5 hash. The php_md5 routine is compatible to the PHP md5 func-
              tion.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.

              The result of the routine is CHAR(32). The result can  be  null;
              if the argument is null, the result is the null value.

       apr_md5( 'cleartext' )
       apr_md5( 'cleartext', :hash )
              Seeded  MD5 hash. The apr_md5 routine is compatible to the func-
              tion used in Apache's htpasswd utility.

              The argument can be a character string that is either a CHAR  or
              VARCHAR not exceeding 4096 bytes.

              The  result  of the routine is CHAR(37). The result can be null;
              if the argument is null, the result is the null value.

       apr_crypt( 'cleartext' )
       apr_crypt( 'cleartext', :hash )
              Unix crypt. The apr_crypt routine is compatible to the  function
              used in Apache's htpasswd utility.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.

              The result of the routine is CHAR(13). The result can  be  null;
              if the argument is null, the result is the null value.

       apr_sha1( 'cleartext' )
       apr_sha1( 'cleartext', :hash )
              SHA1  algorithm. The apr_sha1 routine is compatible to the func-
              tion used in Apache's htpasswd utility.

              The argument can be a character string that is either a CHAR  or
              VARCHAR not exceeding 4096 bytes.

              The  result  of the routine is CHAR(33). The result can be null;
              if the argument is null, the result is the null value.

       apr_sha256( 'cleartext' )
       apr_sha256( 'cleartext', :hash )
              SHA256 algorithm. The apr_sha256 routine returns the  identifier
              {SHA256} plus the base64 encoded sha256 hash.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.

              The result of the routine is CHAR(52). The result can  be  null;
              if the argument is null, the result is the null value.

       sha256_hex( 'cleartext' )
       sha256_hex( 'cleartext', :hash )
              SHA256  algorithm. The sha256_hex routine returns a 64-character
              hexadecimal hash.

              The argument can be a character string that is either a CHAR  or
              VARCHAR not exceeding 4096 bytes.

              The  result  of the routine is CHAR(64). The result can be null;
              if the argument is null, the result is the null value.

       sha1_hex( 'cleartext' )
       sha1_hex( 'cleartext', :hash )
              SHA1 algorithm. The  sha1_hex  routine  returns  a  40-character
              hexadecimal hash.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.

              The result of the routine is CHAR(40). The result can  be  null;
              if the argument is null, the result is the null value.

       sha256( 'cleartext' [, 'salt'] )
       sha256( 'cleartext' [, 'salt'], :hash )
              SHA256  algorithm.  The  sha256 routine returns a glibc2's crypt
              hash. If the system's crypt does not support  sha-256,  an  SQL-
              STATE 39702 is returned.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.  An optional salt can be spec-
              ified,  which  must  be a eight-character string chosen from the
              set [a-zA-Z0-9./]. If the salt is not exactly  eight  characters
              long,  an  SQLSTATE  39703  is  returned.  If  the salt contains
              invalid characters, an SQLSTATE 39704 is returned.

              The result of the function is CHAR(55). The result can be  null;
              if one of the arguments is null, the result is the null value.

       sha512( 'cleartext' [, 'salt'] )
       sha512( 'cleartext' [, 'salt'], :hash )
              SHA512  algorithm.  The  sha512 routine returns a glibc2's crypt
              hash. If the system's crypt does not support  sha-512,  an  SQL-
              STATE 39702 is returned.

              The  argument can be a character string that is either a CHAR or
              VARCHAR not exceeding 4096 bytes.  An optional salt can be spec-
              ified,  which  must  be a eight-character string chosen from the
              set [a-zA-Z0-9./]. If the salt is not exactly  eight  characters
              long,  an  SQLSTATE  39703  is  returned.  If  the salt contains
              invalid characters, an SQLSTATE 39704 is returned.

              The result of the routine is CHAR(98). The result can  be  null;
              if one of the arguments is null, the result is the null value.

       validate_pw( 'password', 'hash' )
       validate_pw( 'password', 'hash', :is_valid )
              This  routine can be used to validate a password against a hash.

              The two input arguments can be character strings that are either
              a  CHAR  or  VARCHAR not exceeding 4096 bytes (password) and 120
              bytes (hash). The second parameter (hash)  must  not  be  empty,
              otherwise an SQLSTATE 39701 is returned.

              The  result  of  the  routine  is an INTEGER. If the password is
              valid, 1 is returned.  If  the  password  is  not  valid,  0  is
              returned.  The  result can be null; if the argument is null, the
              result is the null value.


EXAMPLES

       bcrypt (1):
       Inserting the user test and the sha1 crypted clear text testpwd to  the
       table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', bcrypt('testpwd'))

       bcrypt (2):
       SELECT bcrypt('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       ------------------------------------------------------------
       $2y$05$2jb66aPElSkNLT1t8e6dQepuCY2BP3JnYUh0xeV9r1PEoOGyOLkym

         1 record(s) selected.

       bcrypt (3):
       CALL bcrypt('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $2y$05$WYSu1X6PVA0Ra.aPSjrdv.S6hOp.AYSnNRT521rmLRjD4Mj9UY6ve

         Return Status = 0

       php_md5 (1)
       Inserting  the  user test and the md5 hash of the clear text testpwd to
       the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', md5('testpwd'))

       php_md5 (2)
       SELECT php_md5( 'testpwd' ) FROM SYSIBM.SYSDUMMY1

       1
       --------------------------------
       342df5b036b2f28184536820af6d1caf

         1 record(s) selected.

       php_md5 (3)
       CALL php_md5('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : 342df5b036b2f28184536820af6d1caf

         Return Status = 0

       apr_md5 (1)
       Inserting the user test and the seeded md5 hash of the clear text
       testpwd to the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', apr_md5('testpwd'))

       apr_md5 (2)
       SELECT apr_md5('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       -------------------------------------
       $apr1$HsTNH...$bmlPUSoPOF/Qhznl.sAq6/

         1 record(s) selected.

       apr_md5 (3)
       CALL apr_md5('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $apr1$HsTNH...$bmlPUSoPOF/Qhznl.sAq6/

         Return Status = 0

       apr_crypt (1)
       Inserting the user test and the crypted clear text testpwd to the table
       users.

       INSERT INTO USERS (username, password)
         VALUES ('test', apr_crypt('testpwd'))

       apr_crypt (2)
       SELECT apr_crypt('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       -------------
       cqs7uOvz8KBlk

         1 record(s) selected.

       apr_crypt (3)
       CALL apr_crypt('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : cqs7uOvz8KBlk

         Return Status = 0

       apr_sha1 (1)
       Inserting  the user test and the sha1 crypted clear text testpwd to the
       table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', apr_sha1('testpwd'))

       apr_sha1 (2)
       SELECT apr_sha1( 'testpwd' ) FROM SYSIBM.SYSDUMMY1

       1
       ---------------------------------
       {SHA}mO8HWOaqxvmp4Rl1SMgZC3LJWB0=

         1 record(s) selected.

       apr_sha1 (3)
       CALL apr_sha1('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : {SHA}mO8HWOaqxvmp4Rl1SMgZC3LJWB0=

         Return Status = 0

       apr_sha256 (1):
       Inserting the user test and the sha256 crypted clear  text  testpwd  to
       the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', apr_sha256('testpwd'))

       apr_sha256 (2):
       SELECT apr_sha256('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       ----------------------------------------------------
       {SHA256}qFtqIIE8Maixs/NhjaeWJxyaopOz+AmHMFOyGuxQEIc=

         1 record(s) selected.

       apr_sha256 (3):
       CALL apr_sha256('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : {SHA256}qFtqIIE8Maixs/NhjaeWJxyaopOz+AmHMFOyGuxQEIc=

         Return Status = 0

       sha256_hex(1):
       Inserting  the  user  test and the sha256 crypted clear text testpwd to
       the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', sha256_hex('testpwd'))

       sha256_hex(2):
       SELECT sha256_hex('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       ----------------------------------------------------------------
       a85b6a20813c31a8b1b3f3618da796271c9aa293b3f809873053b21aec501087

         1 record(s) selected.

       sha256_hex(3):
       CALL sha256_hex('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : a85b6a20813c31a8b1b3f3618da796271c9aa293b3f809873053b21aec501087

         Return Status = 0

       sha1_hex(1):
       Inserting the user test and the sha1 crypted clear text testpwd to  the
       table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', sha1_hex('testpwd'))

       sha1_hex(2):
       SELECT sha1_hex('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       ----------------------------------------
       98ef0758e6aac6f9a9e1197548c8190b72c9581d

         1 record(s) selected.

       sha1_hex(3):
       CALL sha1_hex('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : 98ef0758e6aac6f9a9e1197548c8190b72c9581d

         Return Status = 0

       sha256 (1):
       Inserting  the  user  test and the sha256 crypted clear text testpwd to
       the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', sha256('testpwd'))

       sha256 (2):
       SELECT sha256('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       -------------------------------------------------------
       $5$S.LqPR7Z$273zPncMdmJ0dE1WdLldWVBmaHSDUDl8/tW8At8Hc0A

         1 record(s) selected.

       sha256 (3):
       CALL sha256('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $5$vSDCZr2d$rfh.aDopE5l3lm26AwwcIYnuVdV7/9QBACWukqYyV3/

         Return Status = 0

       sha256 (4):
       SELECT sha256('testpwd', '12345678') FROM SYSIBM.SYSDUMMY1

       1
       -------------------------------------------------------
       $5$12345678$.oVAnOr/.FK8fYNiFPvoXPQvEOT9Calecygw6K9wIb9

         1 record(s) selected.

       sha256 (5):
       CALL sha256('testpwd', '12345678', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $5$12345678$.oVAnOr/.FK8fYNiFPvoXPQvEOT9Calecygw6K9wIb9

         Return Status = 0

       sha512 (1):
       Inserting the user test and the sha512 crypted clear  text  testpwd  to
       the table users.

       INSERT INTO USERS (username, password)
         VALUES ('test', sha512('testpwd'))

       sha512 (2):
       SELECT sha512('testpwd') FROM SYSIBM.SYSDUMMY1

       1
       --------------------------------------------------------------------------------------------------
       $6$cD33haq7$dl.RqEaLamlesTPVzSIQr4N1MY3BsVZ76VS8qNte0IOIWO2XorMg8U797KKOFGmX8dJhT3WuF6p17HmvvoQ6Q/

         1 record(s) selected.

       sha512 (3):
       CALL sha512('testpwd', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $6$1W.m9JN1$Dh.VPl7vy.igGaeDUdDWw6ZlD0xufwDWm0ukpOYknPtdjxiSM2yzWBkzHffalb/2axNHPqEi9UUzXUbSm4LGa/

         Return Status = 0

       sha512 (4):
       SELECT sha512('testpwd', '12345678') FROM SYSIBM.SYSDUMMY1

       1
       --------------------------------------------------------------------------------------------------
       $6$12345678$tlHrypdWTz6FqubBpgL/ePlxr4lZuQ8OK1zfV6zWUmGJSz.5kGWwQGjg69Qm1Bm3.DvILruqA61o3EHsxSoko1

         1 record(s) selected.

       sha512 (5):
       CALL sha512('testpwd', '12345678', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : HASH
         Parameter Value : $6$12345678$tlHrypdWTz6FqubBpgL/ePlxr4lZuQ8OK1zfV6zWUmGJSz.5kGWwQGjg69Qm1Bm3.DvILruqA61o3EHsxSoko1

         Return Status = 0

       validate_pw (1)
       Validating the password testpwd against the crypt hash cqs7uOvz8KBlk.

       SELECT validate_pw('testpwd', 'cqs7uOvz8KBlk') FROM SYSIBM.SYSDUMMY1"

       1
       -----------
                 1

         1 record(s) selected.

       validate_pw (2)
       CALL validate_pw('testpwd', 'cqs7uOvz8KBlk', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : IS_VALID
         Parameter Value : 1

         Return Status = 0

       validate_pw (3)
       CALL validate_pw('testpwd', '0123456789abcdef', ?)

         Value of output parameters
         --------------------------
         Parameter Name  : IS_VALID
         Parameter Value : 0

         Return Status = 0


AUTHOR

       Written by Helmut K. C. Tessarek.


BUGS

       Hopefully none :-) But if you find one, please report it at:
       https://github.com/tessus/db2-hash-routines/issues


WEB SITE

       http://tessus.github.io/db2-hash-routines



db2-hash-routines 1.8             August 2017             db2-hash-routines(8)