PostgreSQL – Fix Base64 Encoded SHA512 Digest String Equality Test

postgresqlpostgresql-10

I have the following table

   Column    |            Type            | Collation | Nullable |
-------------+----------------------------+-----------+----------+-------------------
 name        | text                       |           | not null |
 code        | text                       |           | not null |

I insert new elements using the following INSERT

INSERT INTO table(name, code)
    VALUES('SOMENAME',encode(digest(gen_random_uuid()::text,'sha512'), 'base64')) ;

Now when I try to SELECT against the code field:

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

It returns an empty set.

----------
(0 rows)

The puzzling part is that it seems to break only for SHA512 with base64 encoding , it works just fine if I use SHA256 or if I change the encoding to HEX.

I have a feeling that it might be a bug, but am I missing something?

Best Answer

With 64 bytes, the sha512 digest is large enough that encoding it in base64 generates a newline character (LF) in the result (at about 76 characters from the start).

Probably what goes wrong is that newline character not being correctly reinjected in the SELECT query, where you truncated the literal with an ellipsis (so we can't actually see it):

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

To inject a newline, you may use the E'...' syntax and have \n to denote that character.

SELECT * FROM tabel WHERE code= E'K5HUaR.....\n...rest of string';

Related Solutions

Postgresql – Postgres copy data with \xYY as plain string instead of interpreting as encoded string

The backslash \ is the default escape character in the (default) text format of COPY. The format you describe is recognized as (quoting the manual here)

backslash sequences are recognized by COPY FROM
...
\xdigits | Backslash x followed by one or two hex digits specifies the character with that numeric code

.. which seems to be just as intended.
To avoid this effect you could use the CSV format instead, where the backslash has no special meaning:

COPY tbl FROM '/path/to/file/log.sql' (FORMAT csv);

Be sure to read the manual to learn about additional differences.

Alternatively you could replace every \ with \\ in your file with a sed script or something.

If the above doesn't solve your problem, please supply:

PostgreSQL version
Database encoding
Demo data
Definition of the table you COPY to
The exact COPY invocation you used

Postgresql – In Postgres, How to merge two tables

One approach could be to use a writable view to transform results in-query, eg

ALTER TABLE utilities.labpreferences
    RENAME TO utilities.old_labpreferences

CREATE TABLE utilities.labpreferences
( utilities.old_labpreferences EXCLUDING DEFAULTS )
WITHOUT OIDS;

CREATE OR REPLACE RULE "labpreferences_select" AS
  ON SELECT TO utilities.labpreferences
  DO INSTEAD
  SELECT -- fields with modifications --
    FROM utilities.old_labpreferences
    WHERE s.id like 'My.%'
  UNION
  SELECT -- fields without modification --
    FROM utilities.old_labpreferences
    WHERE s.id not like 'My.%'
    ;

Backing the changes out is as simple as removing the rule and new (empty) table, and renaming the old table back.

You will also need rules for update/insert/delete to this table - the postgres documentation for DO INSTEAD is pretty good on this front - see http://www.postgresql.org/docs/devel/static/rules-views.html

Best Answer

Related Solutions

Postgresql – Postgres copy data with \xYY as plain string instead of interpreting as encoded string

Postgresql – In Postgres, How to merge two tables

Related Question