Accessing all REST methods with an ApiKey

Bascy · 2014-01-17 09:40:11

We are investigating the possibilities of accessing data in our server application from within Excel, especially using PowerQuery.
We did manage to use normal authentication scheme from Mormot by writing VBA functions to handle the comminication and the calculation the session_signature parameter. Sadly, there is no way from PowerQuery to call VBA functions. Probably has to do with the possibility to store theses sheets on the cloud, where powerQueries can still be executed, but VBA methods cannot.

Main problem is the fact that (with the same REST command) the actual URI will be different every time because it must contain the (ever increasing) timestamp.

A widely spread alternative way to access data via REST is to use an ApiKey as a kind of special password. We want to implement this using a special call to ask the server for a ApiKey for a specific user (stored in the TSQLAuthUser table), which will timeout when not used for some time. As long as the timeout has not expired, the ApiKey can be used as a parameter in the URI and provide authentication.

http://localhost:8080/API/GetApiKey?User=<username>&password=<sha256encodedpassword>

This call will return a valid ApiKey (when the credentials check out of course). With this Apikey all other available REST command should now be available (without any additional registration of published methods or interfaces) by calling them like this:

http://localhost:8080/API/SampleRecord?ApiKey=<ApiKey>
http://localhost:8080/API/SampleRecord/1?ApiKey=<ApiKey>
http://localhost:8080/API/Sum?ApiKey=<ApiKey>&a=23&b=12

What would be the best/easiest/most robust way to implement this?

Note: To provide extra security use of https would be advised of course, but that is irrelevant to the concept

Last edited by Bascy (2014-01-17 09:52:47)

ab · 2014-01-17 17:41:25

This is very close to the native URI-based-signature authentication scheme already implemented in mORMot.

But I do not like this pattern, which is not secure: it is very sensitive to MIM (man in the middle) or Replay attacks.

You can define your own authentication class if you want to implement this scheme.
The SAD 1.18 pdf and the source code is available.

warleyalex · 2014-01-20 11:31:12

Bascy wrote:

We did manage to use normal authentication scheme from Mormot by writing VBA functions to handle the comminication and the calculation the session_signature parameter.

I am just struggling to figure out how to connect mORMot and Excel through VBA to pull the content
into a Excel sheet. My biggest problem is I can pull the JSON content into a Excel sheet but I
cannot update cells on the fly (I need to close Excel and connect to service again to update).
I found the VBJSON library which has helped me a bit. Can you share some VBA
that handle to mORMot authentication?

ab · 2014-01-20 12:12:31

warleyalex wrote:

Can you share some VBA that handle to mORMot authentication?

Unfortunately, I'm no VBA expert (never used it on production) and I do not know any mORMot user which did access via a VBA client.
It should be feasible, of course, especially with weak authentication.

Could not the VBA execute a Delphi dll to access the remote data?
See http://msdn.microsoft.com/en-us/library/office/bb687915

Bascy · 2014-01-21 11:35:58

I have managed to create a VBA method that authenticates to a REST server, it stores the resulting PrivateHash, SessionID etc in global vars:

Option Explicit

  Dim gSessionIDasHex As String
  Dim gPrivateSaltHash As Long
  Dim gPreviousTix As Long

'Format number as a 4 Byte hex
Private Function To4ByteHex(aNr As Long) As String

  Dim AsHex As String
  
  AsHex = Hex(aNr)
  To4ByteHex = Right("00000000" + AsHex, 8)
End Function

Private Function Authenticate(ByVal aServer, aModel As String, ByVal aUsername, aPassword As String) As Boolean

  Dim MyRequest As WinHttpRequest
  Dim Response As String
  Dim ServerNonce As String
  Dim PasswordHashHexa As String
  Dim SessionPassWordSha256 As String
  Dim ClientNonce As String
  Dim s, i As Integer
  
  Set MyRequest = New WinHttpRequest
    
  gSessionID = 0
  gSessionIDasHex = ""
  gPrivateSaltHash = 0
  
  'First request ServerNonce for username
  MyRequest.Open "GET", aServer + "/" + aModel + "/Auth?username=" + aUsername
  MyRequest.Send
  If MyRequest.Status <> 200 Then
    Debug.Print MyRequest.ResponseText
    Authenticate = False
  Else
    Response = MyRequest.ResponseText
  
    'Authenticate and create session
    ServerNonce = Mid(Response, InStr(Response, ":") + 2, 64)
    ClientNonce = SHA256("TestingCLient")
    PasswordHashHexa = SHA256("salt" + aPassword)
    SessionPassWordSha256 = SHA256(aModel + ServerNonce + ClientNonce + aUsername + PasswordHashHexa)
  
    MyRequest.Open "GET", aServer + "/" + aModel + "/auth?userName=" + aUsername + "&PassWord=" + SessionPassWordSha256 + "&ClientNonce=" + ClientNonce
    MyRequest.Send
    If MyRequest.Status <> 200 Then
      Debug.Print MyRequest.ResponseText
      Authenticate = False
    Else
      s = InStr(MyRequest.ResponseText, ":")
      i = InStr(MyRequest.ResponseText, ",")
      
      'gPrivateSalt is "<SessionID>+<PrivateKey>"
      gPrivateSalt = Mid(MyRequest.ResponseText, s + 2, i - s - 3)
      i = InStr(gPrivateSalt, "+")
      gSessionID = Val(Left(gPrivateSalt, i - 1))
      gSessionIDasHex = To4ByteHex(gSessionID)
      
      gPrivateSaltHash = AddCrc32(PasswordHashHexa, AddCrc32(gPrivateSalt))
      
      Authenticate = True
    End If
  End If
    
End Function

After authenticating, yu can use the folliwng sub to append the needed signature to the url

'Construct the signature to add to the url,
Public Function SignUrl(Url As String) As String
  SignUrl = SignUrlEx(Url, gSessionIDasHex, gPrivateSaltHash)
End Function

Public Function SignUrlEx(ByVal Url As String, ByVal SessionIDAsHex As String, ByVal PrivateSaltHash As Long) As String

Dim Sig As String
Dim Tix As Long
Dim Timestamp As String

  Sig = "session_signature=" + SessionIDAsHex
  Tix = Timer * 100
  
  '//Must be larger then prvious request
  If Tix <= gPreviousTix Then
    Tix = gPreviousTix + 1
  End If
  gPreviousTix = Tix
  
  Timestamp = To4ByteHex(Tix)
  Sig = Sig + Timestamp
  
  Sig = Sig + To4ByteHex(GetCrc32(AddCrc32(Url, AddCrc32(Timestamp, PrivateSaltHash))))
    
  If InStr(Url, "?") > 0 Then
    SignUrlEx = Url + "&" + Sig
  Else
    SignUrlEx = Url + "?" + Sig
  End If
  
End Function

So you can test this with the following sub:

Public Sub TestUrl()
  Dim MyRequest As WinHttpRequest
  Dim Response As String
  Dim Server As String
  Dim Model As String
  Dim Url As String
   
  Set MyRequest = New WinHttpRequest
  
  Server = "http://localhost:8080"
  Model = "root"
  
  If Authenticate(Server, Model, "User", "synopse") Then
  
    Url = Model + "/SampleRecord"
    MyRequest.Open "Get", Server + "/" + SignUrl(Url)
    MyRequest.Send
    Debug.Print MyRequest.ResponseText
    
    Url = Model + "/SampleRecord/2"
    MyRequest.Open "Get", Server + "/" + SignUrl(Url)
    MyRequest.Send
    Debug.Print MyRequest.ResponseText
  
  End If
End Sub

If you need them I can give you the CRC32 and Sha256 methods too, just let me know.

Last edited by Bascy (2014-01-22 08:05:47)

ab · 2014-01-21 21:40:58

Very nice!

Thanks for sharing!

warleyalex · 2014-01-23 20:21:46

