php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #41539 MSSQL ext. with freetds treats '' (string of length 0) as NULL in VARCHAR(MAX)
Submitted: 2007-05-30 09:27 UTC Modified: 2008-08-14 19:32 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:2 (100.0%)
Same OS:1 (50.0%)
From: phpbug dot 41539 at sub dot noloop dot net Assigned: fmk (profile)
Status: Closed Package: MSSQL related
PHP Version: 5.2.5 OS: *
Private report: No CVE-ID: None
 [2007-05-30 09:27 UTC] phpbug dot 41539 at sub dot noloop dot net
Description:
------------
While http://bugs.php.net/bug.php?id=39213 fixed the issue with a '' value (a string of length 0) being returned to PHP as NULL, the issue persists for data stored in columns of type VARCHAR(MAX) (and NVARCHAR(MAX)).

I don't think this is a freetds issue, since running tsql on the command line shows NULL and empty strings correctly after running the PHP code below.

TSQL output is:
% ./tsql -H someserver -p someport -U testnull
locale is "LC_CTYPE=no_NO.ISO8859-1;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C"
locale charset is "ISO-8859-1"
Password: 
1> select * from testnull;
2> GO
id      txt     txtmax
1
2       NULL    NULL




Also, is there a reason for why php_dblib.dll is not being shipped on the win32 binary builds? Apart from this bug, php_dblib appears to be the most complete mssql extension available. All the others I've tried  have critical shortcomings (php_mssql does not support nchar/nvarchar (unicode) columns at all, and also, while the third party php_odbtp_mssql does work properly (actually it even returns NULLs correctly), it is an order of magnitude slower than php_dblib).

Reproduce code:
---------------
<?
  header('Content-Type: text/plain');
  $ms = mssql_connect('some-sqlserver2005-hostname', 'testnull', 'testnull');
  mssql_select_db('testnull', $ms);
  mssql_query('DROP TABLE testnull', $ms);
  mssql_query('CREATE TABLE testnull (id integer, txt varchar(255) NULL, txtmax varchar(max) NULL)', $ms);
  mssql_query('INSERT INTO testnull VALUES (1, \'\', \'\')', $ms);
  mssql_query('INSERT INTO testnull VALUES (2, NULL, NULL)', $ms);

  $rs = mssql_query('SELECT * FROM testnull', $ms);
  while ($row = mssql_fetch_assoc($rs)) {
    var_dump($row);
  }


Expected result:
----------------
array(3) {
  ["id"]=>
  int(1)
  ["txt"]=>
  string(0) ""
  ["txtmax"]=>
  string(0) ""
}
array(3) {
  ["id"]=>
  int(2)
  ["txt"]=>
  NULL
  ["txtmax"]=>
  NULL
}


Actual result:
--------------
array(3) {
  ["id"]=>
  int(1)
  ["txt"]=>
  string(0) ""
  ["txtmax"]=>
  NULL
}
array(3) {
  ["id"]=>
  int(2)
  ["txt"]=>
  NULL
  ["txtmax"]=>
  NULL
}


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-06-04 14:16 UTC] frode at coretrek dot com
So I had a go at hacking on the C source for the mssql extension, and I think I have come up with some sort of solution.

First of all, it appears that the regular FreeTDS dblib api has no way to distinguish "" from NULL for varchar(max). dbdatlen() returns 0 either way, and dbdata() returns a pointer to 0x0. 

Looking at the source code for the FreeTDS tsql application, it became obvious that when result set columns contains a real NULL, the "column_cur_size" is always less than 0. Relevant piece of code from tsql.c:

  if (col->column_cur_size < 0) {
    if (print_rows)
      fprintf(stdout, "NULL\t");
    continue;
  }

Unfortunately, negative column_cur_size is masked in dblib. Relevant piece of code from
dblib.c's implementation of dbdatlen():

  if (colinfo->column_cur_size < 0)
    ret = 0;
  else
    ret = colinfo->column_cur_size;

So it seems we need to access the low level TDS structures to tell the difference. 
Playing around with gdb, it seems that column_cur_size is 0 for strings and -1 for real NULLs.

So I came up with an ugly hack for php_mssql.c. It does produce the expected output,
but it's been a long time since I did anything serious in C, so I don't know if it is 
the correct way of doing things. 

Tested on Windows 2003 x86, compiled with Microsoft Visual C++ 2005 (Compiler version 
14.00.50727.42 for 80x86), FreeTDS 0.64, running against an instance of SQL Server 2005,
and c:\freetds.conf contains:
[global]
  tds version = 7.0
  
  
I also added a "dbdata(..) == NULL" condition to the if()
in php_mssql_get_column_content_without_type(), just because that function seems to be similar to
what was fixed in http://cvs.php.net/viewcvs.cgi/php-src/ext/mssql/php_mssql.c?r1=1.152.2.13.2.2&r2=1.152.2.13.2.3&pathrev=PHP_5_2
but I don't know if it is necessary or even correct. 
  
Here is the diff against PHP 5.2.3. 


diff -u /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.c ./php_mssql.c
--- /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.c   2007-02-24 03:17:25.000000000 +0100
+++ ./php_mssql.c       2007-06-04 15:37:25.265625000 +0200
@@ -814,8 +814,21 @@
 static void php_mssql_get_column_content_with_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type  TSRMLS_DC)
 {
        if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) {
-               ZVAL_NULL(result);
-               return;
+#ifdef HAVE_FREETDS
+               /* double check that it is a real null, it could also be a zero-length varchar(max) string */
+               TDSCOLUMN *colinfo;
+               TDSRESULTINFO *resinfo;
+
+               resinfo = ((DBPROCESS*)(mssql_ptr->link))->tds_socket->res_info;
+               colinfo = resinfo->columns[offset-1];
+               if (colinfo->column_cur_size < 0) {
+#endif
+                       ZVAL_NULL(result);
+                       return;
+#ifdef HAVE_FREETDS
+               }
+#endif
+
        }
 
        switch (column_type)
