PHP generating Excel file over SSL



  • I am having a devil of a time trying to figure out how to get this to work in IE.  It works fine for Opera and Firefox, but none of my users use those, and it's not feasible to convince them to do so.

    Here's the problem:

    I have a PHP page which dynamically generates an Excel file for download.  When an IE user clicks on the link, they get an error (can't download from cache, can't download from this site, etc).  This is a pretty well documented problem with IE, as I've found while scouring Google for answers.  Every single forum/blog I've been to has its own recipe of header()'s to use to fix the problem (Pragma-this, Cache-Control-that) but none of them have worked, and I've tried about 50 different combinations at this point.  I've tried different content-types, dispositions, cache-controls, pragmas, etc.

    The only thing I've done that's gotten it to work is to use http:// (i.e., not using SSL), but this almost entirely defeats the purpose of having an SSL certificate in the first place.  I am right out of ideas, and I'm hoping someone here can shed some light.  Here's some of the relevant code:


    <?php
    session_start();

    // do some database stuff and write to these strings
    $header = "excel headers";
    $data = "excel data";

    // etc...

    // print out the data as an excel file for download
    // note that I've changed these up a billion times, but this is my original coding
    // that's worked fine for every non-IE browser and/or non-SSL session to date
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=what_ever.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\n$data";
    exit();
    ?>


    Please note that I don't have access to any .ini files as far as I know.  A browser setting change would probably also work as an acceptable solution, but I've tried a bunch of those with no avail either.



  • I have no idea why it shouldn't work, i use about the same setup of header's as you do for downloadable content, but have never used it on a site that was viewed over SSL.

    Have you tried just uploading a XLS file and downloading it and looking what headers apache generates for it?

     



  • That's a good idea.  I just did it, but I'm not really making any sense of it.  Here's the headers (I use Live HTTP Headers Firefox extension):

    First, without SSL.


    http://www.xxx.com/ezr3/test.xls

    GET /ezr3/test.xls HTTP/1.1
    Host: www.xxx.com
    User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7
    Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
    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
    If-Modified-Since: Tue, 23 Oct 2007 15:40:03 GMT
    If-None-Match: "30b506-3600-471e15d3"

    HTTP/1.x 304 Not Modified
    Date: Tue, 23 Oct 2007 15:41:11 GMT
    Server: Apache
    Connection: close
    Etag: "30b506-3600-471e15d3"
    ----------------------------------------------------------


    And here's with SSL.


    https://www.xxx.com/ezr3/test.xls

    GET /ezr3/test.xls HTTP/1.1
    Host: www.xxx.com
    User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7
    Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,
    /;q=0.5
    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

    HTTP/1.x 200 OK
    Date: Tue, 23 Oct 2007 15:41:41 GMT
    Server: Apache
    Pragma: no-cache
    Cache-Control: no-cache
    Last-Modified: Tue, 23 Oct 2007 15:40:03 GMT
    Etag: "30b506-3600-471e15d3"
    Accept-Ranges: bytes
    Content-Length: 13824
    Connection: close
    Content-Type: application/x-msexcel
    ----------------------------------------------------------


    I'm going to look at these and experiment a little bit, but does anything obvious jump out?



  • Are you using the Spreadsheet_Excel_Writer class from the PEAR library? I've been using that for a couple years now to output Excel files over HTTPS without trouble. No messing with wonky headers and the like. Pretty simple:

    $wb = new Spreadsheet_Excel_Writer();
    [.... build workbook ...]
    $wb->send('Filename_goes_here.xls');
    $wb->close();

    No problems getting Excel files via IE using that. 



  • This sound very similar to a similar post we had a few months ago (http://forums.worsethanfailure.com/forums/thread/112787.aspx).  I used the following code to download files via PHP before.  As I stated in the previous post, I don't do PHP/Web stuff much any more; so I'm not entirely sure what's going on.  My guess would be the way IE handles the content-type header attribute.  The web application downloaded both PDFs and XLSs, utilized SSL and it worked flawlessly.

    if (strstr($HTTP_USER_AGENT,"MSIE"))
        {
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-type: application-download");
            header("Content-Length: $size");
            header("Content-Disposition attachment; filename=MyPDF.pdf");
            header("Content-Transfer-Encoding: binary");
        }
        else
        {
            //header("Pragma: public");
            //header("Expires: 0");
            //header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-type: application-download");
            header("Content-Length: $size");
            header("Content-Disposition attachment; filename=MyPDF.pdf");
            //header("Content-Transfer-Encoding: binary");
        }    

    readfile($file);


     



  • @boohiss said:

    That's a good idea. I just did it, but I'm not really making any sense of it. Here's the headers (I use Live HTTP Headers Firefox extension):

    First, without SSL.


    http://www.xxx.com/ezr3/test.xls

    HTTP/1.x 304 Not Modified
    Date: Tue, 23 Oct 2007 15:41:11 GMT
    Server: Apache
    Connection: close
    Etag: "30b506-3600-471e15d3"
    ----------------------------------------------------------


    And here's with SSL.


    https://www.xxx.com/ezr3/test.xls

    HTTP/1.x 200 OK
    Date: Tue, 23 Oct 2007 15:41:41 GMT
    Server: Apache
    Pragma: no-cache
    Cache-Control: no-cache
    Last-Modified: Tue, 23 Oct 2007 15:40:03 GMT
    Etag: "30b506-3600-471e15d3"
    Accept-Ranges: bytes
    Content-Length: 13824
    Connection: close
    Content-Type: application/x-msexcel
    ----------------------------------------------------------


    I'm going to look at these and experiment a little bit, but does anything obvious jump out?

     

    Yeah that the top example replies that you already have the file in your cache :)

     
    Second example shows content-length, which might be interesting to add to your headers. (but perhaps apache already does this anyway?)

    It also shows you the correct mime type for excel, which apparently is "application/x-msexcel", instead of the "application/vnd.ms-excel" you used. (uncertain if it matters though, since this is just used by the browser/system to decide what to do with it)

    stating the obvious again, but you might want to also watch what headers are sent when you get your dynamically generated xls. And see what's the difference between that and the static file download example you now have.

    BTW i'm not familiar with live http headers plugin, but shouldn't there be a SSL handshake me-thingy above the request?

    Also i doubt it will be this, but you might want to try it with a bigger file. Of course i don't know how big your dynamically generated xls file is, but i had some trouble with IE  and a very small page a long time ago, still unsure what really happened, but by upping the size to at least 1k seemed to fix it.



  • @boohiss said:

    That's a good idea.  I just did it, but I'm not really making any sense of it.  Here's the headers (I use Live HTTP Headers Firefox extension):

    First, without SSL.


    http://www.xxx.com/ezr3/test.xls

    GET /ezr3/test.xls HTTP/1.1
    Host: www.xxx.com
    User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7
    Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
    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
    If-Modified-Since: Tue, 23 Oct 2007 15:40:03 GMT
    If-None-Match: "30b506-3600-471e15d3"

    HTTP/1.x 304 Not Modified
    Date: Tue, 23 Oct 2007 15:41:11 GMT
    Server: Apache
    Connection: close
    Etag: "30b506-3600-471e15d3"
    ----------------------------------------------------------


    And here's with SSL.


    https://www.xxx.com/ezr3/test.xls

    GET /ezr3/test.xls HTTP/1.1
    Host: www.xxx.com
    User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7
    Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,
    /;q=0.5
    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

    HTTP/1.x 200 OK
    Date: Tue, 23 Oct 2007 15:41:41 GMT
    Server: Apache
    Pragma: no-cache
    Cache-Control: no-cache
    Last-Modified: Tue, 23 Oct 2007 15:40:03 GMT
    Etag: "30b506-3600-471e15d3"
    Accept-Ranges: bytes
    Content-Length: 13824
    Connection: close
    Content-Type: application/x-msexcel
    ----------------------------------------------------------


    I'm going to look at these and experiment a little bit, but does anything obvious jump out?

     

    Is it fun working for a porno website ?    :)



  • Or you could just let Apache take care of the details and simplify the PHP enormously:

        SetEnvIf User-Agent ".MSIE." nokeepalive ssl-unclean-shutdown downgrade-1.0 force-response-1.0

    does wonders in httpd.conf 

     



  • @lpope187 said:

    This sound very similar to a similar post we had a few months ago (http://forums.worsethanfailure.com/forums/thread/112787.aspx). 

    ...
            header("Content-type: application-download");
    ...

    Shouldn't it be application/download ? </stupid question>



  • A workaround that that will make  IE ignore the cache is to provide the hyperlink as  <a href="dynamicxlsgenerator?foo=uniqueid.xls">Download Excel File</a>

    On the server side, you ignore the query parameter foo and do the same as <a href="dynamicxlsgenerator"> The uniqueid should be different on every page served with the hyperlink (a random number will be good enough in practice). The hyperlink that IE sees is different every time so it won't try the cache. The .xls on the end is another workaround, because some versions of IE sometimes ignore or are too late in recognizing the mime type of the content.

    The same trick can be used to avoid cached dynamic images in <img src="dynamicimagegenerator?foo=uniqueid.png"/>.

    That said, as the returned XLS is dynamic, the link is not idempotent. What you really should use, according to W3C standards, is button a with a POST request - that should solve the cache problem too.

    <form method="POST" action="dynamicxlsgenerator"><input type="submit" value="Download Excel File"/></form>
     



  • Heh, no, actually I just x'ed out the actual site (just in case the wife is reading this forum for some random reason).



  • @boohiss said:

    Heh, no, actually I just x'ed out the actual site (just in case the wife is reading this forum for some random reason).

    So you obfuscated the domain name using standard porn-notation, just in case your wife is reading this forum????



  • @stratos said:

    BTW i'm not familiar with live http headers plugin, but shouldn't there be a SSL handshake me-thingy above the request?

    Shouldn't that happen in the socket layer? IOW, shouldn't the headers and the rest of the data be encrypted if we're showing SSL data?

     

    @JvdL said:

    A workaround that that will make  IE ignore the
    cache is to provide the hyperlink as  <a href="dynamicxlsgenerator?foo=uniqueid.xls">Download Excel
    File</a>

    I usually append "?randomnumber" or "&nocache=randomnumber" for a quick fix.

    Isn't there a "no-cache" directive in some HTTP response header?

     



  • @aib said:

    Isn't there a "no-cache" directive in some HTTP response header?

    From my experience, how MSIE deals with HTTP header info is unpredictable at best, as the OP contests.



  • There are supposed to be many, but yeah, what he said about IE...



  • @aib said:

    @stratos said:

    BTW i'm not familiar with live http headers plugin, but shouldn't there be a SSL handshake me-thingy above the request?

    Shouldn't that happen in the socket layer? IOW, shouldn't the headers and the rest of the data be encrypted if we're showing SSL data?
     

    Live HTTP Headers is a Firefox extension and plugs itself into Firefox's API to look at the data flowing back and forth long before it gets crypted. It's not an external proxy that sniffs the headers out of the data stream.

    And generally speaking, you can't differentiate an SSL-encrypted connection from a regular non-encrypted link just from the HTTP headers, except via minor clues like port # (443 v.s. 80), and protocol (https v.s. http). There's nothing in HTTP/HTTPS headers that fundamentally jump out and screams "THIS IS AN ENCRYPTED CONNECTION!", and there's no reason it should. SSL is supposed to be transparent once the link has been established.

    Of course, there'll be a lot of SSL-related environment variables available to CGI scripts, but those are put there by the web server on the receiving end, and don't come in (or go out) over the wire in the request.


     



  • i guess my confusion was because curl also shows you the ssl handshake.




  • <?php
    session_start();

    // do some database stuff and write to these strings
    $header = "excel headers";
    $data = "excel data";

    // etc...

    // print out the data as an excel file for download
    // note that I've changed these up a billion times, but this is my original coding
    // that's worked fine for every non-IE browser and/or non-SSL session to date
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=what_ever.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\n$data";
    exit();
    ?>

     

    Here's my set of headers I use for downloader scripts. It seems to work with everything I throw at it, but I haven't actually tried it over SSL.

    header( 'Content-Length: ' . $filesize );
    header( 'Pragma: public' ); // required for IE
    header( 'Expires: 0' );
    header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
    header( 'Cache-Control: private', false ); // required for certain browsers
    header( 'Content-Type: ' . $mime ); // you want application/vnd.ms-excel
    header( 'Content-Transfer-Encoding: binary' );

    $filename = str_replace( '"', ''', $filename ); // IE can't handle double quotes
    header( 'Content-Disposition: attachment; filename="' . $filename . '";' );



  • Yup.. Its working fine for  HTTPS

    header("Content-type: application/x-msdownload");
      header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
      header( 'Content-Transfer-Encoding: binary' );
      header("Content-Disposition: attachment; filename=".$filename);
      header("Pragma: public");
      header("Expires: 0");



  • Awesome!! 

    Had the same problem and that worked for me too. (insert whew here) . Pretty much just plugged the following headers 

      header("Content-type: application/x-msdownload");
      header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
      header( 'Content-Transfer-Encoding: binary' );
      header("Content-Disposition attachment; filename=".$filename);
      header("Pragma: public");
      header("Expires: 0");

    but changed  this line

      header("Content-Disposition attachment; filename=".$filename);

    to the following so that it would interface with mysql

    header('Content-Disposition: attachment; filename='.$dbTable.'-'.date('Ymd').'.xls');

     Which was in the code I used from the following author and website to  "export mysql to excel with php"

    Export MySQL to Excel using PHP

    Author: Vlatko Zdrale, http://blog.zemoon.com

     

     



  • Man I can't wait until the day when IE6 is dead and gone. How many lives have been tortured trying to figure out the obscure bugs in that browser??

    Anyway, I pulled these lines of code out of a project I'm working on right now:

    // IE has a bug where it can't display certain mimetypes if a no-cache header is sent,
    // so we need to switch the header right before we stream the PDF.
    header('Pragma: private');
    header('Cache-Control: private');
    echo $pdf->ezOutput();

    I had the damnedest time figuring this out, too. I used LiveHeaders in Firefox to look at the actual headers being sent and determined that the framework I was using (Zend Framework) was overwriting my headers. So I had to move the header() calls down immediately before the content is sent. The library I'm using here (EzPdf) is setting the content headers that are necessary.

    For some reason I eventually settled on "private"... don't remember why or what that even means. Looking at your headers, you are sending a "no-cache", which is definitely not the right header to send to IE.



  • @savar said:

    Man I can't wait until the day when IE6 is dead and gone.

    Ha. Even IE5 [url=http://support.microsoft.com/lifecycle/?p1=3071]isn't[/url] yet.

    "Cache-Control: private" means that the response is intended for a single user and may not be stored in a shared cache. "Pragma: private" is meaningless.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.