Converting MS SQL Hash Text to Postgres Hash Text

user_2118607 0
Viewed 763

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?

  1. Convert hexadecimal value generated in MS SQL to hex value equivalent to that generated by using VARCHAR datatype (which is same value in Postgres)
  2. 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
hash postgresql
1 Answers

Let's look at your suggestions in turn:

  1. 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.

  1. 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:

  1. Retrieve the hash from the DB.
  2. Ensure the user's input is represented as UTF-16 in your application, and calculate its hash.
  3. 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.
  4. 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.
user_1579570
Related