@@ -935,7 +948,7 @@
 
 static void php_mssql_get_column_content_without_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC)
 {
-       if (dbdatlen(mssql_ptr->link,offset) == 0) {
+       if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) {
                ZVAL_NULL(result);
                return;
        }
diff -u /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.h ./php_mssql.h
--- /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.h   2007-01-01 10:36:03.000000000 +0100
+++ ./php_mssql.h       2007-06-04 15:37:07.343750000 +0200
@@ -31,8 +31,17 @@
 #define PHP_MSSQL_API
 #endif
 
+#if HAVE_FREETDS
+/* mega hack to get hold of TDSCOLUMN->column_cur_size to check for NULL column values in VARCHAR(MAX) */
+#define _FREETDS_LIBRARY_SOURCE 1
+#include <tds.h>
+#include <sybfront.h>
+#include <sybdb.h>
+#include <dblib.h>
+#else
 #include <sqlfront.h>
 #include <sqldb.h>
+#endif
 
 typedef short TDS_SHORT;
 #ifdef HAVE_FREETDS




Finally, does anyone know if there's a chance that php_dblib.dll will be distributed
with the regular php.net win32 binary release in the future?
 [2007-06-18 20:16 UTC] erudd at netfor dot com
Patch Developed w/ freetds maintainer to fix this issue

--- mssql/php_mssql.c.orig      2006-04-04 14:49:12.000000000 -0400
+++ mssql/php_mssql.c   2006-10-24 16:41:18.000000000 -0400
@@ -818,7 +818,7 @@
 
 static void php_mssql_get_column_content_with_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type  TSRMLS_DC)
 {
-       if (dbdatlen(mssql_ptr->link,offset) == 0) {
+       if (dbdatlen(mssql_ptr->link,offset) == 0 && dbdata(mssql_ptr->link,offset) == NULL) {
                ZVAL_NULL(result);
                return;
        }
@@ -941,7 +941,7 @@
 
 static void php_mssql_get_column_content_without_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC)
 {
-       if (dbdatlen(mssql_ptr->link,offset) == 0) {
+       if (dbdatlen(mssql_ptr->link,offset) == 0 && dbdata(mssql_ptr->link,offset) == NULL) {
                ZVAL_NULL(result);
                return;
        }
 [2007-06-18 20:27 UTC] erudd at netfor dot com
Isn't this a dup of #39213.
 [2007-06-19 05:58 UTC] frode at coretrek dot com
I don't get it - the patch you posted is partly already in 5.2.3, i.e. it's already there for php_mssql_get_column_content_with_type. But not in php_mssql_get_column_content_without_type. But that's not the real issue here:

As I mentioned in the original submission, bug 39213 fixed the problem for most datatypes but doesn't help for the datatype NVARCHAR(MAX). That's because FreeTDS does not return NULL for dbdata() on an NVARCHAR(MAX). Please, take another look at my second comment to this bug, where I posted an ugly work-around that requires the use of FreeTDS' underlying libTDS, which seems to be the only API that can tell the difference between a NULL and a "" in BLOBs (which is what the NVARCHAR(MAX) datatype basically is); the difference is that the size is -1 for NULL values, and 0 for "" values, but the dblib API converts -1 to 0 in dbdatlen().

Thanks again for looking at this bug a second time :)
 [2007-11-15 14:11 UTC] freddy77 at gmail dot com
I'm a FreeTDS developer.

There was a problem with (n)text and (n)varchar(max), not with (n)varchar (these types are blobs). For empty blobs pointer from dbdata was NULL. I fixed the problem in CVS HEAD and 0.64 branch. You can find cumulative post 0.64 patch at http://freetds.sourceforge.net/post64.diff.gz, change is at http://freetds.cvs.sourceforge.net/freetds/freetds/src/dblib/dblib.c?r1=1.244.2.3&r2=1.244.2.4 (similar change for CVS head).

bye
  freddy77
 [2007-11-15 15:31 UTC] frode at coretrek dot com
I compiled (freetds 0.64 + post64.diff.gz) and then recompiled my (unmodified) php 5.2.5 using "--with-mssql=/path/to/the/freetds/installation", and whatever you did in post64.diff.gz seems to fix the problem. The test case in my original submission now prints:

array(3) {
  ["id"]=>
  int(1)
  ["txt"]=>
  string(0) ""
  ["txtmax"]=>
  string(0) ""
}
array(3) {
  ["id"]=>
  int(2)
  ["txt"]=>
  NULL
  ["txtmax"]=>
  NULL
}

as expected.

Thank you very much!

Is there a timeline/ETA for the next stable release of FreeTDS (0.65)? 

Also, if any php developers are reading this: It would be great if the win32 binary distribution of PHP could be compiled and linked with FreeTDS instead of the Microsoft DBLIB libraries :)
 [2007-11-15 15:33 UTC] frode at coretrek dot com
The previous comment was a bit imprecise. I compiled FreeTDS from CVS tag BRANCH0_64 as of today, not from freetds-0.64.tar.gz+post64.diff.gz.
 [2008-08-14 19:32 UTC] jani@php.net
Apparently this wasn't bug in PHP but freetds. Open separate report about the dblib with win32.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Sat Aug 13 09:05:44 2022 UTC