如何在VBA中设置和获取JSESSIONID cookie？ [英] How to set and get JSESSIONID cookie in VBA?

问题描述

我正在使用MSXML2.XMLHTTP60在Excel 2010中为在Tomcat 8.5.5上托管的Java REST Web服务编写VBA Web服务客户端。

I'm writing a VBA web service client in Excel 2010 using MSXML2.XMLHTTP60 for my Java REST web services hosted on Tomcat 8.5.5.

在VBA中，我想从响应中捕获字符串 JSESSIONID = E4E7666024C56427645D65BEB49ADC11 并将其设置在后续请求中。

（如果Excel崩溃，则似乎是该cookie丢失，用户必须再次进行身份验证。我想为用户设置上次存储的会话ID，因此，如果服务器上的会话仍处于活动状态，则不必在Excel客户端中重新进行身份验证。）

In VBA, I want to snag the string JSESSIONID=E4E7666024C56427645D65BEB49ADC11 from a response and set it in a subsequent request.
(if Excel crashes, it seems that this cookie is lost and the user has to authenticate again. I want to set the last stored session ID for the user, so if the session is still alive on the server, they don't have to re-authenticate in the Excel client.)

我看到了一些在线资源，根据这些资源以下内容将提取JSESSIONID cookie，但最后一行始终显示为空：

I saw some online resources according to which the following will pull the JSESSIONID cookie, but the last line always prints empty:

Dim httpObj As New MSXML2.XMLHTTP60
With httpObj
    .Open "POST", URL, False
    .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
    .SetRequestHeader "Connection", "keep-alive"
    .Send
End With
Debug.Print "Response header Cookie: " & httpObj.GetResponseHeader("Cookie")  'This should pull the JSESSIONID cookie but is empty

当我打印时 httpObj.GetAllResponseHeaders 我看不到任何包含JSESSIONID的标头。

When I print httpObj.GetAllResponseHeaders I do not see any headers that hold JSESSIONID.

在相同的资源中，应设置以下内容所需的cookie，但没有（我在服务器上打印了传入请求的标头，发现我的尝试未覆盖JSESSIONID值）。

In the same resources, the following should set the desired cookie, but it doesn't (I print out the headers of the incoming request on the server and see that my attempt did not override the JSESSIONID value).

httpObj.SetRequestHeader "Cookie", "JSESSIONID=blahblah"

我可能会缺少JSESSIONED的传输机制，以及VBA如何以及何时将其设置的机制。

I may be missing the mechanism for how JSESSIONED is transmitted, and how and when VBA pulls it and sets it.

推荐答案

发布了一个很棒的解决方案，我想分享最终使用的解决方案。

While omegastripes posted a great solution, I wanted to share the solution I ended up using.

我使用的原始MSXML2.XMLHTTP60对象不支持cookie。因此，我改为使用 WinHttp.WinHttpRequest 。

The original MSXML2.XMLHTTP60 object I used does not support cookies. So instead I used WinHttp.WinHttpRequest.

这需要添加对您的代码的引用：在VBA IDE中，转到工具->引用，并确保 Microsoft WinHTPP.Services版本xxx。 被选中。

This requires adding a reference to your code: In VBA IDE go to Tools-->References and make sure that Microsoft WinHTPP.Services version xxx is selected.

抓取cookie的代码和存储它（假定类型为 WinHttp.WinHttpRequest 的对象 httpObj ）：

Code that grabs the cookie and stores it (assuming an object httpObj of type WinHttp.WinHttpRequest):

' Get the JESSIONID cookie
Dim strCookie As String
Dim jsessionidCookie As String

strCookie = httpObj.GetResponseHeader("Set-Cookie")     ' --> "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly"
jsessionidCookie = GetJsessionIdCookie(strCookie)       ' Strips to  "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E"

'Store JSESSIONID cookie in the cache sheet

GetJsessionIdCookie过程为：

Where the procedure GetJsessionIdCookie is:

' Takes a string of the form "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly"
' and returns only the portion "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E"
Public Function GetJsessionIdCookie(setCookieStr As String) As String
    'JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly

    Dim jsessionidCookie As String

    Dim words() As String
    Dim word As Variant

    words = Split(setCookieStr, ";")
    For Each word In words
        If InStr(1, word, "JSESSIONID") > 0 Then
            jsessionidCookie = word
        End If
    Next word

    GetJsessionIdCookie = jsessionidCookie
End Function

设置cookie：

以下是创建WinHttp.WinHttpRequest对象并设置cookie的方法

Setting the cookie:

Here's the method that creates an WinHttp.WinHttpRequest object and sets the cookie that was previously stored:

Public Function GetHttpObj(httpMethod As String, uri As String, Optional async As Boolean = False, _
    Optional setJessionId As Boolean = True, _
    Optional contentType As String = "application/xml") As WinHttp.WinHttpRequest
    Dim cacheUtils As New CCacheUtils
    Dim httpObj As New WinHttp.WinHttpRequest
    With httpObj
        .Open httpMethod, uri, async
        .SetRequestHeader "origin", "pamsXL"
        .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
        .SetRequestHeader "Connection", "keep-alive"
        .SetRequestHeader "Content-type", contentType
        .SetRequestHeader "cache-control", "no-cache"
    End With

    ' --- Pull stored cookie and attach to request ---
    If setJessionId Then
        httpObj.SetRequestHeader "Cookie", cacheUtils.GetCachedValue(wsJsessionidAddr)
    End If

    Set GetHttpObj = httpObj
End Function

其中 CCacheUtils 是我实现的类用于存储和检索诸如JSESSIONID cookie之类的缓存值。

Where CCacheUtils is a class I implemented for storing and retrieving cached values such as the JSESSIONID cookie.

这篇关于如何在VBA中设置和获取JSESSIONID cookie？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！