Authentication is working fine in Excel 2003, but it fails on URISignature.

http://localhost:8080/root/auth?userName=User&PassWord=ee28af002f8437aac81c54e94538da108c97259664529e85254aa3786c3cf433&ClientNonce=355ba2d3734e098b7097528a2d9176481f8c6178365266342b385f8c6fe0d87d

{"result":"76578+18debb62e13f34a7092833bb7295168da820146fca75a6af76cc9177104c787d","logonname":"User"}

... As you can see, I have an issue with URISignature, this is probably my CRC32.

http://localhost:8080/root/SampleRecord?session_signature=0000004F0063168F
{"ErrorCode":403, "ErrorText":"Forbidden"}

?? gPrivateSaltHash = AddCrc32(PasswordHashHexa, AddCrc32(gPrivateSalt))

... I was looking for the CRC32 in VBA to match mORMot scheme but i couldn't find any usefull thing yet. I just implement something similar

Private malTable(256) As Long
Private Function AddCrc32(ByVal sItem As String, ByVal lCRC32 As Long) As Long

Dim yCharValue As Byte
Dim lCounter As Long
Dim lIndex As Long
Dim lAccValue As Long
Dim lTableValue As Long

  malTable(0) = &H0
  malTable(1) = &H77073096
  malTable(2) = &HEE0E612C
  malTable(3) = &H990951BA
  malTable(4) = &H76DC419
  malTable(5) = &H706AF48F
  malTable(6) = &HE963A535
  malTable(7) = &H9E6495A3
  malTable(8) = &HEDB8832
  malTable(9) = &H79DCB8A4
  malTable(10) = &HE0D5E91E
  malTable(11) = &H97D2D988
  malTable(12) = &H9B64C2B
  malTable(13) = &H7EB17CBD
  malTable(14) = &HE7B82D07
  malTable(15) = &H90BF1D91
  malTable(16) = &H1DB71064
  malTable(17) = &H6AB020F2
  malTable(18) = &HF3B97148
  malTable(19) = &H84BE41DE
  malTable(20) = &H1ADAD47D
  malTable(21) = &H6DDDE4EB
  malTable(22) = &HF4D4B551
  malTable(23) = &H83D385C7
  malTable(24) = &H136C9856
  malTable(25) = &H646BA8C0
  malTable(26) = &HFD62F97A
  malTable(27) = &H8A65C9EC
  malTable(28) = &H14015C4F
  malTable(29) = &H63066CD9
  malTable(30) = &HFA0F3D63
  malTable(31) = &H8D080DF5
  malTable(32) = &H3B6E20C8
  malTable(33) = &H4C69105E
  malTable(34) = &HD56041E4
  malTable(35) = &HA2677172
  malTable(36) = &H3C03E4D1
  malTable(37) = &H4B04D447
  malTable(38) = &HD20D85FD
  malTable(39) = &HA50AB56B
  malTable(40) = &H35B5A8FA
  malTable(41) = &H42B2986C
  malTable(42) = &HDBBBC9D6
  malTable(43) = &HACBCF940
  malTable(44) = &H32D86CE3
  malTable(45) = &H45DF5C75
  malTable(46) = &HDCD60DCF
  malTable(47) = &HABD13D59
  malTable(48) = &H26D930AC
  malTable(49) = &H51DE003A
  malTable(50) = &HC8D75180
  malTable(51) = &HBFD06116
  malTable(52) = &H21B4F4B5
  malTable(53) = &H56B3C423
  malTable(54) = &HCFBA9599
  malTable(55) = &HB8BDA50F
  malTable(56) = &H2802B89E
  malTable(57) = &H5F058808
  malTable(58) = &HC60CD9B2
  malTable(59) = &HB10BE924
  malTable(60) = &H2F6F7C87
  malTable(61) = &H58684C11
  malTable(62) = &HC1611DAB
  malTable(63) = &HB6662D3D
  malTable(64) = &H76DC4190
  malTable(65) = &H1DB7106
  malTable(66) = &H98D220BC
  malTable(67) = &HEFD5102A
  malTable(68) = &H71B18589
  malTable(69) = &H6B6B51F
  malTable(70) = &H9FBFE4A5
  malTable(71) = &HE8B8D433
  malTable(72) = &H7807C9A2
  malTable(73) = &HF00F934
  malTable(74) = &H9609A88E
  malTable(75) = &HE10E9818
  malTable(76) = &H7F6A0DBB
  malTable(77) = &H86D3D2D
  malTable(78) = &H91646C97
  malTable(79) = &HE6635C01
  malTable(80) = &H6B6B51F4
  malTable(81) = &H1C6C6162
  malTable(82) = &H856530D8
  malTable(83) = &HF262004E
  malTable(84) = &H6C0695ED
  malTable(85) = &H1B01A57B
  malTable(86) = &H8208F4C1
  malTable(87) = &HF50FC457
  malTable(88) = &H65B0D9C6
  malTable(89) = &H12B7E950
  malTable(90) = &H8BBEB8EA
  malTable(91) = &HFCB9887C
  malTable(92) = &H62DD1DDF
  malTable(93) = &H15DA2D49
  malTable(94) = &H8CD37CF3
  malTable(95) = &HFBD44C65
  malTable(96) = &H4DB26158
  malTable(97) = &H3AB551CE
  malTable(98) = &HA3BC0074
  malTable(99) = &HD4BB30E2
  malTable(100) = &H4ADFA541
  malTable(101) = &H3DD895D7
  malTable(102) = &HA4D1C46D
  malTable(103) = &HD3D6F4FB
  malTable(104) = &H4369E96A
  malTable(105) = &H346ED9FC
  malTable(106) = &HAD678846
  malTable(107) = &HDA60B8D0
  malTable(108) = &H44042D73
  malTable(109) = &H33031DE5
  malTable(110) = &HAA0A4C5F
  malTable(111) = &HDD0D7CC9
  malTable(112) = &H5005713C
  malTable(113) = &H270241AA
  malTable(114) = &HBE0B1010
  malTable(115) = &HC90C2086
  malTable(116) = &H5768B525
  malTable(117) = &H206F85B3
  malTable(118) = &HB966D409
  malTable(119) = &HCE61E49F
  malTable(120) = &H5EDEF90E
  malTable(121) = &H29D9C998
  malTable(122) = &HB0D09822
  malTable(123) = &HC7D7A8B4
  malTable(124) = &H59B33D17
  malTable(125) = &H2EB40D81
  malTable(126) = &HB7BD5C3B
  malTable(127) = &HC0BA6CAD
  malTable(128) = &HEDB88320
  malTable(129) = &H9ABFB3B6
  malTable(130) = &H3B6E20C
  malTable(131) = &H74B1D29A
  malTable(132) = &HEAD54739
  malTable(133) = &H9DD277AF
  malTable(134) = &H4DB2615
  malTable(135) = &H73DC1683
  malTable(136) = &HE3630B12
  malTable(137) = &H94643B84
  malTable(138) = &HD6D6A3E
  malTable(139) = &H7A6A5AA8
  malTable(140) = &HE40ECF0B
  malTable(141) = &H9309FF9D
  malTable(142) = &HA00AE27
  malTable(143) = &H7D079EB1
  malTable(144) = &HF00F9344
  malTable(145) = &H8708A3D2
  malTable(146) = &H1E01F268
  malTable(147) = &H6906C2FE
  malTable(148) = &HF762575D
  malTable(149) = &H806567CB
  malTable(150) = &H196C3671
  malTable(151) = &H6E6B06E7
  malTable(152) = &HFED41B76
  malTable(153) = &H89D32BE0
  malTable(154) = &H10DA7A5A
  malTable(155) = &H67DD4ACC
  malTable(156) = &HF9B9DF6F
  malTable(157) = &H8EBEEFF9
  malTable(158) = &H17B7BE43
  malTable(159) = &H60B08ED5
  malTable(160) = &HD6D6A3E8
  malTable(161) = &HA1D1937E
  malTable(162) = &H38D8C2C4
  malTable(163) = &H4FDFF252
  malTable(164) = &HD1BB67F1
  malTable(165) = &HA6BC5767
  malTable(166) = &H3FB506DD
  malTable(167) = &H48B2364B
  malTable(168) = &HD80D2BDA
  malTable(169) = &HAF0A1B4C
  malTable(170) = &H36034AF6
  malTable(171) = &H41047A60
  malTable(172) = &HDF60EFC3
  malTable(173) = &HA867DF55
  malTable(174) = &H316E8EEF
  malTable(175) = &H4669BE79
  malTable(176) = &HCB61B38C
  malTable(177) = &HBC66831A
  malTable(178) = &H256FD2A0
  malTable(179) = &H5268E236
  malTable(180) = &HCC0C7795
  malTable(181) = &HBB0B4703
  malTable(182) = &H220216B9
  malTable(183) = &H5505262F
  malTable(184) = &HC5BA3BBE
  malTable(185) = &HB2BD0B28
  malTable(186) = &H2BB45A92
  malTable(187) = &H5CB36A04
  malTable(188) = &HC2D7FFA7
  malTable(189) = &HB5D0CF31
  malTable(190) = &H2CD99E8B
  malTable(191) = &H5BDEAE1D
  malTable(192) = &H9B64C2B0
  malTable(193) = &HEC63F226
  malTable(194) = &H756AA39C
  malTable(195) = &H26D930A
  malTable(196) = &H9C0906A9
  malTable(197) = &HEB0E363F
  malTable(198) = &H72076785
  malTable(199) = &H5005713
  malTable(200) = &H95BF4A82
  malTable(201) = &HE2B87A14
  malTable(202) = &H7BB12BAE
  malTable(203) = &HCB61B38
  malTable(204) = &H92D28E9B
  malTable(205) = &HE5D5BE0D
  malTable(206) = &H7CDCEFB7
  malTable(207) = &HBDBDF21
  malTable(208) = &H86D3D2D4
  malTable(209) = &HF1D4E242
  malTable(210) = &H68DDB3F8
  malTable(211) = &H1FDA836E
  malTable(212) = &H81BE16CD
  malTable(213) = &HF6B9265B
  malTable(214) = &H6FB077E1
  malTable(215) = &H18B74777
  malTable(216) = &H88085AE6
  malTable(217) = &HFF0F6A70
  malTable(218) = &H66063BCA
  malTable(219) = &H11010B5C
  malTable(220) = &H8F659EFF
  malTable(221) = &HF862AE69
  malTable(222) = &H616BFFD3
  malTable(223) = &H166CCF45
  malTable(224) = &HA00AE278
  malTable(225) = &HD70DD2EE
  malTable(226) = &H4E048354
  malTable(227) = &H3903B3C2
  malTable(228) = &HA7672661
  malTable(229) = &HD06016F7
  malTable(230) = &H4969474D
  malTable(231) = &H3E6E77DB
  malTable(232) = &HAED16A4A
  malTable(233) = &HD9D65ADC
  malTable(234) = &H40DF0B66
  malTable(235) = &H37D83BF0
  malTable(236) = &HA9BCAE53
  malTable(237) = &HDEBB9EC5
  malTable(238) = &H47B2CF7F
  malTable(239) = &H30B5FFE9
  malTable(240) = &HBDBDF21C
  malTable(241) = &HCABAC28A
  malTable(242) = &H53B39330
  malTable(243) = &H24B4A3A6
  malTable(244) = &HBAD03605
  malTable(245) = &HCDD70693
  malTable(246) = &H54DE5729
  malTable(247) = &H23D967BF
  malTable(248) = &HB3667A2E
  malTable(249) = &HC4614AB8
  malTable(250) = &H5D681B02
  malTable(251) = &H2A6F2B94
  malTable(252) = &HB40BBE37
  malTable(253) = &HC30C8EA1
  malTable(254) = &H5A05DF1B
  malTable(255) = &H2D02EF8D


