I am by no means a database developer and use SQL and PL/SQL only for purposes to manage databases and working on it’s internals. Of course I have a basic understanding of it and my experience in developing/scripting in quite a large number of different languages during my career so far at least enables me to understand PL/SQL code written by others good enough for most purposes. But sometimes there are situations where one struggles to understand what’s happening and it is time to learn something new.

So it happened that I was working on a software that processeses data which is generated by an APEX application. To make sure that the data wasn’t changed during transport and all the data was vaild, some SHA512 checksums are generated and compared. I won’t go into detail about the data and application because it doesn’t matter for the problem observed. The one I faced was, that I needed to reproduce the check algorithm from the APEX application, which is obviously written in PL/SQL, in the go language. Part of that algorithm makes use of the DBMS_CRYPTO.MAC function like follows:

DBMS_CRYPTO.MAC('DEADBEEF', DBMS_CRYPTO.HMAC_SH512, 'F00');

This means that a message authentication code is generated for the message DEADBEEF with the secret key F00. Looking over the code and not caring about the signature of the function I just thought, that the function takes the string and uses the ASCII value of the characters and calculates the hash value. Same for the key parameter of course. So in go I cam up with the following code:

encryptMac := hmac.New(sha512.New, []byte("F00"))
encryptMac.Write([]byte("DEADBEEF"))
hmacSum := encryptMac.Sum(nil)
hmac := fmt.Sprintf("%X", hmacSum)

From a glance there’s not much difference and both implementations should return the same results. But that wasn’t the case. So after fiddling around and trying different nonsense I had a look at the signature of the DBMS_CRYPTO.MAC function and noticed that the param for key and src were specified as RAW data type.

DBMS_CRYPTO.MAC (
   src IN RAW,
   typ IN PLS_INTEGER,
   key IN RAW)
 RETURN RAW;

Since I didn’t find a formal specification of how the RAW type handles a string input in 2 minutes, I played around and found out that the hex values are directly interpreted as byte values in hex and not as characters in ASCII or Unicode. So let’s compare these different variants of how to handle character data. There’s a shift by the number of 31 in the integer value when comparing both variants.

ASCII: D  E  A  D  B  E  E  F
BYTE:  44 45 41 44 42 45 45 46

HEX:   D  E  A  D  B  E  E  F
BYTE:  13 14 10 13 11 14 14 15

To get the same result it was necessary to change the input for the mac algorithm from a converted string to integer values of the hex representation. In go that can be achieved using the fmt.Scanf function.

str := "DEADBEEF"
input := make([]byte, len(str)/2)
for i := range input {
  var val byte
  fmt.Sscanf(str[i*2:i*2+2], "%02X", &val)
  input[i] = val
}
encryptMac := hmac.New(sha512.New, []byte{15,0,0})
encryptMac.Write([]byte(input))
hmacSum := encryptMac.Sum(nil)
hmac := fmt.Sprintf("%X", hmacSum)

Of course this is well documented in the Oracle online documentation. This says the following about the RAW data type for release 19c.

When Oracle implicitly converts RAW or LONG RAW data to character data, the resulting character value contains a hexadecimal representation of the binary input, where each character is a hexadecimal digit (0-9, A-F) representing four consecutive bits of RAW data.

This obviously is also true for the other way around and needs to be taken care of. Since after some research I was not the only one not getting the expected results when DBMS_CRYPTO was involved, I just left that here for the world ;-).