Issues with SQL Server 2005 and PHP - Part 1
Posted on 2009-05-08 (Updated on 2019-01-24)
This maddening issue took quite a while to figure out, so I sincerely hope I can save somebody the time by documenting it here.
First of all, here's the environment - at work, we're using the following:
- Windows Server 2003 w. IIS 6
- PHP 5.2.9-1
- MS SQL Server 2005 Express
- Connection made using ODBC functions
Yes, that's right, I have no control over most of the environment. Whatever. Moving on - we were having a problem with one of our new pages. Specifically, getting the information from the database and displaying it on the page.
Whenever I tried to run the query to get the database results, the script would consume all available memory immediately and halt the page execution. We're not talking the normal 8MB of RAM allocated to each page, either: we have our memory allocation bumped up to 128MB. Somehow this script consumed all 128MB and asked for more with just five result rows, each well under a couple kilobytes.
We were able to (somehow) deduce that the problem was in the table itself: apparently PHP's ODBC driver has a fit if there is a 'text' or 'ntext' field involved. It simply consumed all the memory and then quit.
Not a problem, though - MS recommends you stay away from those fields anyways, and instead use nvarchar(MAX) or varchar(MAX). A quick little switch, and we were on our way...
...to an even worse problem. Now, the same field that we had just modified was displaying - but not the text that field contained. Instead, it almost looked as if PHP was dumping memory into that field instead of the text itself. Inside of all the binary symbol garbage I saw things like function names, string values, and more. Extremely upsetting.
After a day and a half I figured out that the new nvarchar(MAX) data type seemed to be the cause, and switched it to the more bland (and not as scalable) nvarchar(1000). Still, if this had happened to one of our live pages, we could have had a serious security problem.
In conclusion, I will stay away from the following SQL Server data types as a sort of "preventative best practice":
- text or ntext
- varchar(MAX) or nvarchar(MAX)
If you have the option, I would recommend staying away from MS SQL Server all together. We have had no limit to the amount of problems this sub-par database has caused. Just use MySQL, PostgreSQL or one of the superior Free alternatives.