vba - 不理解为什么 WinHTTP 不验证某些 HTTPS 资源

Question

如果能帮助我解决问题，我将不胜感激。

从 Excel VBA 代码我需要从 HTTPS 站点下载并解析 CSV 文件 https://redmine.itransition.com/ .我尝试使用 WinHTTP 来获取文件。但是，我不明白为什么身份验证不起作用。这是一段相关的代码:

TargetURL = "https://redmine.itransition.com/projects/pmct/time_entries.csv"
Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
HTTPReq.Option(4) = 13056 ' WinHttpRequestOption_SslErrorIgnoreFlags 13056: ignore all err, 0: accept no err
HTTPReq.Open "GET", TargetURL, False
HTTPReq.SetCredentials "UN", "PW", 0
HTTPReq.send

返回以下响应(仅列出某些字符串):

Content-Type: text/html; charset=utf-8
Status: 406
X-Runtime: 5

但是，如果我在成功使用手动身份验证后从 Firefox cookie 发送“Cookie”字符串

HTTPReq.setRequestHeader "Cookie", SetCookieString
HTTPReq.send

我很容易得到预期的文件。当然，我对这样的解决方案并不满意，并希望执行真正的 WinHTTP 身份验证。但是，我不明白代码中出了什么问题或遗漏了什么。我很可能必须使用 .SetClientCertificate 方法，但这对我来说还不清楚 - 需要哪个证书？

或者，更笼统地说:我应该使用哪些 WinHTTP 方法或函数进行调试，以找出哪个步骤被阻止/不正确并阻止我进行正确的身份验证？我花了2周的时间在MSDN和各种资源中寻找，但仍然没有解决方案。

提前感谢您的建议!

Answer 1

上面的@Alex K. 回复正是我期待已久的内容!在 Firebug 和 MSDN 的帮助下，我完成了 3 个请求:

• GET 请求使用 RegEx 从登录页面收集 authenticity_token 数据
• POST 请求以验证并从响应中收集所需的 Cookie 字符串
• GET 请求最终获得我心爱的 CSV

以下代码按预期工作:

Set RegX_AuthToken = CreateObject("VBScript.RegExp")
' Below Pattern w/o double-quotes encoded: (?:input name="authenticity_token" type="hidden" value=")(.*)(?:")
RegX_AuthToken.Pattern = "(?:input name=" & Chr(34) & "authenticity_token" & Chr(34) & " type=" & Chr(34) & "hidden" & Chr(34) & " value=" & Chr(34) & ")(.*)(?:" & Chr(34) & ")"
RegX_AuthToken.IgnoreCase = True
RegX_AuthToken.Global = True

TargetURL = "https://redmine.itransition.com/login"

Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
HTTPReq.Open "GET", TargetURL, False
HTTPReq.Send

Set Token_Match = RegX_AuthToken.Execute(HTTPReq.ResponseText)
AuthToken = Token_Match.Item(0).SubMatches.Item(0)

PostData = "authenticity_token=" & AuthToken & "&back_url=https://redmine.itransition.com/" & "&username=" & UN & "&password=" & PW & "&login=Login »"

HTTPReq.Open "POST", TargetURL, False
HTTPReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
HTTPReq.Send (PostData)

SetCookieString = HTTPReq.GetResponseHeader("Set-Cookie")

TargetURL = "https://redmine.itransition.com/projects/pmct/time_entries.csv"
HTTPReq.Open "GET", TargetURL, False
HTTPReq.setRequestHeader "Cookie", SetCookieString
HTTPReq.Send

以下 URL 有助于构建 POST 请求:http://tkang.blogspot.com/2010/09/sending-http-post-request-with-vba.html

You need to load that page with no credentials, grab what looks like the volatile field authenticity_token from the generated form & post that along with username & password to /login.

Alex K. - 再次感谢您的绝妙建议! (: