php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #30962 Space being returned for NULL columns
Submitted: 2004-12-02 14:36 UTC Modified: 2006-04-22 18:45 UTC
Votes:18
Avg. Score:4.8 ± 0.7
Reproduced:17 of 17 (100.0%)
Same Version:8 (47.1%)
Same OS:9 (52.9%)
From: richard dot quadling at bandvulc dot co dot uk Assigned:
Status: Not a bug Package: MSSQL related
PHP Version: 5.0.3 OS: Windows XP Pro SP2
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: richard dot quadling at bandvulc dot co dot uk
New email:
PHP Version: OS:

 

 [2004-12-02 14:36 UTC] richard dot quadling at bandvulc dot co dot uk
Description:
------------
This bug has been reported before but repeatedly closed as a bogus bug.

It is NOT bogus. It is NOT a problem in the library. It IS a bug in the PHP code.

The problem is for any column where the content is NULL, the value retrieved by PHP is ' '. That is a single space.

The bug is in php_mssql.c (/* $Id: php_mssql.c,v 1.137.2.4 2004/11/15 23:35:50 iliaa Exp $ */)

Lines 798 to 810 are currently ...

		case SQLTEXT: {
			int length;
			char *data = charcol(offset);

			length=dbdatlen(mssql_ptr->link,offset);
#if ilia_0
			while (length>0 && data[length-1] == ' ') { /* nuke trailing whitespace */
				length--;
			}
#endif
			ZVAL_STRINGL(result, data, length, 1); 
			break;
		}


The problem is that "length" is never tested to see if it is zero, as per the Microsoft documentation (Online books and look for dbdata). It says ...

dbdata ... returns a BYTE pointer to the data for the column. A NULL BYTE pointer is returned if there is no such column or if the data has a null value. To make sure that the data is really a null value, check for a return of 0 from dbdatlen.

and ...

Remarks. The data is not null-terminated. To get the length of the data, use dbdatlen.

I would propose that the php_mssql.c code would be as follows ...

		case SQLTEXT: {
			int length;
			char *data = charcol(offset);

			length=dbdatlen(mssql_ptr->link,offset);
			if (length == 0) {
				ZVAL_EMPTY_STRING(result); // Force the return of an empty string if the length is 0 as data MAY not be NULL.
			} else {
#if ilia_0
				while (length>0 && data[length-1] == ' ') { /* nuke trailing whitespace */
					length--;
				}
#endif				ZVAL_STRINGL(result, data, length, 1); 
			}
			break;
		}


Unfortunately, I am not in a position to test this (well, I have MSVC++V6.0 Standard, but cannot get PHP to compile. I am not very familiar with MSVC++ and its setup to know what is missing).

If someone can explain how I can submit this to the actual source online for compilation, then I'd be very grateful.

I'd be even more grateful if someone could help me get PHP compiled. Even money may be sent, though I'd rather buy beer or something fizzy for the ladies.


This possible fix does not interfere with the removing of trailing spaces, though I wonder what would happen if 


Regards,

Richard Quadling.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-12-03 03:27 UTC] iliaa@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 [2004-12-16 11:32 UTC] richard dot quadling at bandvulc dot co dot uk
Back again in V5.0.3!

Or should that be still here?

I notice that the version of the code now tests to see if the length is 0 before the conversion of the data to its appropriate type.

Is it possible, that there is a distinction between NULL and 0?

My C knowledge says no. NULL is 0, but I may be wrong!

The reason I say this, is that if I make the column NULL, then I get NULL. If I make the column an empty string (i.e. select all and then delete - doing this in Enterprise Manager), I get a space in the result set! Argh!

Is there ANY way a debug version could be built that reported that the code that has been modified is actually called. I'd really like to know what length IS being returned if the field is empty.

I am more than willing to help get this fixed, but I need some hand holding in getting MSVC++ setup appropriately. I do not know what additional tools I need. I am in the process of downloading Cygwin to start some work on the PHP documentation (just getting the CHM compiled first!).
 [2004-12-16 11:39 UTC] richard dot quadling at bandvulc dot co dot uk
Simple test script to show the problem.

<?php
$rConn = mssql_connect('localhost','PHPBB_User','PHPBB_User');
$rResults = mssql_query('SELECT username, user_icq, LEN(user_icq) AS user_icq_len FROM PHPBB_User.phpbb_users');
while ($row = mssql_fetch_assoc($rResults))
	{
	echo '<pre>' . var_export($row, True) . '</pre>Length of ' . $row['username'] . '\'s user_icq = ' . strlen($row['user_icq']) . '<br /><br /><br />';
	}
