Postgres digest() behaving differently with string concat versus bytea concat?

Question

I am attempting to replicate some postgres code that uses the pgcrypto digest function into Java. I was assuming that the following two commands would generate the same result:

select digest('Hi there' || 'Abc' || decode('00','hex'), 'sha256');
select digest('Hi thereAbc' || decode('00','hex'), 'sha256');

However, this is not the case... Those two commands produce different results:

postgres=# select digest('Hi there' || 'Abc' || decode('00','hex'), 'sha256');
                           digest                               
--------------------------------------------------------------------
 \xd4321124595112a1e8cd8d90709b8cf58aabdd14ad09ff972e6fe0f75bd25d97

postgres=# select digest('Hi thereAbc' || decode('00','hex'), 'sha256');
                           digest                               
--------------------------------------------------------------------
 \xf2477e4fcaf6ea37ab985fc1fa029a99fb331657e7a3e349ffc844e9d068f250

Any idea why those give different results?

On the other hand, doing the concatenation without the bytes on the end works as expected:

postgres=# select digest('Hi thereAbc', 'sha256');
                          digest                              
------------------------------------------------------------------
 \x3e1d7fed02b93824aba03835b93314f0293026276c8e50f4b70935f988e0c0bf

postgres=# select digest('Hi there' || 'Abc', 'sha256');
                          digest                              
------------------------------------------------------------------
 \x3e1d7fed02b93824aba03835b93314f0293026276c8e50f4b70935f988e0c0bf

(1 row)

Thanks!

Edit: Answer update As per @ferhat elmas's answer, the two inputs to digest are as follows:

select 'Hi thereAbc' || decode('00', 'hex');
 \x486920746865726541626300

select 'Hi there' || 'Abc' || decode('00', 'hex');
Hi thereAbc\x00

I was assuming those two values were equivalent - just one as bytes and the other as a string, but they are not - the backslash x 0 0 are literal characters in the string, not a single 0 byte.

Can you try with ('Hi there' || 'Abc') || decode('00','hex')? — Bergi
– Bergi, Commented Jan 9, 2020 at 15:15
Tried it out... select digest( ('Hi there' || 'Abc' ) || decode('00','hex'), 'sha256'); produces the same result as without the ( ): \xd4321124595112a1e8cd8d90709b8cf58aabdd14ad09ff972e6fe0f75bd25d97 — Jamie
– Jamie, Commented Jan 9, 2020 at 15:19

ferhatelmas · Accepted Answer · 2020-01-09 17:22:50Z

3

It's because how postgres resolves unknown types.

select 'Hi there' || 'Abc' || decode('00', 'hex')
       unknown || unknown || bytea -- see the snippet below from the docs
       string || bytea
       string

From docs:

In this case there is no initial hint for which type to use, since no types are specified in the query. So, the parser looks for all candidate operators and finds that there are candidates accepting both string-category and bit-string-category inputs. Since string category is preferred when available, that category is selected, and then the preferred type for strings, text, is used as the specific type to resolve the unknown-type literals as.

select 'Hi thereAbc' || decode('00', 'hex')
       unknown || bytea -- this step is hinted by the known type
       string

That's why in one case, the text is encoded as hex then concatenated but in the other case, they are concatenated directly because one case has the known type while the other hasn't.

To ensure consistency, it's better to provide type hints.

edited Jan 9, 2020 at 17:22

user330315

answered Jan 9, 2020 at 15:22

ferhatelmas

3,9981 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie Over a year ago

Unfortunately, I am unable to change the postgres code, as we have lots of data using this already. Do you think that the data being passed to digest is different in the two cases, or do you think that digest(text) uses a different algorithm from digest(bytea), or something else going on?

ferhatelmas Over a year ago

Inputs passed to digest are different. If you can ensure that values have proper type hints (::text or ::bytea), then it will be associative and should be fine.

Collectives™ on Stack Overflow

Postgres digest() behaving differently with string concat versus bytea concat?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related