Hash an integer in Python to match Oracle's STANDARD_HASH

Question

In Oracle, my data has been hashed by passing an integer into `STANDARD_HASH' as follows. How can I get the same hash value using Python?

Result in Oracle when an integer passed to STANDARD_HASH:

SELECT STANDARD_HASH(123, 'SHA256') FROM DUAL;
# A0740C0829EC3314E5318E1F060266479AA31F8BBBC1868DA42B9E608F52A09F

Result in Python when a string is passed in:

import hashlib

hashlib.sha256(str.encode(str(123))).hexdigest().upper()
# A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3
# I want to modify this function to get the hash value above.

Maybe this information will also help. I cannot change anything on the Oracle side, but if I could, I would convert the column to CHAR and it would give the same value as my current Python implementation. An example follows.

Result in Oracle when a string passed to STANDARD_HASH:

SELECT STANDARD_HASH('123', 'SHA256') FROM DUAL;
# A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3 (matches Python result)

I've made several attempts, like simply passing in an integer to Python, but this results in the error that a string is required. I've also searched for a way to encode the integer, but haven't made any progress.

Answer 1

Oracle represents numbers in its own internal format, which can be seen using the dump() function in Oracle. E.g.,

SELECT dump(123) FROM dual;

Typ=2 Len=3: 194,2,24

So, to hash a number in Python and get the same result as in Oracle, you need to convert the Python number to a set of bytes the same way that Oracle does it in its internals.

A good analysis of the internal logic Oracle uses can be found here. It is correct with one minor omission having to do with terminating negative numbers. Also, it is written from the perspective of decoding an Oracle number from its bytes. In our case, we need to encode an Oracle number to its internal byte format. Nevertheless, I used it extensively in forming this answer.

The code below shows a Python function, to_oracle_number(), which will return an array of integers having the same byte representation of a number as the Oracle database would calculate. It should handle any number (positive, negative, fractional, zero, etc).

The code at the very bottom also shows how to call this function and hash its results to get the same hash value as was computed in the Oracle database, which I believe is the core of your question.

NOTE: The function expects the number you want to convert to be passed in as a string, to avoid precision losses.

import math
import decimal
import hashlib

def to_oracle_number( nstr ):
  # define number n that we want to convert
  n = decimal.Decimal(nstr)

  # compute exponent (base 100) and convert to Oracle byte along with sign
  #print (abs(n))
  l_exp = 0
  l_len = 0

  l_abs_n = abs(n)


  if l_abs_n != 0:
    l_exp = math.floor(math.log(l_abs_n,100))
    # Oracle adds 1 to all bytes when encoding
    l_exp = l_exp + 1
    # Oracle adds 64 to exponent whe encoding
    l_exp = l_exp + 64

  if n < 0:
    # take 1's complement of exponent so far (bitwise xor)
    l_exp = (l_exp ^ 127)

  if n >= 0:
    # add sign bit.  zero is considered positive.
    l_exp = l_exp + 128

  l_bytes = []
  l_bytes.append(l_exp)

  l_len = l_len + 1   # exponent and sign take 1 byte

  l_whole_part = str(int(l_abs_n))
  # make sure there is an even number of digits in the whole part
  if len(l_whole_part) % 2 == 1:
    l_whole_part = '0' + l_whole_part

  # get the fractional digits, so if 0.01234, just 01234
  l_frac_part = str(l_abs_n - int(l_abs_n))[2:]
  # make sure there is an even number of digits in the fractional part
  if len(l_frac_part) % 2 == 1:
    l_frac_part = l_frac_part + '0'

  l_mantissa = l_whole_part + l_frac_part

  # chop off leading 00 pairs
  while l_mantissa[0:2] == '00':
    l_mantissa = l_mantissa[2:]

  # chop off trailing 00 pairs
  while l_mantissa[-2:] == '00':
    l_mantissa = l_mantissa[:-2]

  # compute number of 2-character chunks
  l_chunk_count = int(len(l_mantissa) / 2)

  l_chunks = '';

  for i in range(0, l_chunk_count):
    l_chunk = int(l_mantissa[i*2:i*2+2])
    if n < 0:
      # for negative numbers, we subtract from 100
      l_chunk = 100-l_chunk

    # Oracle adds 1 to all bytes
    l_chunk = l_chunk + 1

    # Add the chunk to our answer
    l_chunks = l_chunks + ',' + str(l_chunk)
    l_bytes.append(l_chunk)
    l_len = l_len + 1   # we have computed one more byte
    #print (str(i) + ':' + str(l_chunk))

  if n < 0 and l_len < 21:
    # terminating negative numbers always end in byte 102 (do not know why)
    l_chunks = l_chunks + ',102'
    l_bytes.append(102)
    l_len = l_len + 1

  l_computed_dump = 'Typ=2 Len=' + str(l_len) + ': ' + str(l_exp) + l_chunks
  print  (l_computed_dump)
  print  (l_bytes)

  return l_bytes


# test it

m = hashlib.sha256()
b = bytes(to_oracle_number('123'))  # pass a string so no precision errors
m.update(b)
print(m.hexdigest().upper())

OUTPUT

Typ=2 Len=3: 194,2,24
[194, 2, 24]
A0740C0829EC3314E5318E1F060266479AA31F8BBBC1868DA42B9E608F52A09F

Answer 2

WARNING: The original solution to the thread comes from @Matthew McPeak's and that's the answer should be rewarded, below you'll find a slightly modified version where I've added a bit of refactoring to his algorithm though:

import math
import decimal
import hashlib


def to_oracle_number(nstr):
    n = decimal.Decimal(nstr)

    # compute exponent (base 100) and convert to Oracle byte along with sign
    l_exp, l_len, l_abs_n = 0, 0, abs(n)

    if l_abs_n != 0:
        l_exp = math.floor(math.log(l_abs_n, 100)) + 65

    l_exp = (l_exp ^ 127) if n < 0 else l_exp + 128
    l_bytes = [l_exp]
    l_len += 1   # exponent and sign take 1 byte
    l_whole_part = str(int(l_abs_n))

    # make sure there is an even number of digits in the whole part
    if len(l_whole_part) % 2 == 1:
        l_whole_part = '0' + l_whole_part

    # get the fractional digits, so if 0.01234, just 01234
    l_frac_part = str(l_abs_n - int(l_abs_n))[2:]

    # make sure there is an even number of digits in the fractional part
    if len(l_frac_part) % 2 == 1:
        l_frac_part += '0'

    l_mantissa = l_whole_part + l_frac_part

    # chop off leading 00 pairs
    while l_mantissa[0:2] == '00':
        l_mantissa = l_mantissa[2:]

    # chop off trailing 00 pairs
    while l_mantissa[-2:] == '00':
        l_mantissa = l_mantissa[:-2]

    # compute number of 2-character chunks
    l_chunks = ''

    for i in range(0, int(len(l_mantissa) / 2)):
        l_chunk = int(l_mantissa[i * 2:i * 2 + 2])
        if n < 0:
            l_chunk = 100 - l_chunk

        l_chunk += 1
        l_chunks = f"{l_chunks},l_chunk"
        l_bytes.append(l_chunk)
        l_len += 1

    if n < 0 and l_len < 21:
        # terminating negative numbers always end in byte 102 (do not know why)
        l_chunks += ',102'
        l_bytes.append(102)
        l_len += 1

    # bytes(l_bytes)l_computed_dump = f"Typ=2 Len={l_len}: {l_exp}{l_chunks}"
    m = hashlib.sha256()
    m.update(bytes(l_bytes))
    return m.hexdigest().upper()


if __name__ == '__main__':
    assert to_oracle_number('123') == "A0740C0829EC3314E5318E1F060266479AA31F8BBBC1868DA42B9E608F52A09F"