vba 不明白为什么 WinHTTP 不验证某些 HTTPS 资源
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14184926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Not understanding why WinHTTP does NOT authenticate certain HTTPS resource
提问by Peter L.
I'd be extremely grateful for any kind of help that may help me resolving the problem.
我将非常感谢任何可能帮助我解决问题的帮助。
From Excel VBA code I need to download & parse CSV file from HTTPS site https://redmine.itransition.com/. I try to use WinHTTP to get the file. However, I can't understand why authentication does not work. Here is the piece of related code:
从 Excel VBA 代码，我需要从 HTTPS 站点https://redmine.itransition.com/下载和解析 CSV 文件。我尝试使用 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
returns the following response (only certain strings are listed):
返回以下响应（仅列出某些字符串）：
Content-Type: text/html; charset=utf-8
Status: 406
X-Runtime: 5
However, if I send "Cookie" string from Firefox cookie after successful manual authentication using
但是，如果我在使用手动身份验证成功后从 Firefox cookie 发送“Cookie”字符串
HTTPReq.setRequestHeader "Cookie", SetCookieString
HTTPReq.send
I easily get the expected file. Of course I'm not happy with such solution, and want to perform true WinHTTP authentication. However, I can't understand what's wrong or what I miss in my code. Most likely I have to use .SetClientCertificatemethod, but this is unclear for me - which cert is required?
我很容易得到预期的文件。当然，我对这样的解决方案并不满意，并希望执行真正的 WinHTTP 身份验证。但是，我无法理解代码中出了什么问题或遗漏了什么。很可能我必须使用.SetClientCertificate方法，但这对我来说还不清楚 - 需要哪个证书？
Or, being more general: which WinHTTP methods or functions I should use for debugging to find out which step is blocking / incorrect and prevents me from correct authentication? I spent 2 weeks seeking through MSDN and various resources, but still have no solution.
或者，更笼统地说：我应该使用哪些 WinHTTP 方法或函数进行调试，以找出哪个步骤阻塞/不正确并阻止我进行正确的身份验证？我花了 2 周时间通过 MSDN 和各种资源寻找，但仍然没有解决方案。
Thanks in advance for your suggestions!
预先感谢您的建议！
回答by Peter L.
The above @Alex K. response was exactly what I was looking for soooo long! With the help of Firebug and MSDN I finished with 3 requests:
上面的@Alex K. 回复正是我一直在寻找的！在 Firebug 和 MSDN 的帮助下，我完成了 3 个请求：
- GET request to collect authenticity_token data from login page using RegEx
- POST request to authenticate & collect required Cookie string from response
- GET request to finally obtain my beloved CSV
- 使用 RegEx 从登录页面收集authenticity_token 数据的GET 请求
- POST 请求验证并从响应中收集所需的 Cookie 字符串
- GET请求最终获得我心爱的CSV
The following piece of code which is working as expected:
以下代码按预期工作：
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
The following URL was helpful in building POST request: http://tkang.blogspot.com/2010/09/sending-http-post-request-with-vba.html
以下 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.
您需要在没有凭据的情况下加载该页面，从生成的表单中获取看起来像可变字段authenticity_token 的内容，并将其连同用户名和密码一起发布到 /login。
Alex K. - thanks again for the brilliant suggestion! (:
Alex K. - 再次感谢您的绝妙建议！(:
回答by Alex K.
The logon at https://redmine.itransition.com/is just an HTML form that posts a username & password to a script at /login.
https://redmine.itransition.com/上的登录只是一个 HTML 表单，将用户名和密码发布到/login.
This is not compatible with SetCredentialswhich is designed for server based authentication schemes like basic/digest/ntlm.
这与SetCredentials专为基于服务器的身份验证方案（如 basic/digest/ntlm）设计的不兼容。
You need to load that page with no credentials, grab what looks like the volatile field authenticity_tokenfrom the generated form & post that along with username & password to /login.
您需要在没有凭据的情况下加载该页面，authenticity_token从生成的表单中获取看起来像 volatile 字段的内容，并将其与用户名和密码一起发布到/login.
If its a session based system it will response with the set-cookie header + data you need to use in subsequent request.
如果它是基于会话的系统，它将使用 set-cookie 标头 + 您需要在后续请求中使用的数据进行响应。
回答by Peter L.
And one more thing (in addition to the above solution) one should be aware of in case of using POST requests similar to the above code: for some obscure reason I still got once in 4-5 times (or even more) the hated 406 response from the website, which means in my case auth is NOT complete. After hours of step-by-step debugging I happily caught the cause: auth token value may have +signs, and from analyzing an arrow of several dozens auth tokens / response codes I discovered that +-containing tokens exactly match 406 codes.
还有一件事（除了上述解决方案）在使用类似于上述代码的 POST 请求的情况下应该注意：出于某种晦涩的原因，我仍然有 4-5 次（甚至更多）讨厌的 406来自网站的响应，这意味着在我的情况下auth is NOT complete。经过数小时的逐步调试，我很高兴地找到了原因：身份验证令牌值可能有+符号，通过分析几十个身份验证令牌/响应代码的箭头，我发现 -包含+令牌与 406 代码完全匹配。
The solution became pretty obvious: safely URL-encode +es for the PostData. With the help of http://www.blooberry.com/indexdot/html/topics/urlencoding.htmI finally came up to the following:
解决方案变得非常明显：安全地+为PostData. 在http://www.blooberry.com/indexdot/html/topics/urlencoding.htm的帮助下，我终于想到了以下内容：
PostData = "authenticity_token=" & Replace(AuthToken, "+", "%2B", vbTextCompare) & _
"&back_url=https://redmine.itransition.com/projects/" & Trim(RedmineProject) & _
"/time_entries" & "&username=" & UN & "&password=" & PW & "&login=Login ?"
+es are replaced by %2Bs, and this was that - no more 406s!)
+es 被%2Bs取代，这就是 - 不再是 406s！）
The other special chars do not matter in my case, but the lesson was learned. Hope this will save several hours of life for someone else!
其他特殊字符在我的情况下无关紧要，但吸取了教训。希望这会为其他人节省几个小时的生命！
