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)