mssql_free_result($rResults);
mssql_close($rConn);
?>


Requires phpBB and at least 1 user defined with an ICQ number. Obviously, you could choose any field or any other MS SQL database.

Richard.
 [2005-01-12 21:02 UTC] wchannospam at tomoye dot com
This problem is also appearing in PHP 4.3.x where x>=4. Can you implement the same fix there as well. Thank you very much.
 [2005-01-12 21:15 UTC] public at nexia dot ca
I second the request by wchannospam at tomoye dot com to port this bug fix to 4.3.x stream.

Its causing major issues for my PHP apps on Windows.
 [2005-02-15 19:13 UTC] erudd at netfor dot com
-- The reason I say this, is that if I make the column NULL, 
-- then I get NULL. If I make the column an empty string 
-- (i.e. select all and then delete - doing this in 
-- Enterprise Manager), I get a space in the result set! 

I recently came across this issue when upgrading to the lastest FreeTDS and the lastet PHP 4.3.x connecting to MS SQL Server 2000. The issue was actualy not php, as it was easily fixed by editing the freetds.conf and set the global "tds version" from 4.2 to 7.0 and the space issue went away.
 [2005-02-28 21:19 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5-win32-latest.zip


 [2005-03-08 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2005-03-17 17:59 UTC] beschr at free dot fr
I've got this problem too with php 5.0.3 on IIS/Windows XP Pro SP2.

With the mssql.dll of this snaps: Built On: Mar 17, 2005 01:30 GMT it work great.

So I think the correction in CVs is ok and you can close this bug.
 [2005-03-25 14:54 UTC] rantal at eoss dot ru
Same problem still exist in php4 snapshot 
php4-win32-STABLE-200503230530.zip
 [2005-03-31 10:54 UTC] beschr at free dot fr
Please ignore my precedent comment, the bug is still present in CVS.
I test the mssql.dll today (latest snap: Built On: Mar 29, 2005 16:30 GMT) and the bug is still present.
 [2005-06-21 20:59 UTC] robert dot sevcik at gmail dot com
Hi, It'd be nice to see it working in php 5.1 because right now I am developing in php5. I've tried various php5 snaps and 5.0.4 stable without efect.

I am on a Win2003 server and here is my try-case:

<?php
$c = mssql_pconnect('myserver');
$res = mssql_query('select top 1 Obrazek,len = len(Obrazek),bin = cast(Obrazek as varbinary) from katalog..Nabidka');
$a = mssql_fetch_assoc($res); //any method
var_dump($a);
print phpversion();

/* returns:
array(3) {
  ["Obrazek"]=>
  string(1) " "
  ["len"]=>
  int(0)
  ["bin"]=>
  string(1) " "
}
5.1.0-dev

Thank you much :)

*/

?>
 [2006-04-22 18:34 UTC] larry dot menard at rogers dot com
I seem to be having this same problem on PHP 5.1.2 (on Windows XP).

Simple test script:

<?php
$db = mssql_connect($server, $user, $pswd);
if (!$db) die();
mssql_select_db($dbname);
$rs = mssql_query('select g_theme from g2_AlbumItem where g_id = 7');
if (!$rs) die();
$row = mssql_fetch_array($rs);
mssql_free_result($rs);
var_dump($row);
mssql_close($db);
?>

Returns:

C:\MyServer>php testMsSql_mssql.php
array(2) {
  [0]=>
  string(1) " "
  ["g_theme"]=>
  string(1) " "
}

I know this is not correct, the actual content of that column is a 0-byte string:

C:\MyServer>sqlcmd -d ... -S ... -U ... -P ... -e
1> select g_theme from g2_albumitem
2> go
select g_theme from g2_albumitem

g_theme
--------------------------------


(1 rows affected)
1> select len(g_theme) from g2_albumitem
2> go
select len(g_theme) from g2_albumitem


-----------
          0

(1 rows affected)
1> select 'x' + g_theme + 'x' from g2_albumitem
2> go
select 'x' + g_theme + 'x' from g2_albumitem


----------------------------------
xx

(1 rows affected)
1> 

Is anyone still working on this?  It's been about 10 months since this bug was last updated.  (Unfortunately I do not have a PHP Build environment.)

Thanks.
 [2006-04-22 18:45 UTC] tony2001@php.net
See bug #29292.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Apr 16 08:01:32 2024 UTC