# Using SPA1 hashing algorithm in Excel to test authenticity of RFID chip

#### SerenityNetworks

##### Board Regular
Following are the instructions I've been provided by the manufacturer. I'm neither a developer nor knowledgeable about ascii, hex, or binary coding. I've found Excel and VBA SHA1 hashing algorithms (here) and tried to implement them. But for the test values provided I've never been able to have the "167F" value returned. Someone in another company I know was able to test and return the "167F" value using the VB code I've provided further down, but I have no idea how to implement such code and it's not something I can request of the other person. I'm still stuck with trying to figure out how to successfully implement a checking tool in Excel if possible or another tool if not. Excel would be my preferred solution. Any help will be greatly appreciated.

Note: I have found Microsoft documentation on the "SHA1CryptoServiceProvider" referenced in the VB code at the bottom of this post. But it's beyond my technical understanding.

Andrew

Code:
``````Below is example of how the UII validation bytes shall be calculated using:

[FONT=Arial]1.         [/FONT]The first 10 bytes of the UII (starting with the “DSFID” field)
[FONT=Arial]2.         [/FONT]The 32 byte key (determined by the issuing group)

[FONT=Arial]3.         [/FONT]The bytes of the RFID's TID [1] (length varies - see footnote)

The open standard SHA1 hashing algorithm shall be used. The hashing sequence shall be:

[FONT=Arial]a.     [/FONT]Concatenate the 10 UII memory bytes, the 32 byte key and the TID bytes to form a single byte sequence
[FONT=Arial]b.     [/FONT]Determine the SHA1 hash of this byte sequence above
[FONT=Arial]c.     [/FONT]For UII Validation the first 2 bytes from the 40 byte hash result shall be used.

UII Validation Reference Calculation is provided below:
UII 10 byte:          0x[B][FONT=Courier]FFFFFFFFFFFFFFFFFFFF[/FONT][/B]
Key 32 byte: 0x[B][FONT=Courier]ABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABAB[/FONT][/B]
TID (12 byte example):    0x[B][FONT=Courier]000000000000000000000000[/FONT][/B]
Result 20 byte hash value:     0x[B][FONT=Courier]167F9C5B3933148B68AAD51EE3C4B5F858166451[/FONT][/B]
UII Validation bytes:         0x[B][FONT=Courier]167F[/FONT][/B]
The 12 UII bytes shall be encoded as: 0x[B][FONT=Courier]FFFFFFFFFFFFFFFFFFFF167F

[/FONT][/B]
[1] The TID length can vary per the ISO 18000-63 specification.  For fully serialized RFIDs, the complete header and serialized portion of the TID (which can be anywhere from 96 - 192 bits, given the allowable serial number length of 48 - 144 bits) will be used for the indicated calculations.  Per the standard, the length of a RFID's TID serial number is indicated on each, in bits 20h - 22h of the TID.

Code:
``````Here is the VB code (vb.net) that another organization uses to calculatethe SHA1 hash value from EPC + Agency-Key + TID.  The function below will calculate the following hash value: 167F

(Based on the example)

EPC: FFFFFFFFFFFFFFFFFFFFFFFF
KEY: ABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABAB
TID: 000000000000000000000000
Hash: 167F

Function epc_validator(vc_agencyKey As String, ByVal vc_epc As String, vc_tid As String) As String
Dim lo_sha1 As SHA1 = New SHA1CryptoServiceProvider()
Dim la_resulthash() As Byte
Dim lc_validation As String = ""

vc_epc = vc_epc.Substring(0,20)

Try
la_resulthash = lo_sha1.ComputeHash(strhex_to_byte(vc_epc + vc_agencyKey + vc_tid))
lc_validation = byte_to_strhex(la_resulthash, "").Substring(0, 4)
Catch ex As Exception
lc_validation = ""
End Try

Return lc_validation
End Function``````

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Here is the VBA equivalent of the epc_validator function, plus supporting conversion functions and a test macro which returns 167F for the test values.

Code:
``````Option Explicit

Public Sub test()

Dim key As String, epc As String, tid As String, hash As String
Dim expectedHash As String

key = "ABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABABAB"
epc = "FFFFFFFFFFFFFFFFFFFFFFFF"
tid = "000000000000000000000000"
expectedHash = "167F"

hash = epc_validator(key, epc, tid)
MsgBox "Hash = " & hash, Title:=IIf(hash = expectedHash, "Expected result", "Unexpected result")

End Sub

Public Function epc_validator(vc_agencyKey As String, ByVal vc_epc As String, vc_tid As String) As String

Dim lo_sha1 As Object
Dim la_resulthash() As Byte
Dim bytes() As Byte

Set lo_sha1 = CreateObject("System.Security.Cryptography.SHA1CryptoServiceProvider")

bytes = HexStringToBytes(Left(vc_epc, 20) & vc_agencyKey & vc_tid)
la_resulthash = lo_sha1.ComputeHash_2(bytes)
epc_validator = UCase(Left(BytesToHexString(la_resulthash), 4))

End Function

Public Function HexStringToBytes(hexString As String) As Byte()

'For each pair of hex digits in a hex string, convert them to their byte value and return an array of the bytes

Dim bytes() As Byte
Dim i As Long, b As Long
Dim hex1 As Long, hex2 As Long

ReDim bytes(Len(hexString) \ 2 - 1) As Byte

b = 0
For i = 1 To Len(hexString) Step 2
hex1 = InStr(1, "0123456789ABCDEF", Mid(hexString, i, 1), vbTextCompare) - 1
hex2 = InStr(1, "0123456789ABCDEF", Mid(hexString, i + 1, 1), vbTextCompare) - 1
bytes(b) = hex1 * &H10 + hex2
b = b + 1
Next

HexStringToBytes = bytes

End Function

Private Function BytesToHexString(bytes() As Byte) As String

'Convert an array of bytes to a hex string

Dim DOMdoc As Object

Set DOMdoc = CreateObject("MSXML2.DOMDocument")
With DOMdoc
.DocumentElement.DataType = "bin.Hex"
.DocumentElement.nodeTypedValue = bytes
BytesToHexString = Replace(.DocumentElement.Text, vbLf, "")
End With

End Function``````

Here is a pure VBA version of the BytesToHexString function in my previous post. The previous function is only really needed for fast conversion of an array with thousands of bytes.

Code:
``````Private Function BytesToHexString(bytes() As Byte) As String

'Convert an array of bytes to a hex string

Dim i As Long

BytesToHexString = ""
For i = LBound(bytes) To UBound(bytes)
BytesToHexString = BytesToHexString & Hex(bytes(i))
Next

End Function``````

Last edited:
Thank you!

John,

Thank you! This is perfect.

It's going to take me a bit of study to understand the code, but it works and gets me off the ground.

Thanks again,
Andrew

Replies
1
Views
738
Replies
1
Views
811
Replies
6
Views
947
Replies
3
Views
1K
Replies
21
Views
2K

1,196,369
Messages
6,014,885
Members
441,854
Latest member
Amstaff

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back