The same results of HASH function with different inputs

[Roland Turcan]

Hello all!

I have found a very strange !?BUG?!

Execute this SQL and compare the values. All are the same...

SELECT HASH(COALESCE('EXE_SERVIS_KONTROLA_PLATBA_POV', '') ||

            COALESCE(1947, '') ||

            COALESCE(3011, '') ||

            COALESCE('Suma úhrad od povinného od 01.07.2020 do 31.07.2020 nedosiahla požadovanú výšku', '') ||

            COALESCE('Spis 24/2018 nemal v sledovanom období od 01.07.2020 do 31.07.2020 vymožených 40,00 €, vymožené bolo 10,00 €.', '')),

       HASH(COALESCE('EXE_SERVIS_KONTROLA_PLATBA_POV', '') ||

            COALESCE(1947, '') ||

            COALESCE(3011, '') ||

            COALESCE('Suma úhrad od povinného od 01.07.2021 do 31.07.2021 nedosiahla požadovanú výšku', '') ||

            COALESCE('Spis 24/2018 nemal v sledovanom období od 01.07.2021 do 31.07.2021 vymožených 40,00 €, vymožené bolo 10,00 €.', '')),

       HASH(COALESCE('EXE_SERVIS_KONTROLA_PLATBA_POV', '') ||

            COALESCE(1947, '') ||

            COALESCE(3011, '') ||

            COALESCE('Suma úhrad od povinného od 01.07.2022 do 31.07.2022 nedosiahla požadovanú výšku', '') ||

            COALESCE('Spis 24/2018 nemal v sledovanom období od 01.07.2022 do 31.07.2022 vymožených 40,00 €, vymožené bolo 10,00 €.', ''))

  FROM RDB$DATABASE;

What could be a reason?

Thanks. Regards, RT

[Tomasz Tyrakowski]

On 29.10.2021 at 10:25, Roland Turcan wrote:
> Hello all!
>
> I have found a very strange !?BUG?!
>
> Execute this SQL and compare the values. All are the same...

> [...]

Tried and got three different hashes:

4046780615757864960
2893859111151013888
6352623624971583488

FB 2.5.9, Win64.

regards
Tomasz

[Roland Turcan]

Hello all,

I have simplified it to get it:

SELECT HASH(COALESCE('do 31.07.2020 ', '')),

       HASH(COALESCE('do 31.07.2021 ', '')),

       HASH(COALESCE('do 31.07.2022 ', ''))

  FROM RDB$DATABASE;

result 3 times the same: 482226288880596256

but

SELECT HASH(COALESCE(' 31.07.2020', '')),

       HASH(COALESCE(' 31.07.2021', '')),

       HASH(COALESCE(' 31.07.2022', ''))

  FROM RDB$DATABASE;

HASH;HASH1;HASH2

38912733041488;38912733041489;38912733041490

But the difference is only in last digit...

Ufffffff.

[Roland Turcan]

Thanks for fast response, but I have tried it on FB 3.0.* mostly 3.0.7

[Dimitry Sibiryakov]

Roland Turcan wrote 29.10.2021 10:37:
> But the difference is only in last digit...

Default hash algorithm is not a cryptographic one, collisions on similar
strings are very probable.

--
WBR, SD.

[Roland Turcan]

Thanks for response.

But how to to get rid of this problem on FB 3? I have used this function on many places...

Thanks.

[Dimitry Sibiryakov]

Roland Turcan wrote 29.10.2021 11:45:
> But how to to get rid of this problem on FB 3? I have used this function on many
> places...

There is no problem. Every hash is supposed to have collisions and must not
be used alone for equality check.
That said, if hash values are different - sources are guaranteed to be
different, if hash values are the same - sources are NOT guaranteed to be the
same. This is a basic property of hashes that must be known.

--
WBR, SD.

[Mathias Pannier (unitel)]

Use md5 hash via an udf. For example something like this: https://github.com/kotalr/fbshash

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7003fe01-e013-4549-90d6-5f9258db5c18n%40googlegroups.com.

ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna
Geschaeftsfuehrung Klaus Richter, Olaf Meyer
Amtsgericht Stendal
HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778
Deutsche Bank IBAN DE53 86070024 0 6143234 00
Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82
_____________________________________________________________________
Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet
ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes
Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs-
äußerung ist die des Autors und stellt nicht notwendigerweise die
Ansicht oder Meinung von ub.unitel GmbH dar.
Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich
erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung,
Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt.
_____________________________________________________________________

[Mathias Pannier (unitel)]

But there can be collisions, too. See also this discussion: https://github.com/FirebirdSQL/firebird/issues/4756

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9e11c2754f474bb58fc730f0d75af0e4%40NT-Server11.unitel.lokal.

[Omacht András]

Roland, just be careful with UDFs, they are deprecated in FB4.0.

 

https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-compat-udfs

 

András

 

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9e11c2754f474bb58fc730f0d75af0e4%40NT-Server11.unitel.lokal.

[Mark Rotteveel]

On 29-10-2021 11:45, Roland Turcan wrote:
> Thanks for response.
>
> But how to to get rid of this problem on FB 3? I have used this function
> on many places...

What is the problem you're trying to solve? Or phrased differently: why
is hash collision a problem for you in this case? The HASH function uses
ELF64, which is fast, but doesn't have good collision properties.

If you want a cryptographic hash, you can either upgrade to Firebird 4.0
and use CRYPT_HASH[1], or use a UDF or UDR that provides cryptographic
hashes.

[1]:
https://www.firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-functions-string.html#fblangref40-scalarfuncs-crypthash

--
Mark Rotteveel