php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39213 NULL == '' in mssql extention
Submitted: 2006-10-20 15:16 UTC Modified: 2007-02-04 21:21 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (100.0%)
From: frediano dot ziglio at vodafone dot com Assigned: fmk (profile)
Status: Closed Package: MSSQL related
PHP Version: 5.1.6 OS: Linux
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 you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: frediano dot ziglio at vodafone dot com
New email:
PHP Version: OS:

 

 [2006-10-20 15:16 UTC] frediano dot ziglio at vodafone dot com
Description:
------------
when you get data you assume NULL if dbdatlen == 0 however is possible that data is just an empty string (''), in this case you should check that dbdatlen == 0 and dbdata == NULL.

Reproduce code:
---------------
I use this test script (pwd.inc define just variable to connect)

<?php

// $Id: null.php,v 1.2 2006/10/20 14:38:22 freddy77 Exp $

require_once("pwd.inc");

$conn = mssql_connect($server,$user,$pass) or die("opps");

mssql_query("CREATE TABLE #MyTable (
   myfield VARCHAR(10) NULL,
   n INT
)", $conn) or die("error querying");


mssql_query("INSERT INTO #MyTable VALUES('',1)
INSERT INTO #MyTable VALUES(NULL,2)
INSERT INTO #MyTable VALUES(' ',3)
INSERT INTO #MyTable VALUES('a',4)", $conn) or die("error querying");

$result = 0;

function test($sql, $expected)
{
        global $conn, $result;

        $res = mssql_query($sql, $conn) or die("query");
        $row = mssql_fetch_assoc($res);
        $s = $row['myfield'];

        if (is_null($s))
                $s = '(NULL)';
        else if ($s == '')
                $s = '(Empty String)';
        else
                $s = "'".str_replace("'", "''", $s)."'";
        echo "$sql -> $s\n";
        if ($s != $expected)
        {
                echo "error!\n";
                $result = 1;
        }
}

test("SELECT top 1 * FROM #MyTable WHERE n = 1 -- ''", "(Empty String)");

test("SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL", "(NULL)");

test("SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' '", "' '");

test("SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a'", "'a'");

exit($result);
?>


Expected result:
----------------
SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> (Empty String)
SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL -> (NULL)
SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' ' -> ' '
SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a' -> 'a'


Actual result:
--------------
SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> (NULL)
error!
SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL -> (NULL)
SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' ' -> ' '
SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a' -> 'a'


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-10-20 15:25 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.


 [2006-10-20 19:08 UTC] frediano dot ziglio at vodafone dot com
Here you are a simplified version. It require a db but it create the only table it needs and delete it.

Expected result:
  got --

<?php
$conn = mssql_connect(<server>,<user>,<pass>) or die("opps");

mssql_query("CREATE TABLE #tmp(c VARCHAR(10) NULL)", $conn) or die("error querying");

mssql_query("INSERT INTO #tmp VALUES('')", $conn) or die("error querying");

$res = mssql_query("SELECT * FROM #tmp", $conn) or die("query");
$row = mssql_fetch_assoc($res);

$s = is_null($row['c']) ? 'NULL' : $row['c'];
echo "got -$s-\n";
?>

In http://cvs.php.net/viewvc.cgi/php-src/ext/mssql/php_mssql.c?view=markup you have 

if (dbdatlen(mssql_ptr->link,offset) == 0) {

however in order to fix the problem should be

if (dbdatlen(mssql_ptr->link,offset) == 0 && dbdata(mssql_ptr->link,offset) == NULL) {

note that under Windows Microsoft dblib use only older wire protocol version which do not support empty string so it returns them as a single space.
 [2006-10-20 19:13 UTC] frediano dot ziglio at vodafone dot com
Mmm... perhaps a less invasive change is to add 

if (data == 0) {
   ZVAL_NULL(result);
   return;
}

after 

char *data = charcol(offset);

line on the same function.
 [2007-02-01 15:08 UTC] frode at coretrek dot com
We're running into the same issue. Is the patch that was provided by the bug reporter planned for inclusion in a future release?
 [2007-02-04 21:21 UTC] fmk@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.

Microsofts libraries will return a length of 1 for the empty string. WHen the MSSQL extension is compiled with the FreeTDS library the returned values are correct for both empty string and null values.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Dec 22 06:01:30 2024 UTC