Converting MS SQL Hash Text to Postgres Hash Text

Question

I am working on migrating all our databases from MS SQL server to Postgres. In this process, I am working on writing equivalent code in Postgres to yield the same hashed texts obtained in MS SQL.

Following is my code in MS SQL:

DECLARE @HashedText nvarchar(50) 
DECLARE @InputText nvarchar(50) = 'password'

DECLARE @HashedBytes varbinary(20) -- maximum size of SHA1 output

SELECT @HashedBytes = HASHBYTES('SHA1', @InputText)

SET @HashedText = CONVERT(nvarchar(50), @HashedBytes, 2)

SELECT @HashedText

This is yielding the value E8F97FBA9104D1EA5047948E6DFB67FACD9F5B73

Following is equivalent code written in Postgres:

DO
$$
DECLARE v_InputText VARCHAR = 'password';
DECLARE v_HashedText  VARCHAR;
DECLARE v_HashedBytes BYTEA;

BEGIN 
  SELECT 
         ENCODE(DIGEST(v_InputText, 'SHA1'), 'hex')
  INTO
         v_HashedBytes;

  v_HashedText := CAST(v_HashedBytes AS VARCHAR);

  RAISE INFO 'Hashed Text: %', v_HashedText;
END;
$$;

This yields the value 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8.

After spending some time I understood that replacing the datatype 'NVARCHAR' with 'VARCHAR' in MS SQL yields the same result as Postgres.

Now the problem is in MS SQL we already have passwords hashed and stored in database as shown above. I am unable to convert hashed text in MS SQL to Postgres and also unable to generate same hashed text in Postgres as Postgres doesn't support UTF-16 unicode.

So, I just want to know if there is any possibility of following solutions?

Convert hexadecimal value generated in MS SQL to hex value equivalent to that generated by using VARCHAR datatype (which is same value in Postgres)
Convert UTF8 texts to UTF16 texts in Postgres (even by any kind of extensions) and generate hex values which would be equivalent to values generated in MS SQL

you shouldn't use SHA1 annymore especially without salt and or pepper because off large existing SHA1 rainbow tables which can "decrypt" the SHA1 hashes within seconds.. Besides SHA1 algorithm is a fast algorithm on CPU's but also on GPU's makes it possible to do 1 million+ geusses per second with ease on modern GPU hardware.. — Raymond Nijland
– Raymond Nijland, Commented Sep 14, 2018 at 11:06
This is extremely insecure code. It looks like the password is transmitted in clear text for starters. Passwords need to be hashed multiple times too, even with strong hashing algorithms like SHA25, SHA512. 1000 iterations is typical. A single SHA1 has is very weak — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 14, 2018 at 11:27
Furthermore, the real hash is the byte array, not the string. Where is the hash stored, in what form? As a varbinary(20) or as a varchar? BTW VARCHAR without a length doesn't mean arbitrary length. I think the default lenght is 30. You may be truncating the hash already — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 14, 2018 at 11:29

IMSoP · Accepted Answer · 2018-09-14 11:22:15Z

2

Let's look at your suggestions in turn:

Convert hexadecimal value generated in MS SQL to hex value equivalent to that generated by using VARCHAR datatype (which is same value in Postgres)

This comes down to converting the user's password from UTF-16 to UTF-8 (or to some other encoding) and re-hashing it. To do this, you need to know the user's password, which theoretically, you don't - that's the point of hashing it in the first place.

In practice, you're using unsalted SHA1 hashes, for which large pre-computed tables exist, and for which brute force is feasible with a GPU-optimised algorithm. So a "grey hat" option would be to crack all your user's passwords, and re-hash them.

If you do so, it would probably be sensible to re-hash them using a salt and better hash function, as well as converting them to UTF-8.

Convert UTF8 texts to UTF16 texts in Postgres (even by any kind of extensions) and generate hex values which would be equivalent to values generated in MS SQL

This, theoretically, is simpler, you just need a routine to do the string conversion. However, as you've found, there is no built-in support for this in Postgres.

For any string composed entirely of ASCII characters, the conversion is trivial: insert a NULL byte (hex 00) before every byte of the string. But this will break any password that used a character not in this range.

An alternative would be to move the responsibility for generating the hash out of the database:

Retrieve the hash from the DB.
Ensure the user's input is represented as UTF-16 in your application, and calculate its hash.
If valid, you can now generate a new hash (because you know the password the user just typed), using a better algorithm, and store that in the DB instead of the old hash.
Once all active users have logged in at least once, you will have no SHA1 hashes left, and can remove the support for them completely.

answered Sep 14, 2018 at 11:22

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

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

1 Comment

D Surya Praveen Over a year ago

Thanks for all your answers!! Actually we used SALT text, however in order not to reveal our own salt on security reasons, I didn't put that code here. W.r.t. your suggestion regarding inserting NULL byte before every byte of string to convert to UTF16 in Postgres, can you send any sample code for me? I'm unable to develop it. Your alternate solution for generating hash from UTF-16 in application and then dropping it once all active users are logged at least once also sounds great!

Collectives™ on Stack Overflow

Converting MS SQL Hash Text to Postgres Hash Text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related