excel - VBA WinHTTP 登录到 Oddsportal

Question

我正在尝试通过 VBA 脚本登录到oddsportal。我已经编写了以下代码，但它不起作用。当我为 POST 请求设置 cookie 时出现错误。如果有人能指出我正确的方向，我将不胜感激。

Dim WHTTP As Object
Dim myuser, mypass, url, strAuthenticate As String
Dim out As String

url = "http://www.oddsportal.com/"

myuser = "user"
mypass = "pass"
strAuthenticate = "login-username=" & myuser & "&login-password=" & mypass & "&login-submit="

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "POST", url, False
WHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.114 Safari/537.36"
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.setRequestHeader "Connection", "keep-alive"
WHTTP.send strAuthenticate
strCookie = WHTTP.getResponseHeader("Set-Cookie")  **'I am getting an error here**
strResponse = WHTTP.ResponseText


WHTTP.Open "GET", "http://www.oddsportal.com/soccer/africa/africa-cup-of-nations/results/", False
WHTTP.setRequestHeader "Connection", "keep-alive"
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.114 Safari/537.36"
WHTTP.setRequestHeader "Cookie", strCookie
WHTTP.send

out = WHTTP.ResponseText

我使用 setcredential 方法将身份验证方法更改为基于服务器。现在我可以从第一部分设置 cookie。但是，我仍然无法从第二页(代码的第二部分)获取数据。以下是我使用 setcredentials 获得的响应 header : 我设置的 cookie 行只能保留最后一个 cookie。

Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
   Connection: keep-alive
   Date: Tue, 27 May 2014 16:01:29 GMT
   Pragma: no-cache
   Transfer-Encoding: chunked
   Content-Type: text/html
   Expires: Thu, 19 Nov 1981 08:52:00 GMT
   Age: 0
   Server: Apache
   Set-Cookie: op_lang=deleted; expires=Thu, 01-Jan-1970 00:00:01 GMT; path=/
   Set-Cookie: op_oddsportal=fehof62734n35crjd9vpshhf10; path=/
   Set-Cookie: op_cookie-test=ok; expires=Wed, 27-May-2015 16:01:28 GMT; path=/
   Set-Cookie: op_state=1
   Set-Cookie: op_last_id=1; expires=Thu, 26-Jun-2014 16:01:28 GMT; path=/
   Set-Cookie: op_cookie-test=ok; expires=Wed, 27-May-2015 16:01:28 GMT; path=/
   Vary: Accept-Encoding
   X-Deliver: Tue, 27 May 2014 16:01:29 GMT

这是我从 Fiddler 那里得到的 cookie:

Set-Cookie:op_user_logout=0; expires=Mon, 18-May-2015 18:44:49 GMT; path=/
Set-Cookie:op_last_id=1; expires=Thu, 26-Jun-2014 18:44:49 GMT; path=/
Set-Cookie:op_user_login_id=95774; expires=Mon, 18-May-2015 18:44:49 GMT; path=/
Set-Cookie:op_user_login_hash=73a967ad18d6a353afa12877309f4708; expires=Mon, 18-May-2015 18:44:49 GMT; path=/
Set-Cookie:op_cookie-test=ok; expires=Wed, 27-May-2015 18:44:48 GMT; path=/
Set-Cookie:op_user_time_zone=1.00; expires=Thu, 26-Jun-2014 18:44:49 GMT; path=/
Set-Cookie:op_user_full_time_zone=35; expires=Thu, 26-Jun-2014 18:44:49 GMT; path=/
Set-Cookie:op_lang=deleted; expires=Thu, 01-Jan-1970 00:00:01 GMT; path=/

Answer 1

我不确定它在这种特定情况下是否会有所帮助，但请求和响应 cookie 处理包含在我的库中 Excel-REST .下面是获取和设置登录 ID 和哈希的示例:

Dim OddsClient As New RestClient
OddsClient.BaseUrl = "http://www.oddsportal.com/"

Dim LoginRequest As New RestRequest
LoginRequest.Method = httpPOST
LoginRequest.Format = formurlencoded
LoginRequest.AddParameter "login-username", myuser
LoginRequest.AddParameter "login-password", mypass
LoginRequest.AddParameter "login-submit", ""

Dim LoginResponse As RestResponse
Set LoginResponse = OddsClient.Execute(LoginRequest)

If LoginResponse.StatusCode = 200 Then
  Dim OddsRequest As New RestRequest
  OddsRequest.Resource = "soccer/africa/africa-cup-of-nations/results/"
  OddsRequest.AddCookie "op_user_login_id", LoginResponse.Cookies("op_user_login_id")
  OddsRequest.AddCookie "op_user_login_hash", LoginResponse.Cookies("op_user_login_hash")

  Dim OddsResponse As RestResponse
  Set OddsResponse = OddsClient.Execute(OddsRequest)
End If

我在从 header 中检索 cookie 时遇到了一些困难，因为每个 cookie 都有相同的 header 键“Set-Cookie”。在 Excel-REST 中，我拆分了 getAllResponseHeaders变成 Collection键值标题以便于访问，然后使用“Set-Cookie”键取出那些(参见 RestHelpers)。对于您的示例，类似于以下内容的内容可能会起作用:

Dim Headers As Collection ' of Dictionaries with key, value
Set Headers = ExtractHeadersFromResponseHeaders(WHTTP.getAllResponseHeaders) ' from RestHelpers

' Setup GET...

Dim Header As Dictionary
For Each Header In Headers
    ' Move all cookies from login to GET request
    If Header("key") = "Set-Cookie" Then
        WHTTP.setRequestHeader "Cookie", Header("value")
    End If  
Next Header