Firebird: How to make an md5 hash?

Question

I want to update all passwords in the DB to have a MD5 hashes password. The following doesn't solve my problem:

UPDATE USERS SET USERPASS = hash('SALT' || USERPASS);

Problem being: it returns a hash that was not generated using the MD5 algorithm. How can I impliment the md5 hash algorithm in Firebird?

Answer 1

The next version of Firebird, Firebird 4, will add cryptographic hash functions. To quote from the Firebird 4 Beta 1 release notes:

Tracker ticket CORE-4436

Returns a hash for a string using a specified algorithm. Format is:

HASH( <string> [ USING <algorithm> ] )    
algorithm ::= { MD5 | SHA1 | SHA256 | SHA512 }

The syntax with the optional USING clause is introduced in FB 4.0 and returns VARCHAR strings in character set OCTETS.

Important

The syntax without the USING clause is still supported. It uses the 64-bit variation of the non-cryptographic PJW hash function (also known as ELF64):
https://en.wikipedia.org/wiki/PJW_hash_function
which is very fast and can be used for general purposes (hash tables, etc), but its collision quality is sub-optimal. Other hash functions (specified explicitly in the USING clause) should be used for more reliable hashing.

Examples

select hash(x using sha256) from y;
-- 
select hash(x) from y; -- not recommended 

Firebird 4 Beta 1 can be downloaded for testing from https://www.firebirdsql.org/en/firebird-4-0-0-beta1/

Answer 2

Unfortunately the algortihm of the hash function is not mentioned in the documentation... anyway, you can implement it as an UDF or use some thirdparty UDF lib which implements it. There is a list of UDF Libraries for Firebird on the IBPhoenix website and it seems that both rFunc and FreeAdhocUDF library have one (I only checked these two, there is probably others).