For lCounter = 1 To Len(sItem)
yCharValue = Asc(Mid$(sItem, lCounter, 1))
lAccValue = lCRC32 And &HFFFFFF00
lAccValue = lAccValue \ &H100
lAccValue = lAccValue And &HFFFFFF
lIndex = lCRC32 And &HFF
lIndex = lIndex Xor yCharValue
lTableValue = malTable(lIndex)
lCRC32 = lAccValue Xor lTableValue
Next lCounter

AddCrc32 = lCRC32

End Function

Public Function ComputeCRC32(ByRef sString As String) As String

Dim lCrc32Value As Long

lCrc32Value = InitCrcc32()
lCrc32Value = AddCrc32(sString, lCrc32Value)

ComputeCRC32 = Hex$(GetCrc32(lCrc32Value))

End Function

Private Function GetCrc32(ByVal lCRC32 As Long) As Long
GetCrc32 = lCRC32 Xor &HFFFFFFFF
End Function

Private Function InitCrc32(Optional ByVal Seed As Long = &HEDB88320, Optional ByVal Precondition As Long = &HFFFFFFFF) As Long

Dim iBytes As Integer
Dim iBits As Integer
Dim lCRC32 As Long
Dim lTempCrc32 As Long

For iBytes = 0 To 255

lCRC32 = iBytes

For iBits = 0 To 7
lTempCrc32 = lCRC32 And &HFFFFFFFE
lTempCrc32 = lTempCrc32 \ &H2
lTempCrc32 = lTempCrc32 And &H7FFFFFFF

If (lCRC32 And &H1) <> 0 Then
lCRC32 = lTempCrc32 Xor Seed
Else
lCRC32 = lTempCrc32
End If
Next iBits

malTable(iBytes) = lCRC32
Next iBytes

InitCrc32 = Precondition

End Function

Would you mind to share CRC32 module?

ab · 2014-01-23 20:33:45

Why not use a weaker authentication scheme on mORMot side?
So you just need authentication, without the URI signature.
Could be enough for your purpose.

Bascy · 2014-01-24 08:17:04

Alex, if you send me your email address, i'll mail the units for CRC32 and SHA256 to you

warleyalex · 2014-01-24 14:46:13

Suddenly, everything started to work properly. I realized that my CRC32 code is actually works.

Finally, this authentication and URISignature for VBA is working just fine with mORMot.

I think VBA's performance is very respectable in Excel 2003; It is partly compiled rather than purely interpreted as some people think.

Another idea is to use Add-in Express component for Delphi to develop COM-Addins for Excel. This must be easy to integrate mORMot. A COM Add-in will run considerably faster than VBA and will offer increased security in terms of people breaking into / reverse engineering your work in any way.

Thank's for sharing!

ab · 2014-01-24 15:13:57

warleyalex wrote:

Thank's for sharing!

Thanks to you!

mORMot Open Source

#1 2014-01-17 09:40:11

Accessing all REST methods with an ApiKey

#2 2014-01-17 17:41:25

Re: Accessing all REST methods with an ApiKey

#3 2014-01-20 11:31:12

Re: Accessing all REST methods with an ApiKey

#4 2014-01-20 12:12:31

Re: Accessing all REST methods with an ApiKey

#5 2014-01-21 11:35:58

Re: Accessing all REST methods with an ApiKey

#6 2014-01-21 21:40:58

Re: Accessing all REST methods with an ApiKey

#7 2014-01-23 20:21:46

Re: Accessing all REST methods with an ApiKey

#8 2014-01-23 20:33:45

Re: Accessing all REST methods with an ApiKey

#9 2014-01-24 08:17:04

Re: Accessing all REST methods with an ApiKey

#10 2014-01-24 14:46:13

Re: Accessing all REST methods with an ApiKey

#11 2014-01-24 15:13:57

Re: Accessing all REST methods with an ApiKey

Board footer