Skip to main content

Querying GitHub Issues from Google App Script

I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later. GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation.  After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits ) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per d

Indy and HTTPS Post

I'm back to work on the HTTPS Post issue with DHL's ShipIt XML API. The problem I'm having is when I use Indy to post the xml via an HTTPS post, the response is that the file is malformed. I should mention that I'm using a relatively recent version of Indy 10. If I use FF (if you don't have it, check out the Poster add-on, let's you play with the HTTP functions using FF's libraries) it works fine. The first problem is that it's a little hard to see what FF is actually doing that is different from what Indy is doing. Enter Fiddler, stage right. Fiddler is an http proxy that captures that http information and lets you manipulate it or display the raw headers. So, pointing FF to the localhost proxy on port 8888, the headers I get are:

POST /apilandingtest.asp HTTP/1.1
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv: Gecko/2008070208 Firefox/3.0.1
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Content-Type: text/xml
Content-Length: 1509
Pragma: no-cache
Cache-Control: no-cache

Applying a TIdLogFile to the Indy HTTP IO Intercept provides Indy's headers of:

POST /apilandingtest.asp HTTP/1.0
Content-Type: multipart/form-data; boundary=--------072108153606968
Content-Length: 1662
Accept: text/html, */*
Accept-Encoding: identity
User-Agent: Mozilla/3.0 (compatible; Indy Library)

Content-Disposition: form-data; name="Shipment"; filename="dhl.xml"
Content-Type: text/xml

Obviously, not the same. A large part of this is because I'm using the TidMultiPartFormData class to encapsulate the data. Changing over to just a plain TStringList results in headers of :

POST /apilandingtest.asp HTTP/1.0
Content-Type: text/xml
Content-Length: 1471
Accept: text/html, */*
Accept-Encoding: identity
User-Agent: Mozilla/3.0 (compatible; Indy Library)

but DHL still won't take the file, responding again that the XML is malformed. Looking into the log a little more, it appears as if the HTTP Post converted all CR/LF pairs into the symbol &. Sure enough, in Indy's code, there is a converter that converted CR/LF to &. Stripping CR/LF pairs out of the raw XML produces a workable solution that passes DHL's IIS server and returns the correct response. Obviously, that's a bit of a poor solution since it leaves the XML mashed together and I tend to use a text editor to review my XML data more often than an XML editor. So, with a short bit of preprocess work that converts CR/LF pairs to spaces, I now have a working Indy SSL HTTPS Post routine that DHL will accept. I've posted the code below so you won't have to go digging all over creation for a sample of Indy 10's HTTPS Post. Enjoy!

CR = #13;
LF = #10;
xmlEOL = EOL;

// this is a descendant of TidLogFile, it will create a plain text file with
// information about the transfer session
TlogFile = class(TidLogFile)
procedure LogReceivedData(const AText, AData: string); override;
procedure LogSentData(const AText, AData: string); override;
procedure LogStatus(const AText: string); override;
procedure LogWriteString(const AText: string); override;
class function buildLogLine(data, prefix: string) : string;

// this ensures the output of error and debug logs are in the same format, regardless of source
class function TlogFile.buildLogLine(data, prefix: string) : string;

data := StringReplace(data, EOL, RSLogEOL, [rfReplaceAll]);
data := StringReplace(data, CR, RSLogCR, [rfReplaceAll]);
data := StringReplace(data, LF, RSLogLF, [rfReplaceAll]);

result := FormatDateTime('yy/mm/dd hh:nn:ss', now) + ' ';
if (prefix <> '') then
result := result + prefix + ' ';
result := result + data + EOL;

// ---------------------------------------------------------------------------

procedure TlogFile.LogReceivedData(const AText, AData: string);
// ignore AText as it contains the date/time
LogWriteString(buildLogLine(Adata, '<<'));

// ---------------------------------------------------------------------------

procedure TlogFile.LogSentData(const AText, AData: string);
// ignore AText as it contains the date/time
LogWriteString(buildLogLine(Adata, '>>'));

// ---------------------------------------------------------------------------

procedure TlogFile.LogStatus(const AText: string);
LogWriteString(buildLogLine(AText, '**'));

// ---------------------------------------------------------------------------

procedure TlogFile.LogWriteString(const AText: string);
// protected --> public

// ---------------------------------------------------------------------------

//this will use an https post to push the data
procedure TDHLShipment.sendFile;
const testURL = 'https://some url that accepts data'; //replace this with the url of where you will be sending the data or send it as a param
logFileName = 'j:\winprog\main\http.log'; //the logfile name if you use the logging
plainData : TStringList; //this is the unencoded data
// formData : TIdMultiPartFormDataStream; //I've left this in just in case you want to see how the multi-part works
HTTP : TidHTTP; //going to need the HTTP object
SSLIO : TIdSSLIOHandlerSocketOpenSSL; //going to need the SSL object
response : TStringList; //this will catch the response from the server
logFile : TLogFile; //this is the logfile and is optional but handy for debuging

//this is because the plainData TStringList may contain CR/LF pairs. If so, HTTP Post will convert those pairs to & which, in my specific case, kills the data.
procedure ConvertCRLFToSpace;
if plainData.Count > 1 then
// convert CR/LF pairs to spaces
plainData.Text := StringReplace(Trim(plainData.Text), sLineBreak, ' ',[rfReplaceAll]);

// formData := TIdMultiPartFormDataStream.Create; //if you wanted to send multi-part data, use this instead
plainData := TStringList.Create; //My specific case wants stripped down headers and no multipart functionality
// formData.AddFile('Shipment', 'dhl.xml', 'text/xml'); //you can add a lot more detail about the file with the multi part data stream
plainData.LoadFromFile('dhl.xml'); //or you can just load up the plain file
Http:=TidHTTP.Create(nil); //create the HTTP instance
SSLIO:=TIdSSLIOHandlerSocketOpenSSL.Create(nil); //create the SSL handler
SSLIO.SSLOptions.Method := sslvSSLv3; //set the SSL mode
HTTP.ReadTimeout := 10000; //depending on size of upload, you may need to adjust these
HTTP.ConnectTimeout := 10000;
HTTP.IOHandler := SSLIO; //assign the SSL handler to the HTTP IO Handler or you won't be able to interact with SSL servers
HTTP.Request.Contenttype := 'text/xml'; //I'm doing XML, set the content type appropriate to your data type. If you used the multi-part, you probably don't need this
HTTP.HTTPOptions := []; //no options
logFile := TLogFile.Create(nil); //create the log file
logFile.FileName:=logFileName; //set the default log file name - NOTE: you could get fancy with this, but I normally don't use it
logFile.Active:=true; //activate the log file (creates the file and intializes everything)
HTTP.IOHandler.Intercept := logFile; //set the logFile to the intercept of the IOHandler to capture the events
response:=TStringList.Create; //create the string list to catch the server's response
ConvertCRLFToSpace; //clean up the CR/LFs so the data doesn't get funky (you may not need this)
response.Text := HTTP.Post(testURL, plainData); //post the data to the URL and place the response in the response string list
except on EIdOSSLCouldNotLoadSSLLibrary do //if we have an SSL problem, this is going to be when it shows
ShowMessage('An error occurred loading the SSL '+ //provide a little clue although, as I mentioned yesterday, you may need to change the SSL loader to get good error messages
'libraries. Please make sure you '+
'have installed the OpenSSL '+
'libraries from http://www.'+
response.SaveToFile('response.xml'); //save the server's response (or process it or whatever you're going to do)
ShowMessage('Sent successfully. Response='+#13#10+response.Text); //tell the user it worked
response.Free; //free the response
finally; //free the http object
plainData.Free; //free the data



That tip on the CR/LF pairs solved a problem we had. Thanks a bunch!
Anonymous said…
Wonderful post. Thanks for sharing the code. This got us out of a bind.
Marshall Fryman said…
Glad it helped. It took me a while to track down all the information I needed to diagnose the problem. I tend to post on anything that I had to spend a lot of time researching so it's an easy reference for everyone.
James said…
This comment has been removed by the author.
Anonymous said…
Many thanks for this tip!

I have been trying to use TIdHTTP to invoke an IBM Webshpere SOAP service for many months, but could never get it to work.

Using this article, I finally have TIdHTTP working!

Many thanks!

CNB said…
The CRLF to & behavior is documented in the manual. Under "TIdCustomHTTP.Post" it says: "When ASource is a TStrings instance, Post will replace all occurrences of the End-Of-Line (EOL) character in ASource with the value '&' prior to transfer to the HTTP server. When ASource is a TStream instance, no preprocessing of the stream content is performed." The clean solution, in my opinion, if possible, would be to place your data in a TStream before sending, not a TStringList.

Popular posts from this blog

Detecting a virtualized environment

CubicDesign on recently asked the question: "How do I know/detect if my software is running under Windows [or a virtual environment]?" Well, it turns out that it's a lot harder to tell than you would think. Apparently, the VM (VMware, Xen, Wine, etc.) doesn't really want you to be able to do this. At least not easily. For VMware, there is a decent little C routine called jerry.c that does the trick. Jerry actually uses a simple communication channel that VMware left open. It's not 100% foolproof since the admin can change the channel, but that's not likely going to happen unless the system is specifically designed to be a honeypot. If you're running on a honeypot and still have a legitimate reason for detection, you could look at the much more complex scoopy implementation which inspects how the system is actually virtualized using the SIDT CPU instruction instead of a communication channel. Another reference (red pill) is here .

SMTP Mail and Indy (again)

Having spent a lot of time recently working on a ping scanner using Indy, I noticed that there's a lot of questions still on using SMTP with Indy. Let me first say that I am not an Indy expert. I get along with it successfully but find I still have to research things frequently. With the disclaimer out of the way, we can get to the offering. A while back I wrote a handy little unit to simply send a mail message with or without attachments and with or without providing authentication. It even has support for OpenSSL. I use this unit in a number of applications and have seen it successfully send hundreds of e-mails without issue. I recently added support for threaded message sending to take advantage of the multi-core system I'm now running on. This code has had a few additions (like the logger) that I've gleaned over time from various newsgroup postings, but I didn't record the authors so let me credit the anonymous Internet authors who support Indy. It's really am

Detecting Virtual PC

Adding to my previous post on detecting virtual environments, here's the code for detecting Virtual PC. Note that it's a conversion from CodeProject, the original author is here . I also didn't write the conversion, I'm simply accumulating the VMM detection code here. Original credit for the conversion goes to Dennis Pasamore who did the bulk of the conversion work with some assistance from Avatar Zonderatau. Code: function TForm1.IsRunningVirtualPC: boolean ; asm push ebp; mov ebp, esp; mov ecx, offset @exception_handler; push ebx; push ecx; push dword ptr fs:[0]; mov dword ptr fs:[0], esp; mov ebx, 0; // Flag mov eax, 1; // VPC function number // call VPC db $0F, $3F, $07, $0B mov eax, dword ptr ss:[esp]; mov dword ptr fs:[0], eax; add esp, 8; test ebx, ebx; setz al; lea esp, dword ptr ss:[ebp-4]; mov ebx, dword ptr ss:[esp]; mov ebp, dword ptr ss:[esp+4]; add esp, 8; jmp @ret1; @exce

Copyright 2008-2022, Marshall Fryman