Web scraping client certificate issue WinHttp - excel VBA

Question

So, I'm trying to scrape the following public site using vba and the WinHttp library:

https://auctions.seecao.com/DAILY_AUCTION_LIST

Having examined the network traffic when the "Show Data" button is clicked, I came up with my code:

Sub test()
Dim border As String
Dim req As New WinHttpRequest
Dim url As String
Dim reqBodyObj As Object, respObj As Object, auction As Object
Dim reqBodyStr As String
Dim deliveryDay As Date
url = "https://auctions.seecao.com/api/DailyAuction/GetDailyAuctionList"
deliveryDay = Date
border = "ALME"
Set reqBodyObj = JsonConverter.ParseJson("{""parameters"":{""dayFrom"":""2021-04-01"",""dayTill"":""2021-04-01"",""auctionState"":[0,3,4,5,6,7,9]}}")
reqBodyObj("parameters")("dayFrom") = Format(deliveryDay + 1, "yyyy-mm-dd")
reqBodyObj("parameters")("dayTill") = Format(deliveryDay + 1, "yyyy-mm-dd")
reqBodyStr = JsonConverter.ConvertToJson(reqBodyObj)

With req
    .Open "POST", url, False
    .setRequestHeader "Content-Type", "application/json"
    .Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 256 '=0x0100 =ignore "Unknown certification authority (CA) or untrusted root" error refer to: https://docs.microsoft.com/en-us/windows/win32/winhttp/winhttprequestoption
    .send reqBodyStr
    Debug.Print .responseText
    Set respObj = JsonConverter.ParseJson(.responseText)
End With

For Each auction In respObj("dailyAuctionListData")("rows")
    If auction("columns")("auctionName") Like border & "*" Then
        Debug.Print auction("columns")("id")
    End If
Next auction
End Sub

It's probably worth noting that the first time you visit the site via a browser you'll get a warning saying that the server's certificate is not trusted and you'll have to add an exception to visit it:

To overcome this I used this .Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 256 to ignore the errors.

Everything worked fine for about a month. Today however I started getting the following error:

Run-time error '-2147012711 (80072f99)': No credentials were available in the client certificate.

So I suppose that the website started requesting a client certificate?

Requesting the data through a browser works normally without the need of any credentials and the POST request that is being sent under the hood seems to be the same as it used to be. Editing and resending the request via the browser's developer tools also works without any problems.

So my question is, what is going on here? Is the site asking for a client certificate?

And if so, which client certificate does Firefox use under the hood?

Does the browser use some kind of a default certificate for these cases?

Why am I asked for credentials when I run the code when clearly they are not needed when I visit the page?

Finally, when I try to specify a random certificate like so :

.SetClientCertificate "NameOfCertificate"

the request is being sent and I get the following response:


400 The SSL certificate error

400 Bad Request
The SSL certificate error
nginx/1.18.0

Keep in mind that it's a public site accessible to everyone. No subscription needed.

EDIT

The same request works just fine with Postman after disabling SSL certificate verification. Is there a way to imitate this behavior in VBA?

Web scraping client certificate issue WinHttp - excel VBA

0 Answers