I was quite miffed that Malwarebytes identified my code as a virus. I had some Excel VBA macros that downloaded files from sharepoint in a fractoin of the time compared to opening them as a file from SharePoint. Brilliant … until “MalwareBytes just saved your life by killing Excel for you before you were doomed forever” … but … but hey – my program just poof … gone. Changes, long macro (that during VPN/Covid days) takes forever and now … gone. After some debugging it was the line URLDownloadToFile that paused Excel for about 4 minutes before then caused MalwareBytes to kill it. I did put in a ticket (and expect it to never be addressed) but suggested that if a file is in the trusted sites list – perhaps don’t view that as an exploit. It is likely faster to re-program things.

Suggestions online only have 3 options to download from SharePoint.

• map a drive, use the drive (and optionally kill the drive mapping). This seems lame. Plus you are opening a file using Explorer type access – which I said was slow
• Use URLDownloadToFile() which – EVERYONE IS USING – that is why it is likely why people who make viruses use it – it seems to be quite popular with Virus developers
• REST – way to complicated – no one has done it without asking for usernames and passwords everytime (I did figure it out – and this is how I am doing it now)

Alas – I DID figure out the bits and pieces to NOT as for credentials and to camp-out on an existing connection to SharePoint. I am doing this from an Excel VBA macro – so we are 99% guaranteed that we are connected. Complicated – you have no idea – YES IT IS.

Here is my journey:

• “URLDownloadToFile” malwarebytes memory heap exploit – Google search as to how MalwareBytes saw my UrlDownloadToFile() command
• TODO: Fill these links in – I seemed to have closed all my browser windows for this topic so I will have to regain them. I have a few
  • This guy has most of the code where I started – in specific Jefferson Gomes‘ response on Jan 14, 2019. However as he admits – it needs some work and his main goal was to get a SPOIDCR cookie – this is used INSTEAD OF CREDENTIALS. I
  • If you read the code, to figure out your STS Url – I had to search more. This code helped immensely – there were others but they did NOT seem simple nor were they helpful. So go to the code in the link and only run the Get-UserRealmUrl() and then you can put a stop statement.
  • Then I added a bunch of other code so it flows nicely and tested it with a file download REST endpoint. I already had one, but for the reader – use this code and replace the credentials part with your cookie header starting with SPOIDCR: and ending with the end of the cookie token itself. See down below for the GET code in VBA

TS URL

• Then I cached the Cookie and the Cookie Timestamp in the users’ registry so i would not have to do the 3 REST operations each time to get the current cookie.
  • The cookie seems to last for an hour – so i react at 55 minutes. So to get your current time in VBA in the YYYY-DD-MMTHH:mm:ssZ format you need do some jiggery pokery to get the current-time-plus-5-minutes and the cookie time into a VBA Date variable then compare and either get a new cookie or use a cached one. For that I had to
    • figure out that format above is one of ISO 8601 Date formats. Regex seems to be the way to get it and THIS website’s regex (actually from this stackoverflow) is NOT PERFECT AND IS NOT the answer for the format above but its close. My Regex is closer – but i didn’t perfect it – it is good enough except the milliseconds part – that needs the work still (but I didn’t need it).
      • ^([\+-]?\d{4}(?!\d{2}\b))((-?)((0[1-9]|1[0-2])(\3([12]\d|0[1-9]|3[01]))?|W([0-4]\d|5[0-2])(-?[1-7])?|(00[1-9]|0[1-9]\d|[12]\d{2}|3([0-5]\d|6[1-6])))([T\s]((([01]\d|2[0-3])((:?)([0-5]\d))?|24\:?00)([\.,]\d+(?!:))?)?(\17[0-5]\d([\.,]\d+)?)?([zZ]|([\+-])([01]\d|2[0-3]):?([0-5]\d)?)?)?)?$
  • One thing I have NOT done and need to is that if you come back lets say 50 minutes later and try your cookie and the user explicitly logged off an active session – what the program might do. I would LIKE it to open up a window and prompt for a login and carry on.

The GET code for the SharePoint REST endpoint WITH THE COOKIE would look like

'VBA Code
' note the cookkie variable would contain something like 
' SPOIDCR: dhriso8&238dkk874kkr0P...UUIOEIUAjdsoei (shortened for readability)

Set oRequest = New WinHttp.WinHttpRequest
With oRequest
    .Open "GET", sUrl, False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Cookie", cookkie
    .setRequestHeader "X-FORMS_BASED_AUTH_ACCEPTED", "f"

        .send
        .waitForResponse
        sResult = .responseText

    If oRequest.status = 200 Then
        Dim oStream         As Object: Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write oRequest.responseBody
        destFinalPath = fso.BuildPath(destFolder, destName)
        oStream.SaveToFile destFinalPath, Abs(CInt(overwrite)) + 1
        oStream.Close
        DownloadFileB = Len(dir(destFinalPath)) > 0

        DownloadSpFileViaREST = destFinalPath

        Exit Function
    Else
        Debug.Print .responseText
    End If

    Debug.Print PrettyPrintXML(sResult)
    sResult = oRequest.status
    Debug.Print sResult
End With

Note there is no Bearer Token: header etc. like the MS article would include – that is now just the cookie header.