ODBC, PHP5 and Microsoft SQL Server
So this is just a short entry about a problem we encountered at work the other day. Some folks here were working on a site using PHP5 and MS-SQL 2000 on Windows and Apache 2.2. They were using the ODBC drivers in PHP to connect to the database, and whenever they had to pull some column data from a ntext field, anything beyond 4k bytes would start to cause garbage to appear on the page. It was like the buffer internally was being overwritten and PHP was just spitting out some random memory chunks. Nothing crashed, but the garbage was unacceptable.
They had decided it was just a problem with the ODBC drivers, and wanted to switch to MySQL to use the familiar MySQL functions, and possibly convert all the code to use Pear::DB at the same time. That was a lot of work, would have put the project behind, and from what little I had over-heard (I don't technically work with them), the clients were not too thrilled.
I finally managed to get in the loop with the help of some folks, and got a chance to look at the problem. I spent close to an hour and a half I think just trying some various code changes, finding out the exact details of the problem. First I checked to see if the data was getting corrupt before it was even put into the query to rule out a problem with apache or PHP's request processing. The query was built just fine, and it all looked good up to the exec() call. Next I busted out the MS-SQL tools to see if the data was getting corrupt during the update operation. I checked the data after it was inserted using the MS-SQL tools and everything appeared to be fine, so the bug was not in ODBC's update sql code. Next I tried changing the type of the field from ntext to plain text, to see if possibly unicode was the issue. Strangely this change made the web server process completely lock up. I am still not sure why this occurred, I simply put it back to ntext and started looking for something else.
I noticed they were using addslashes() to escape the content they were inserting, which I knew was not correct, so I fixed that, but that did not fix the problem. That is where the other folks were suspecting the problem was to begin with. I finally determined that the problem had to be somewhere when the data is read back to the page. I knew MS-SQL's native php extension has a configuration setting that controls how much data you can read back from a text/ntext field, and decided I should look for something in ODBC. Sure enough, ODBC has a similar setting. In the PHP.INI file, you can find a configuration directive entitled odbc.defaultlrl. I increased the size of this field, and noticed that each time I increased it, the length of the string we could store and retrieve before seeing the garbage was also increasing. I ended up setting this to 8 megs, which is the size of the maximum post size. This way nobody will be exceeding the limit as they can't post that much to the DB in the first place.
So, from this I gather there is a bug somewhere either in the ODBC driver, or PHP as there is no reason that garbage started to appear when the limit was reached. The string should have simply been cut off, which I believe is how MS-SQL's native driver handles the situation. But, increasing the limit solved the issue for now, and prevented several weeks worth of work re-doing the site. Everyone is happy again that things can go forward. Lessons learned, don't jump to conclusions. Investigate the issues thoroughly before deciding something is beyond repair. My extra hour and a half of looking into the issue probably saved two or three weeks worth of code re-writes, and possibly an already fragile client relationship.
