Tuesday, 24 August 2010

PHP Library 64-bit Bug Shocker!

I've just spent a happy day troubleshooting one of our legacy systems after we transplanted it to a new server. The system runs on PHP, and uses the PEAR package spreadsheet reader to pull apart Excel spreadsheets uploaded by the user. Using exactly the same code as on our old server, and with all necessary config for our app double-checked, to account for differences in the filesystem, the PP error log suddenly started filling up with strange error messages.

Notice: Uninitialized string offset: 2199023255040 in /home/archi/sites/service/public_html/ui/dataloader/excellib/oleread.inc on line 27


Eventually, I came across a few comments in a forum on PHPBuilder that pointed me to the problem. The oleread.inc file is using some hard-core bitwise operators to decode the XL file format, and by default, these assume a 32-bit instruction set. Our new server, is, of course, 64-bit, and fails spectacularly.

I can't find any official patches. I took the code from the comment, and patched the oleread file myself, and suddenly it works. So, replace this (gotta love one-line functions for clarity, eh?):

function GetInt4d($data, $pos) {
return ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
}


with this:

function GetInt4d($data, $pos) {
$_or_24 = ord($data[$pos+3]);

if ($_or_24>=128)
$_ord_24 = -abs((256-$_or_24) << 24);
else
$_ord_24 = ($_or_24&127) << 24;

return ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | $_ord_24;
}


We're using POI and jXLS to handle our spreadsheet processing systems these days, and after today, I'm very glad that we are.

Hope this helps some lost soul perusing the web in search of a reason for their spreadsheet processor failing to transplant nicely.

UPDATE
I've found the up-to-date version of the oleread.inc library, which is quite an adventure in itself. Searching for "spreadsheet" in the PEAR packages list returns a spreadsheet writer and a datasource based on Excel files, but no reader. Surely the datasource reads the spreadsheets first?

http://pear.php.net/package/Structures_DataGrid_DataSource_Excel

has this to say:

"This is a DataSource driver for Structures_DataGrid using Excel spreadsheets. It
requires that the package Spreadsheet_Excel_Reader is installed (not available
as a PEAR package, but via Sourceforge: http://sourceforge.net/projects/phpexcelreader)."

So, I download the latest version of phpexcelreader, and it does appear to have a patch, at any rate, a slightly different implementation of GetInt4d().

function GetInt4d($data, $pos)
{
$value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
if ($value>=4294967294)
{
$value=-2;
}
return $value;
}


So, if you're using a recent version of oleread.inc, you should be OK.

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi thanks for this solutions. What a nice job !!. Actually i had faced this problem in my blogs. Hey thanks again very very much for this support.

    ReplyDelete
  3. Great post for web developers .I have bookmarked your page for the further reference Thanks for this valueable post.
    Ecommerce Web design

    ReplyDelete
  4. Awesome man that worked like a Charm! Even other experts could not catch this glitch! You did a great job in comparing it with old script and sharing with us the right code to fix this. Thank you and may God bless you, Cya! :)

    ReplyDelete
  5. Thank you for your help... Is working fine.

    ReplyDelete
  6. Thank you Very Much!! You just saved me quite a pit of time troubleshooting. Worked like normal on Xampp, but broke when moved to production Linux server. This fixed it!!

    ReplyDelete
  7. Good information here. I will post these information to my facebook page. It is really very informative for others. FSD solutions

    ReplyDelete